Total Number of Each Item in Database...

cyronuts

It's been awhile, hello everyone. I'm stumped on something basic here.

I have a table called orders, which lists all the orders I've had on my online store. I'd like to create a table that looks like this:

Product Quantity Sold

CPQ1000 3
Dell1000 2

and so forth. The field name for product is ItemNumber. So here is my sql so far, and all it does is return ItemNumber the number of times it appears in the table, which ain't right. The query starts when the user selects a value named 'AllProducts', which should total the sale of each product sold, and then create the list like above:


<?
include("dbconnect.php");
$query = "select * from orders order by itemNumber"; //where prod_name is the product name
$result = mysql_query($query, $mysql_link);

$products = "";
 while($row = mysql_fetch_row($result))
{
if($product == $row[2]){}
else{
echo $row[2]."<br>";
$product = $row[2];
}
}

?>

Thanks in advace!!

neon

I didn't quite get you database table structure, could you be a litle bit more precise in that. Seems more like database operation than some PHP level thing...

seby

well you can do something like this:


$result1 = mysql_query("SELECT COUNT(*) AS sold FROM orders WHERE itemNumber = 'CPQ1000'");
$CPQ1000 = mysql_fetch_array($result1);

$result2 =  mysql_query("SELECT COUNT(*) AS sold FROM orders WHERE itemNumber = 'Dell1000'");
$Dell1000 = mysql_fetch_array($result2);

echo 'Dell1000 Sold:' . $Dell1000[sold] . '<br>';
echo 'CPQ1000  Sold:' .  $CPQ1000[sold] . '<br>';

cyronuts

Yes, sorry for being obtuse!

The database structure is such that when the product is sold, a new row is written, with two important fields -- the itemNumber and the qty. So, if a purchase of three CPQ1000s are made, a row is written, which fills itemNumber with CPQ1000 and qty with 3. Does that make more sense? What I am trying to do is allow a user to generate a report, based on everything in the orders table, with a proper count of each product (itemNumber) sold.

--Cyronuts

seby

i posted what you need ..

neon

seby: what if there are 100 different products....

cyronuts : Ok, try this

$connection = mysql_connect("host","user","passwd");
mysql_select_db("your_db");
$sql = "SELECT SUM(qty) as sum, itemNumber FROM your_table GROUP by itemNumber";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result)) {
         echo $row[1].' : '.$row[0]."<br>\n";
}
mysql_close($connection);

seby

Originally posted by neon
seby: what if there are 100 different products....

well i was only going by the two products listed.. this will work too, just an extra query.


$result = mysql_query("SELECT itemNumber FROM your_table");
    while($row = mysql_fetch_array($result)) {

$result2 = mysql_query("SELECT COUNT(*) AS sold FROM orders WHERE itemNumber = '".$row['itemNumber']."'");
$totals  =  mysql_fetch_array($result2);

	echo $row[itemNumber] . ':' .$totals[sold];

}