Finding out number of rows from a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sphenn
Forum Commoner
Posts: 48
Joined: Sun Jul 17, 2005 8:08 pm
Location: Winnipeg, MB

Finding out number of rows from a table

Post 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]
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post 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 :)
Post Reply