PHP & MySQL JOIN's

[crazyryan]

Hey

Need a bit of help with mysql joins and php...

<?php
$query = mysql_query("select * from categories");
while($row = mysql_fetch_array($query)) {
echo '<li><a href="#">' . $row['name'] . '</a> - (55)</li>';
}
?>

Currently, that's just selecting the name of the category, however where (55) is, I want to display how many files are within that category. I could use 2 queries but think I should learn properly...

So, categories table has id and the name, e.g.
ID -- Name
51 -- Arcade

Then, I have a files table that has a column called catid, and if the file is in the arcade category, the catid column has 51 in it..

Hopefully you understand what I'm getting at, but I want to select the amount of total games in a category and the name in one query, any help appreciated.

Thanks

[Muhammad Haris]

Not sure, sorry.

[crazyryan]

Thanks for the suggestion Haris, anyone else got any ideas?

[SmallPotatoes]

Assuming the files table also has an 'id' column which refers uniquely to each file...


$sql = "select categories.name, count(files.id) as count
from categories, files
where categories.id = files.catid
group by categories.name";
$st = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($st))
{
echo "<li><a href='#'>{$row['name']}</a> - ({$row['count']})</li>";
}


Note that this will not display any categories which have zero files. If you want those too, you have to do an outer join.

[crazyryan]

Thanks, works perfectly.