CODEWITHSUNDEEP
phpmysqlselect
Guilherme Natal de Mello
Guilherme Natal de Mello

Reputation: 427

Best way to select 2 tables mysql

PHP + MYSQL

I have 2 tables:

I need to show in my html page the posts, with the images in each post. Example:

My first post comment (this post has one image)

My second post comment (this post has three images)

My third post (this post has two images)

...

One way to do it is: for each post, go to database and get the images... but I think that as less access to database is better... how can I do my select?

( I would like ONE line per POST... ) is it possible?

Result example:

COLUMN POST - COLUMN IMAGE

"My first post" - "image1.jpg, image2.jpg, image3.jpg"

Is there another better way to do it?

Thanks!

Upvotes: 0

Views: 67

Answers (2)

aleroot
aleroot

Reputation: 72636

Just use GROUP_CONCAT to concatenate multiple images related to one post, as in the following example :

SELECT POST.ID AS POST_ID, GROUP_CONCAT(IMAGES.IMAGE) AS IMAGES
FROM POST
JOIN IMAGES ON IMAGES.POST = POST.ID
GROUP BY POST.ID
ORDER BY POST.ID

Upvotes: 1

agim
agim

Reputation: 1841

Join the two tables and you have what you want. You should simply order by post id and that's it

SELECT POST.ID AS POST_ID, P.COMMENT, IMAGES.IMAGE
FROM POST
JOIN IMAGES ON IMAGES.POST = POST.ID
ORDER BY POST.ID

Upvotes: 1

Related Questions