I am trying to get one item from one table in my database and several items from another table then group them.

The idea is a photo gallery.

It will get the album name and id from one table then display the Album name on the page. Then get 3 photos from the database that have the same Album Id as this first album.

This will then repeat for album 2 and album 3 and so on.


So i should have

Album 1, Album 1 photo 1, Album 1 photo 2, Album 1 photo 3.

Album 2, Album 2 photo 1, Album 2 photo 2, Album 2 photo 3.

Album 3, Album 3 photo 1, Album 3 photo 2, Album 3 photo 3.


My current code is this, it doesn't work though.

<table>
<?php
$sql =
'SELECT p_album_name, GROUP_CONCAT( photo_image ) as photo_image
FROM albums
JOIN photos
GROUP BY "p_album_id"';

$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{

$p_album_name = $row[p_album_name];
$p_album_id = $row[p_album_id];
$photo_image = $row[photo_image];


echo "<tr><td>" . $p_album_name . "</td><td>" . $photo_image . "</td></tr>";
}

?>
</table>



Can anyone please help me with this,
Thanks.