current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I was wondering if someone could help me simplify my current counting method. I´m trying to learn more in PHP/MySQL so please be positive :)

I have a table with multiple articles that are categorized into sections. What I would like to do is count the number of articles in each section and be able to call that value to be displayed on my web page.

This is my current solution (simplified version without extra query filters) but I would think it fairly resource heavy for a simple task:

$query = mysql_query("SELECT category FROM table");
$list = "";

while($item = mysql_fetch_array($query) ) {
     $list = $list.",".$item['category'];
}
// Then to count how many results for each category:
$categoryA = substr_count($list, 'Category A');
$categoryB = substr_count($list, 'Category B');
...

I have about 20 categories that are counted in this manner.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Try this:

SELECT category, COUNT(*) AS num FROM table GROUP BY category

You can then use this PHP code to get the data in the same format you wanted it.

$query = mysql_query("SELECT category, COUNT(*) AS num FROM table GROUP BY category");
$list = array();

while($item = mysql_fetch_array($query)) {
     $list[$item['category']] = $item['num'];
}

$categoryA = $list['Category A'];
$categoryB = $list['Category B'];
share|improve this answer
    
it seems you forgot something :) –  Your Common Sense Nov 5 '10 at 17:21
    
I probably did, its been one of those days. Mind elaborating? I am not seeing it at the moment. –  Alan Geleynse Nov 5 '10 at 17:25
    
I've just added category to your query. otherwise it seems quite useless –  Your Common Sense Nov 5 '10 at 17:34
    
That was already on there. I missed it at first and edited a few seconds later before your comment. I wonder why it was not showing up for you. I also don't see any edit history from you. –  Alan Geleynse Nov 5 '10 at 17:37
    
Thanks, I´ll try that. on my first try I got no results (by just replacing my query with yours). But when I get home I can give it a better try :) –  Skuli Axelson Nov 5 '10 at 18:02
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.