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),
    )

CodePudding user response：

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']};

CodePudding user response：

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.