Question 42106 in PHP Question posted in
Official manual can be found here.

Selecting data from multiple tables in MySQL – PHP

I have three tables which look like this (simplified)

"plants" Table

ID (PK, AI) | botanicalName      | quickCode
1           | Monstera adansonii | 1234567
2           | Aloe Vera          | 1233456

"commonNames" Table

ID (PK, AI) | plantsID | commonName     
1           | 1        | Swiss Cheese Vine
2           | 2        | Cape Fern
3           | 1        | Hurricane Plant

"images" Table

ID (PK, AI) | plantsID | fileName    
1           | 1        | monstera_adansonii.jpg
2           | 2        | capefern.jpg
3           | 2        | capefern2.jpg

In "commonNames" and "images" tables the "plantsID" columns are references to the ID in "plants" table.

How could I write my MySQL Select and php to format a result like this:

    array (
        id => 1, //plants.id
        botanicalName => Monstera adansonii, //plants.botanicalName
        commonNames => array ( 0 => Swiss Cheese Vine, 1 => Hurricane Plant ), //commonNames.commonName (array)
        fileName => array ( 0 => monstera_adansonii.jpg ) //images.fileName (array),
    )
Tags:

2

Answers


  1. 0

    I would like to offer the following solution:

    1. Select all plants and save result to $plants variable

      SELECT ID as id, botanicalName FROM plants;

    2. Use the loop and add key commonNames and fileName

      foreach(plants as &$plant){

      $plant['commonNames'] = #result of SQL1

$plant['fileName'] = #result of SQL2

      }

    3. SQL1

      SELECT ID as id, commonName FROM commonNames WHERE plantsID = {$plant[‘id’]};

    4. SQL2

      SELECT ID as id, fileName FROM images WHERE plantsID = {$plant[‘id’]};

    Login or Signup to reply.
  2. 0

    I would use one query with GROUP_CONCAT for commonNames and fileName. Then loop through results and explode those fields. Something like that:

    SELECT plant.id, plant.botanicalName, GROUP_CONCAT(name.commonName) AS commonNames, GROUP_CONCAT(image.fileName) AS fileName
FROM plants AS plant
JOIN commonNames AS name ON name.plantsID = plant.ID
JOIN images AS image ON image.plantsID = plant.ID
GROUP BY plant.id

    Let’s say the query returns results into $plants so then you would do:

    foreach($plants as $key => $plant) {
    $plants[$key]['commonNames'] = explode(",", $plant['commonNames']);
    $plants[$key]['fileName'] = explode(",", $plant['fileName']);
}

    Please note that this is all hand written without syntax check.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search