Page 1 of 1

Finding out number of rows from a table

Posted: Thu May 18, 2006 12:08 pm
by Sphenn
Hi,

I'm looking to find the number of rows from a certain table in my database. They're articles, and I want to find out how many (approved) articles are in each category. I've thought of a few different ways, but I am wondering if anyone has any better ones.

Idea 1

Code: Select all

// Get the number of categories

foreach ( $category_id as $category )
{
   $sql = 'SELECT article_id
      FROM article_table
         WHERE article_category_id = ' . $category
}
Idea 2

Code: Select all

$sql = 'SELECT article_id
      FROM article_table';
// Get resultset

if ( $row['article_id'] = '1' )
{
   $cat1++;
}
// etc
I know there's got to be a much better way. Any and all help would be appreciated.

Thank you

Sphen[/syntax]

Posted: Thu May 18, 2006 1:05 pm
by timvw
Assuming that for each article_category there is at least one article in the article_table...
(Otherwise you'll have to perform an outer join, and use ifnull...)

Code: Select all

SELECT article_category_id, SUM(article_id) as total
FROM article_table
GROUP BY article_category_id

Posted: Fri May 19, 2006 11:03 am
by BadgerC82
I don't think you meant to use SUM?

Code: Select all

SELECT article_category_id, SUM(article_id) AS total 
FROM article_table 
GROUP BY article_category_id
Will this do?

Code: Select all

SELECT COUNT(article_category_id) FROM article_table GROUP BY article_category_id
I'm probably confused :)