Help setting up a count() between two queries

[mykel_suthertun]

I'm still a newbie, so please bear with me...

 

Here's my dilemma:

 

I've made my own PHP/MySQL photo gallery. It uses two db tables - photos_album, photos_image. (Every image entry in the photos_image table associates itself with a photo album by storing the appropriate album_id.) Soo, that's how it works. Pretty simple.

 

On the index of the photo gallery, I am currently querying every album from the photos_album table to display it to the user. Here's a simplified version of what's going on:

 

$query = mysql_query("SELECT album_id, album_name 
    FROM $photos_album 
    ORDER by album_name ASC",$db)
while($row = mysql_fetch_array($query)) {
    $album_name = $row['album_name'];
    echo("<div class=\"album\">$album_name</div>\n");
}

 

Here's the question:

How do I, for every album that's being echoed from the table photos_album, count the number of images in the table photos_image that associate with that album?

 

I've used COUNT() in MySQL before, but never across two different tables like this, and I'm not sure how to set it up. Any help here would be greatly appreciated. Thanks!

[fenway]

You simply need to join the two tables, add a group by on album_id, and count() appropriately.

[mykel_suthertun]

Thanks for the tip. I don't have any experience with JOIN, GROUP, and minimal experience with COUNT. I check some samples though, and here's my first (failed) attempt:

$query = mysql_query("SELECT $dbt_photos_album.album_id, 
		$dbt_photos_album.album_name, 
		$dbt_photos_album.album_image, 
		$dbt_photos_album.album_description, 
		$dbt_photos_album.album_path, 
		$dbt_photos_image.image_album_id, 
		COUNT($dbt_photos_image.image_album_id) 
	FROM $dbt_photos_album 
	INNER JOIN $dbt_photos_image 
	ON $dbt_photos_album.album_id = $dbt_photos_image.image_album_id 
	GROUP BY album_id 
	ORDER by album_name ASC",$db) 
	or die_now("<h4>Could not select photo albums from database.</h4>");

 

Notice:

Undefined index: COUNT($dbt_photos_image.image_album_id) in /Users/michaelsoutherton/Sites/Clients/The Drive/photos/index.php on line 48

 

Can you point me in the right direction? Thanks.

[fenway]

Doesn't look 'wrong'... I have no idea what produced that error... and your group by should have an explicit table prefix. 

[mykel_suthertun]

This is where the notice is occuring:

while($row = mysql_fetch_array($query)) {
...etc...
$image_count = $row['COUNT($dbt_photos_image.image_album_id)'];
...etc...
}

 

Is there anything wrong with that?

[fenway]

Two things -- one, your variable won't be interpolated from within single quotes; two, you should alias that expression in your column list (e.g. COUNT($dbt_photos_image.image_album_id) AS imageCount) so that you can refer to it cleanly in your $row hash.