php mysql: displaying multiple images accociated with a record

[porlewest]

Hi all,

I am having trouble selecting multiple records from one table, and their associated multiple images from another table.

$qry = "SELECT * FROM `news` LEFT JOIN images ON news.id = images.imgCategory AND images.imgSection = 'news'";

So I am able to return the results, but for the records with multiple images, it returns that record multiple times, a separate record for each image.

Is there a better way i could structure my query to bring back one of each record with all its associated images? Or is there a way i can make php cycle the results to display all the images with the same record reference before moving onto the next record. This has had me stumped for a while. I think this is where my current sql knowledge halts.

Thanks for looking,
Paul.

 

[sleipnir214]

What you are seeing is the standard behavior of most relational database managers.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[DRJ478]

Much depends on your MySQL version.
However, for 4.1 the GROUP_CONCAT is the best solution. You will get a one record result that has all values of the desired filed concatenated.

From there you would get the record, explode the values into an array and iterate in whatever fashion you'd like.

Alternatively just loop trough the results of the query and handle each record individually. How to do that, read faq434-3850