Selecting data from multiple tables in MySQL

Question

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

This "requirements dump" is missing its coding attempt and proof of researcher/toil. Please edit your question to avoid downvotes. — mickmackusa
– mickmackusa ♦, Commented Oct 20, 2022 at 7:19

f.nics · Accepted Answer · 2022-10-20 11:14:58Z

1

I would like to offer the following solution:

Select all plants and save result to $plants variable

SELECT ID as id, botanicalName FROM plants;

Use the loop and add key commonNames and fileName

foreach(plants as &$plant){

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

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

}

SQL1

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

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

edited Oct 20, 2022 at 11:14

answered Oct 20, 2022 at 11:01

f.nics

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Soriyyx · Accepted Answer · 2022-10-20 11:26:12Z

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.

Collectives™ on Stack Overflow

Selecting data from multiple tables in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related