Count and display total articles in a category

[yourbrain]

I have a database with a table for my [b]articles[/b] and another for my [b]categories[/b].
I want to be able to display the [u]number of articles in each category[/u]. For example:[list][*]Tutorials (5)[*]Updates (11)[*]Links (7)[*]Downloads (3)[/list]
How can I do this? I've tried several ways, but none have worked in my application.
Thanks in advance.

[ober]

SELECT COUNT(colname) AS cntCol FROM tablexyz WHERE cat = 'x';

[redarrow]

[!--quoteo(post=356716:date=Mar 20 2006, 07:03 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 20 2006, 07:03 PM) [snapback]356716[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have a database with a table for my [b]articles[/b] and another for my [b]categories[/b].
I want to be able to display the [u]number of articles in each category[/u]. For example:[list]
[*]Tutorials (5)
[*]Updates (11)
[*]Links (7)
[*]Downloads (3)
[/list]
How can I do this? I've tried several ways, but none have worked in my application.
Thanks in advance.
[/quote]
[code]
<?php
$res = mysql_query("select * from blah")
$row = mysql_fetch_array($res);
echo count($row);
echo $row[Tutorials ];
echo $row[Updates];
echo $row[Links];
echo $row[Downloads];
echo count($row);
?>
[/code]

Hope this works.
good luck.

[ober]

That will not work, redarrow.

[yourbrain]

[!--quoteo(post=356719:date=Mar 20 2006, 10:09 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ Mar 20 2006, 10:09 PM) [snapback]356719[/snapback][/div][div class=\'quotemain\'][!--quotec--]
SELECT COUNT(colname) AS cntCol FROM tablexyz WHERE cat = 'x';
[/quote]
A few questions please...
What do I put instead of [b]colname[/b]?
And from which table do I select?

My articles table has a column called [b]article_category_id[/b] which carries the Id of the category. The category table only has three columns (id, name, and description).

Do I do an INNER JOIN?

Sorry, new at this... I'm trying different this now as I wait for your reply... but I am not getting good results. So far, this is telling me that each category has 1 article in it... not correct.

[redbullmarky]

[!--quoteo(post=357902:date=Mar 24 2006, 12:17 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 24 2006, 12:17 PM) [snapback]357902[/snapback][/div][div class=\'quotemain\'][!--quotec--]
A few questions please...
What do I put instead of [b]colname[/b]?
And from which table do I select?

My articles table has a column called [b]article_category_id[/b] which carries the Id of the category. The category table only has three columns (id, name, and description).

Do I do an INNER JOIN?

Sorry, new at this... I'm trying different this now as I wait for your reply... but I am not getting good results. So far, this is telling me that each category has 1 article in it... not correct.
[/quote]

colname can be any field really, but your primary key is normally the best.
you select from your articles table. the above function counts the number of articles in each category.

you may be able to get it all in one go with:

[code]
select count(articles.id), categories.cat_name from articles left join categories on articles.article_category_id = categories.id
[/code]

[yourbrain]

I tried this:
[code]SELECT COUNT(*) FROM np_articles INNER JOIN np_categories ON article_category_id=category_id WHERE article_category_id = 3[/code]

as my query. And after my category name I have this:
[code]<p><?php echo $row_rsCategories['category_name']; ?> (<?php echo $totalRows_rsCountArticles ?>)</p>[/code]

But, in preview mode, I still get [b](1)[/b] next to each category name....

Any clue as to why that happened?

[redbullmarky]

it's not $totalRows_rsCountArticles you need. all that does is return the number of records the SQL query found.

[code]
select count(articles.id) as articlecount, categories.cat_name from articles left join categories on articles.article_category_id = categories.id
[/code]

then refer to the article count as $row_rsCategories['articlecount'], not $totalRows_rsCountArticles

[yourbrain]

[!--quoteo(post=357915:date=Mar 24 2006, 04:03 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 04:03 PM) [snapback]357915[/snapback][/div][div class=\'quotemain\'][!--quotec--]
it's not $totalRows_rsCountArticles you need. all that does is return the number of records the SQL query found.

[code]
select count(articles.id) as articlecount, categories.cat_name from articles left join categories on articles.article_category_id = categories.id
[/code]

then refer to the article count as $row_rsCategories['articlecount'], not $totalRows_rsCountArticles
[/quote]

I get this error when I try to implent that:
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

[redbullmarky]

[!--quoteo(post=357917:date=Mar 24 2006, 01:40 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 24 2006, 01:40 PM) [snapback]357917[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I get this error when I try to implent that:
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
[/quote]

whoops. try this:

[code]
select count(articles.id) as articlecount, categories.cat_name from articles left join categories on articles.article_category_id = categories.id GROUP BY categories.cat_name
[/code]

[yourbrain]

:( This isn't working for me. And it's supposed to be so simple. Argh. Tell me, is there a working example I can see somewhere. Or, can I give you any more info that'll help lead to the problem (other than my non-functional mind)?

[redbullmarky]

[!--quoteo(post=357933:date=Mar 24 2006, 02:31 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 24 2006, 02:31 PM) [snapback]357933[/snapback][/div][div class=\'quotemain\'][!--quotec--]
:( This isn't working for me. And it's supposed to be so simple. Argh. Tell me, is there a working example I can see somewhere. Or, can I give you any more info that'll help lead to the problem (other than my non-functional mind)?
[/quote]

what errors/results are you getting now?

[yourbrain]

[!--quoteo(post=357946:date=Mar 24 2006, 06:33 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 06:33 PM) [snapback]357946[/snapback][/div][div class=\'quotemain\'][!--quotec--]
what errors/results are you getting now?
[/quote]

Nothing shows up in my brackets, I get: Category Name (). As if I didn't do anything.
And my mac battery died on me, so I have yet to check what happened to my file...

[yourbrain]

Nothing was damaged, thank God.

Anyway, here's more info that might be of use for anyone who can help out...

Two tables: [b]np_articles[/b] and [b]np_categories[/b]

[b]np_articles[/b] has many columns, the most important for this topic are [b]article_id[/b] and [b]article_category_id[/b]

[b]np_categories[/b] only has three columns: [b]category_id[/b], [b]category_name[/b], and [b]category_description[/b].

On my current page, I have a list of my categories. I use the following query: [i]$query_rsCategories = "SELECT * FROM np_categories ORDER BY category_name ASC";[/i]

I have had no problems with this query and have used it often. It lives at the very top of my document.

To display a list of the categories, I use something similar to this,
[code]<p>Total Categories: <?php echo $totalRows_rsCategories ?></p>
   <?php do { ?>
      <p><?php echo $row_rsCategories['category_name']; ?></p>
   <?php } while ($row_rsCategories = mysql_fetch_assoc($rsCategories)); ?>
[/code]

I tried adding this query: [i]$query_rsCountArticles = "SELECT COUNT(np_articles.article_id) AS articlecount, np_categories.category_name FROM np_articles LEFT JOIN np_categories ON np_articles.article_category_id = np_categories.category_id GROUP BY np_categories.category_name";[/i] and adding this line: [code]<?php echo $row_rsCategories['articlecount'];?>[/code] in brackets after my category_name bit (shown above), but no luck.

*sigh*. Help me. anyone.

[yourbrain]

Any suggestions?