Help with SELECT query needed

[2sharp]

Hi all,

I have two tables in the database for my photographic website:
[a href=\"http://www.sharperstill.com/\" target=\"_blank\"]http://www.sharperstill.com/[/a]
One is Categories and contains three fields: a unique id, category_anme, and category_path
The other is Photos and contains four fields: a unique id, photo_filename, photo_caption, and c_id (which links to category tables id field....

What I want to do, with one SELECT query, is extract the names and paths of all categories (for building a list of links) as well as getting a count of the number of pictures in each category (for building 'smart' next/previous links.

All has been going well except that the count returned is always for the number of categories, not the number of pics in each category.

Have been playing with lots of code lately but follwing will show the direction I am heading in:

SELECT category_name, category_path, photo_filename, photo_caption, COUNT(*) FROM category, photos WHERE category.id = photos.c_id GROUP BY category.id ORDER BY id DESC";

The number of pics shown in each category is correct on the output for the links list, but when I use it to show pic x of xx total it is wrong and does not change between categories...

Jon

[fenway]

Yeah, the COUNT() is returning the number of rows returned, which is necessarily simply the number of categories. COUNTs can be confusing with GROUP BY clauses; you may have to do a subselect, or write another query.

Does the following return the desired result?

[code]SELECT category.category_name, COUNT(*) FROM category, photos WHERE category.id = photos.c_id GROUP BY category.id;[/code]