Counting and displaying total articles in specific category

[bayruni]

I need help learning how I can display the total number of articles in each category, I need something sort of like this:

• General (11)
• Tutorials (5)
• Downloads (7)

Info:
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. I've searched other forums, but haven't found a working solution yet.

[redhead]

This obvius belongs here, so now it's been moved..
How about somethign like this:

PHP Code:

$query=mysql_query("SELECT * FROM np_categories");
while($row= mysql_fetch_array($query))
{
     $data=mysql_query("SELECT COUNT (*) FROM np_articles WHERE article_category_id = $row[category_id]");
     $i_row=mysql_fetch_array($data);
     echo $row["category_name"] . " (" . $i_row[0] . ")"; 
} 


Or perhaps something like this:

PHP Code:

$query=mysql_query("SELECT * FROM np_categories");
while($row=mysql_fetch_array($query))
{
     $data=mysql_query("SELECT * FROM np_articles WHERE article_category_id = $row[category_id]");
     $i=mysql_num_rows($data);
     echo $row["category_name"] . " (" . $i . ")"; 
} 


[sde]

or something like this:

PHP Code:

<?
$result = mysql_query("select c.category_name, count(a.article_id) as count from np_categories as c
  left join np_articles as a on c.category_id=a.article_category_id
  group by c.category_id
  order by c.category_name");

while ($row = mysql_fetch_assoc($result)) {
  echo $row['category_name'] . " (" . $row['count'] . ")<br />\n";
}

[Brent]

Here's how I'd do it:

$result = mysql_query( "SELECT category_id, category_name FROM np_categories ORDER BY category_name ASC" );
while( $row = mysql_fetch_array( $result ) )
	{
	$category_id = $row[0];
	$name = $row[1];
	$data = mysql_fetch_array( mysql_query( "SELECT COUNT(*) FROM np_articles WHERE article_category_id = $category_id" ) );
	$num_articles = $data[0];
	print( "<li> $name ($num_articles)</li>\n" );
	}

[bayruni]

Originally Posted by sde

or something like this:

PHP Code:

<?
$result = mysql_query("select c.category_name, count(a.article_id) as count from np_categories as c
  left join np_articles as a on c.category_id=a.article_category_id
  group by c.category_id
  order by c.category_name");

while ($row = mysql_fetch_assoc($result)) {
  echo $row['category_name'] . " (" . $row['count'] . ")<br />\n";
}

Thank you so much! This option worked perfect. However, what if I already have a list of my categories in a table and only want to display the number of articles in the category - and not the entire list again?

[sde]

in that case, i would create an array so it can be re-used. it can be keyed by category_id so you know which category you are accessing.

PHP Code:

<?
$result = mysql_query("select c.category_id, c.category_name, count(a.article_id) as count from np_categories as c
  left join np_articles as a on c.category_id=a.article_category_id
  group by c.category_id
  order by c.category_name"); 

while ($row = mysql_fetch_assoc($result)) {
  $categories[$row['category_id']] = $row;
}

// now echo the name and article count of category id 15
echo $categories[15]['category_name'] . " (" . $categories[15]['count'] . ")";
?>

[bayruni]

A little problem... my categories are already listed using another recordset. I also have the repeat thing going... this table is automatically updates, so - because I have to specify the [id] number in the brackets... I think it's going to cause some problems. I tried replacing [id] with [category_id] - thinking that it might work.. but, no luck.

Here's the code for my table:

<table id="tbl" border="0" cellpadding="0" cellspacing="0">	
 <tr>
  <td><p><b>Category</b></p></td>
  <td colspan="2"><p><b>Description</b></p></td>
 </tr>

<?php do { ?>
 <tr <?php 
   # Alternate Row Color, Block 2/3
   echo " style=\"background-color:$color\""; ?>>
 <td width="100"><p><?php echo $row_rsCategories['category_name']; ?> (<? echo $categories[1]['count'] ?>)</p></td>
 <td width="300"><p><?php echo $row_rsCategories['category_description']; ?></p></td>
 <td width="145"><p><a href="display_category.php?category_id=<?php echo $row_rsCategories['category_id']; ?>">View</a> | <a href="edit_category.php?category_id=<?php echo $row_rsCategories['category_id']; ?>">Edit</a> | <a href="javascript:BRB_PHP_DelWithCon('delete_category.php','category_id',<?php echo $row_rsCategories['category_id']; ?>,'Are you sure you want to delete this category?');">Delete</a></p></td>
</tr>
<?php # Alternate Row Color, Block 3/3
	if ($color == $color1) {
       $color = $color2;
       } else {
       $color = $color1;
       }
?>
<?php } while ($row_rsCategories = mysql_fetch_assoc($rsCategories)); ?>
</table>

[sde]

doesn't your code enter a blank line on the first iteration of your do/while loop? i'd suggest this format:

PHP Code:

<table id="tbl" border="0" cellpadding="0" cellspacing="0">    
<tr>
  <td><p><b>Category</b></p></td>
  <td colspan="2"><p><b>Description</b></p></td>
</tr>

<?
while ($row_rsCategories = mysql_fetch_assoc($rsCategories)) {

  $color = ($color == $color1) ? $color2 : $color1;

    echo "<tr style=\"background-color:$color\">\n
  <td width=\"100\"><p>" . $row_rsCategories['category_name'] . " (" .$categories[1]['count'] . ")</p></td>\n
  <td width=\"300\"><p>" . $row_rsCategories['category_description']. "</p></td>\n
  <td width=\"145\"><p><a href=\"display_category.php?category_id=" . $row_rsCategories['category_id'];. "\">View</a> | <a href="edit_category.php?category_id=" . $row_rsCategories['category_id'] . "">Edit</a> | <a href=\"javascript:BRB_PHP_DelWithCon('delete_category.php','category_id'," . $row_rsCategories['category_id'] . ",'Are you sure you want to delete this category?');\">Delete</a></p></td>\n
</tr>\n";

}
?>

</table>

next post i'll recommend a different way of handling your situation.

[sde]

ok, let me know if this helps. read the comments.

PHP Code:

<?
// build categories array at the top of every page
$result = mysql_query("select c.category_id, c.category_name, c.category_description, count(a.article_id) as count from np_categories as c
  left join np_articles as a on c.category_id=a.article_category_id
  group by c.category_id
  order by c.category_name"); 

// declare an array (just a safeguard for when we use foreach below)
$categories = array();

while ($row = mysql_fetch_assoc($result)) {
  $categories[$row['category_id']] = $row;
}

// buld your table with the array
foreach ($categories as $category) {

    // use the ternary operator to easily switch your colors in 1 line
  $color = ($color == $color1) ? $color2 : $color1;
  
  // print your category menu
  echo "<tr style=\"background-color:$color\">\n
  <td width=\"100\"><p>" . $category['category_name'] . " (" .$category['count'] . ")</p></td>\n
  <td width=\"300\"><p>" . $category['category_description']. "</p></td>\n
  <td width=\"145\"><p><a href=\"display_category.php?category_id=" . $category['category_id'];. "\">View</a> | <a href="edit_category.php?category_id=" . $category['category_id'] . "">Edit</a> | <a href=\"javascript:BRB_PHP_DelWithCon('delete_category.php','category_id'," . $category['category_id'] . ",'Are you sure you want to delete this category?');\">Delete</a></p></td>\n
</tr>\n";

}

// ok, so now we're on display_category.php.
// this could be on the same page as the menu table
// or a different one, the important thing is that our
// $categories array is defined.

// now we set the current category from the category
// defined in the URL query string
$current_category = $categories[$_GET['category_id']];

echo "The current category is: " . $current_category['category_name'];
?>

[bayruni]

It works! I just had to add the count to my already created recordset, change a few things, and everything came out the way I wanted, thank God.

I searched many forums for help and this is the only one I had success in. Thank you so much, sde. You should write a tutorial regarding this topic. Your solution was the simples and most understandable one so far. I hate to think of the other ones!

In case another beginner runs into the same problem, I'm going to add my final query and the table column I had problems with. Who knows, it might be of use to someone.
<?$query_rsCategories = "SELECT *, COUNT(a.article_id) AS count FROM np_categories AS c LEFT JOIN np_articles AS a ON c.category_id=a.article_category_id GROUP BY c.category_id ORDER BY category_name ASC"; ?>
<td width="100"><p><?php echo $row_rsCategories['category_name']; ?> (<? echo $row_rsCategories['count']; ?>)</p></td>

Once again sde, thanks for your help. If you can spare some more time, please, can you just explain the the count query for me so I at least understand why it now works? I don't understand why you can use shortcuts like a and c for example.

Thanks again, very much, for your help.

[sde]

great glad i could help.

count() is a mysql function that returns the number of rows found in your query. you can find out other mysql functions by looking through their documentation. there are a lot of other helpful database functions that you will need to use from time to time. count is one of the more common ones though.

the abbreviations for the table names are just that, abreviations.

Take this query for example:

select c.id, p.number from customers as c
  left join phone_numbers as p on c.id=p.customer_id
  where c.id=153

You have a customer table, and a phone numbers table with a 1 to many relationship. We are simply selecting all the phone numbers for customer id 153. The abbreviation just makes it less to type.

Below is the same query without abbreviations:

select customer.id, phone_numbers.number
  from customers, phone_numbers
  left join customers on customers.id=phone_numbers.customer_id
  where customers.id=153

it's a little more typing. it's nice to only have to type the table name once, and then use 1 letter to reference it later in the query.

[bayruni]

Thanks for the explanation. Now I have a better idea of how things work. Thank you.