PHP & MySQL JOIN's

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>';
                        }
                        ?>

PHP:

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

 

Not sure, sorry.

 

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

 

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>";
}

PHP:

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

 

Thanks, works perfectly.