Jump to content


Photo

Count and display total articles in a category


  • Please log in to reply
14 replies to this topic

#1 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 20 March 2006 - 07:03 PM

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

#2 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 07:09 PM

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

Info: PHP Manual


#3 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 20 March 2006 - 07:11 PM

[!--quoteo(post=356716:date=Mar 20 2006, 07:03 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 20 2006, 07:03 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I have a database with a table for my articles and another for my categories.
I want to be able to display the number of articles in each category. For example:
  • Tutorials (5)
  • Updates (11)
  • Links (7)
  • Downloads (3)
How can I do this? I've tried several ways, but none have worked in my application.
Thanks in advance.
[/quote]
<?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);
?> 

Hope this works.
good luck.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#4 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 07:14 PM

That will not work, redarrow.

Info: PHP Manual


#5 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 12:17 PM

[!--quoteo(post=356719:date=Mar 20 2006, 10:09 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ Mar 20 2006, 10:09 PM) View Post[/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 colname?
And from which table do I select?

My articles table has a column called article_category_id 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.


#6 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 24 March 2006 - 12:21 PM

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

My articles table has a column called article_category_id 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:

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


"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#7 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 12:54 PM

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

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

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

Any clue as to why that happened?

#8 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 24 March 2006 - 01:03 PM


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

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

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

"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#9 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 01:40 PM

[!--quoteo(post=357915:date=Mar 24 2006, 04:03 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 04:03 PM) View Post[/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.

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

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

#10 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 24 March 2006 - 02:08 PM

[!--quoteo(post=357917:date=Mar 24 2006, 01:40 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 24 2006, 01:40 PM) View Post[/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:

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

"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#11 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 02:31 PM

:( 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)?



#12 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 24 March 2006 - 03:33 PM

[!--quoteo(post=357933:date=Mar 24 2006, 02:31 PM:name=yourbrain)--][div class=\'quotetop\']QUOTE(yourbrain @ Mar 24 2006, 02:31 PM) View Post[/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?
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#13 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 05:47 PM

[!--quoteo(post=357946:date=Mar 24 2006, 06:33 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 06:33 PM) View Post[/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...

#14 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 24 March 2006 - 06:06 PM

Nothing was damaged, thank God.

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

Two tables: np_articles and np_categories

np_articles has many columns, the most important for this topic are article_id and article_category_id

np_categories only has three columns: category_id, category_name, and category_description.

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

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,
<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)); ?>

I tried adding this query: $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"; and adding this line:
<?php echo $row_rsCategories['articlecount'];?>
in brackets after my category_name bit (shown above), but no luck.

*sigh*. Help me. anyone.


#15 yourbrain

yourbrain
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 25 March 2006 - 06:57 PM

Any suggestions?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users