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
mattcooper
Forum Contributor
Posts: 210 Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK
Post
by mattcooper » Fri Mar 10, 2006 6:39 am
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
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Fri Mar 10, 2006 6:45 am
Code: Select all
$sql = "SELECT category, COUNT(category) as total FROM catalogue GROUP BY category";
$result = mysql_query($sql); // Issue the query
mattcooper
Forum Contributor
Posts: 210 Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK
Post
by mattcooper » Fri Mar 10, 2006 6:59 am
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...
newmember
Forum Contributor
Posts: 252 Joined: Fri Apr 02, 2004 12:36 pm
Post
by newmember » Fri Mar 10, 2006 7:58 am
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";