How do I retrieve category from db then show the number of i

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

How do I retrieve category from db then show the number of i

Post by mattcooper »

Hi all,

I'm trying to display a list of categories (successfully) and the number of items in them (unsuccessfully) with the following code:

Code: Select all

$sql = "SELECT DISTINCT category FROM catalogue";
$result = mysql_query($sql); // Issue the query

$category = $sql ->  category;

$sql = "SELECT prod_id FROM catalogue";
$result2 = mysql_query($sql);
$rows = mysql_fetch_array($result2);
foreach ($rows as $count){
$count=mysql_num_rows($result2);
}

echo "Please select a category<p><ul>";
while($output = mysql_fetch_array($result)){
echo "<li><a href=\"{$PHP_SELF}?pagename=shop&ecommerce=on&category=$output[category]\">".$output[category]."</a>".$count."</li><br>";
}
echo "</ul>";
mysql_close();
I'm getting the category name with the total number of rows returned next to each instead of the number of items in them.

I'm not too hot with working with arrays at the moment and could really use some help!!

Thank you in advance,

Matt
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Code: Select all

$sql = "SELECT category, COUNT(category) as total FROM catalogue GROUP BY category";
$result = mysql_query($sql); // Issue the query
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Post by mattcooper »

Thank you for the fast reply!

OK, I pasted the code you suggested into my script and removed a lot of the dead wood. It is now:

Code: Select all

$sql = "SELECT DISTINCT category, COUNT(category) as total FROM catalogue GROUP BY category"; 
$result = mysql_query($sql); 

echo "Please select a category<p><ul>";

while($output = mysql_fetch_array($result)){
echo "<li><a href=\"{$PHP_SELF}?pagename=shop&ecommerce=on&category=$output[category]\">".$output[category]."</a>".$count."</li><br>";
}
}
echo "</ul>";
mysql_close();
I'm now getting no output... I added DISTINCT, it makes no difference in the grand scheme of things.

How else does the original code need to be edited?

Thanks again...
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

try this:

"SELECT category_id,category_name, COUNT(catalog_id) as total FROM category LEFT JOIN catalog ON catalog_id=category_id GROUP BY category_id";
Post Reply