Jump to content

Archived

This topic is now archived and is closed to further replies.

yourbrain

Count and display total articles in a category

Recommended Posts

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.

Share this post


Link to post
Share on other sites
SELECT COUNT(colname) AS cntCol FROM tablexyz WHERE cat = 'x';

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
That will not work, redarrow.

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
:( 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)?

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
[!--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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.