mysql，php从多个表mysql中检索数据

Question

I have been trying to retrieve the values form two tables mysql:

table1 = jid, name, lat, lng

table2 = id, imgs, jid

My problem is that the second table could have 2 or more items. I would like to retrieve the data from the two tables in one query. Is it possible to make some thing like:

id name    lat  lon  imgs
1  coco   -122  210   1.jpg, 2.jpg, 3.jpg

I tryed with Join, union and :-(

Answer 1

You cannot get the result you want in one step, you will have to unload the data from the database like this for example:

SELECT table1.id, table1.name, table1.lat, table1.lng, table2.imgs
FROM table1
    INNER JOIN table2 ON table2.jid = table1.jid;

This will build a result like this

id name    lat  lon  imgs
1  coco   -122  210   1.jpg
1  coco   -122  210   2.jpg
1  coco   -122  210   3.jpg
2  late   -100  270   1.jpg
2  late   -100  270   2.jpg

You can then process through the result set row by row and build either the page content or some new data store using a change in the id field to indicate that you are starting the image set for a new jid.