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 :-(
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.
Try this query:
SELECT table2.id, table1.name, table1.lat, table1.lng, table2.imgs
FROM table1
INNER JOIN table2
ON table1.jid = table2.jid;