Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Newbie question, counting products in each category
- Thread starter sevenone
- Start date
sleipnir214
Programmer
Fetch a list of all category IDs. Loop through that list.
For each member of the list:[ul][li]fetch a count of all products that fall into that category.[/li][li]Update the category's product count[/li][/ul]
Want the best answers? Ask the best questions!
TANSTAAFL!!
For each member of the list:[ul][li]fetch a count of all products that fall into that category.[/li][li]Update the category's product count[/li][/ul]
Want the best answers? Ask the best questions!
TANSTAAFL!!
- Thread starter
- #3
OK I tried this below but can't get the reults I want. I can display the number of products in each category but when I try to update the product_count field, it only updates with the number of products for the very last category, so all the product_counts are set to 17.
What am I doing wrong?
sevenone,
<html>
<body>
<?
$connection=mysql_connect("localhost","user","password");
if (!$connection){
echo "Could not connect to server";
exit;
}
$db=mysql_select_db("test",$connection);
if (!$db){
echo "Could not connect to database!";
exit;
}
$sql ="SELECT * FROM xcart_categories ORDER BY category";
$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result);
if ($num_rows == 0){
echo "No records";
} else {
while ($row=mysql_fetch_array($mysql_result))
{
$cat=$row["categoryid"];
$products=$row["product_count"];
$name=$row["category"];
#Display Results
echo "$cat: $name: $products <br> ";
$noofprods="SELECT * FROM xcart_products WHERE categoryid=$cat";
$noofprodsresult=mysql_query($noofprods,$connection);
$counted=mysql_num_rows($noofprodsresult);
mysql_query("UPDATE xcart_categories SET product_count=$counted");
echo "$counted <br>";
} }# end of else
?>
</body>
</html>
What am I doing wrong?
sevenone,
<html>
<body>
<?
$connection=mysql_connect("localhost","user","password");
if (!$connection){
echo "Could not connect to server";
exit;
}
$db=mysql_select_db("test",$connection);
if (!$db){
echo "Could not connect to database!";
exit;
}
$sql ="SELECT * FROM xcart_categories ORDER BY category";
$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result);
if ($num_rows == 0){
echo "No records";
} else {
while ($row=mysql_fetch_array($mysql_result))
{
$cat=$row["categoryid"];
$products=$row["product_count"];
$name=$row["category"];
#Display Results
echo "$cat: $name: $products <br> ";
$noofprods="SELECT * FROM xcart_products WHERE categoryid=$cat";
$noofprodsresult=mysql_query($noofprods,$connection);
$counted=mysql_num_rows($noofprodsresult);
mysql_query("UPDATE xcart_categories SET product_count=$counted");
echo "$counted <br>";
} }# end of else
?>
</body>
</html>
sleipnir214
Programmer
Reformatting your code for greater readability:
I notice that you are not selecting a count of the number of products in a given category. You are fetching the products in a category themselves. (The line in question is highlighted in blue.)
There is an entry in the MySQL online manual titled "Counting rows", which you may find interesting.
Also I notice that you are switching between HTML output mode and PHP-interpreted mode in your code. My own findings, which I posted in thread434-843309 in this forum, indicate that this impedes performance. You are better of using a single "<?php...?>" tag-pair that encompasses the entire script and outputting HTML through print or echo statements.
Want the best answers? Ask the best questions!
TANSTAAFL!!
Code:
<html>
<body>
<?
$connection=mysql_connect("localhost","user","password");
if (!$connection)
{
echo "Could not connect to server";
exit;
}
$db=mysql_select_db("test",$connection);
if (!$db)
{
echo "Could not connect to database!";
exit;
}
$sql ="SELECT * FROM xcart_categories ORDER BY category";
$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result);
if ($num_rows == 0)
{
echo "No records";
}
else
{
while ($row=mysql_fetch_array($mysql_result))
{
$cat=$row["categoryid"];
$products=$row["product_count"];
$name=$row["category"];
#Display Results
echo "$cat: $name: $products <br> ";
[blue]$noofprods="SELECT * FROM xcart_products WHERE categoryid=$cat";[/blue]
$noofprodsresult=mysql_query($noofprods,$connection);
$counted=mysql_num_rows($noofprodsresult);
mysql_query("UPDATE xcart_categories SET product_count=$counted");
echo "$counted <br>";
}
}# end of else
?>
</body>
</html>I notice that you are not selecting a count of the number of products in a given category. You are fetching the products in a category themselves. (The line in question is highlighted in blue.)
There is an entry in the MySQL online manual titled "Counting rows", which you may find interesting.
Also I notice that you are switching between HTML output mode and PHP-interpreted mode in your code. My own findings, which I posted in thread434-843309 in this forum, indicate that this impedes performance. You are better of using a single "<?php...?>" tag-pair that encompasses the entire script and outputting HTML through print or echo statements.
Want the best answers? Ask the best questions!
TANSTAAFL!!
- Status