Page 1 of 1

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

Posted: Fri Mar 10, 2006 6:39 am
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

Posted: Fri Mar 10, 2006 6:45 am
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

Posted: Fri Mar 10, 2006 6:59 am
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...

Posted: Fri Mar 10, 2006 7:58 am
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";