0

I currently have 2 tables. One that contains the news and second witch contains the images.

news table:

news_id

news_head

news_content

Gallery table

gallerynews_id

gallerynews_newsid

gallerynews_filename

My question is how do i join them together (php/mysql) so the images match the news? For example. 1 news/story can contain 4 images. The result must look something like this:

News 1(head)
image1.jpg, image2.jpg, image3 (related images for the news).

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
simon
  • 2,235
  • 6
  • 33
  • 53

1 Answers1

0
SELECT N.*, G.*
FROM news N
LEFT JOIN gallery G ON N.news_id = G.gallerynews_newsid

I use a left join so that if there are no images, you still get your news item.

SELECT N.*, GROUP_CONCAT(G.gallerynews_filename)
FROM news N
LEFT JOIN gallery G ON N.news_id=G.gallerynews_newsid
Sam
  • 9,933
  • 12
  • 68
  • 104
  • Thanks for yor answer. How do i categorize them in PHP? :-) – simon Dec 26 '12 at 21:16
  • Categorize them? Is this for a list page, or the news article page? – Sam Dec 26 '12 at 21:19
  • Its for a list with all the news. There must be a header and then all the images related to that header :) – simon Dec 26 '12 at 21:21
  • If you want them all in one row and can parse them out by a delimiter in your PHP, you can try using GROUP_CONCAT (see updated answer above). – Sam Dec 26 '12 at 22:16
  • Can you give an example how to parse them out by a delimiter via php? – simon Dec 27 '12 at 16:33
  • Use the explode function with to split the comma-separated string into an array. You can then loop through that. http://stackoverflow.com/questions/5159086/php-split-string – Sam Dec 27 '12 at 17:33