Page 1 of 1

product groups with quantity

Posted: Mon Mar 21, 2005 3:45 am
by rsmarsha
Not sure whether this should be in php code or db's as it uses both. :)

I am searching the db for, in this case cpu's and grouping them by cpu type.

The code is fine for most queries but some have a quantity in the order as well. If q is more than 1 i need it to take that into account.

Current code is:

Code: Select all

$cpu = "SELECT count(cpu) as amount,cpu,q FROM orders WHERE status='Processing' GROUP BY cpu"; 
$cpuq = mysql_query($cpu, $db_conn) or die("Query $cpu failed".mysql_error());
while ($cpurow = mysql_fetch_array($cpuq))
{
	if ($cpurow['q']>1)
	{
		$needed = $cpurow['amount']*$cpurow['q'];
	}
	else
	{
	$needed = $cpurow['amount'];
	}
This is fine for 2 different cpu's as it shows the quantity correct, but when there are 2 of the same, the query groups them together and the if doesn't work. Any ideas?

Example:

If i had the following in the orders table

Code: Select all

cpu                 Quantity

cpu1                1
cpu1                5
cpu2                5
this would show cpu2 as having a value of 5 and cpu 1 as 10 for some reason.

Where as :

Code: Select all

cpu                 Quantity
cpu1                1
cpu2                5
will show cpu1 as having 1 and cpu2 as having 5 which is what i want.


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Mar 21, 2005 7:45 am
by feyd
use the SUM aggregate function.

Posted: Tue Mar 22, 2005 4:12 am
by rsmarsha
OK i tried adding it in this form:

Code: Select all

$cpu = "SELECT count(cpu) as amount,cpu,sum(q) as q FROM orders WHERE status='Processing' GROUP BY cpu"; 
$cpuq = mysql_query($cpu, $db_conn) or die("Query $cpu failed".mysql_error());
echo '<tr bgcolor="#FFFFFF">';
echo '<td colspan="2"><b><FONT style="FONT-SIZE: 8pt; size: 2" face=Arial color=black>CPU\'s</b></td>';
echo '</tr>'; 
while ($cpurow = mysql_fetch_array($cpuq))
{
	if ($cpurow['q']>1)
	{
		$needed = $cpurow['amount']*$cpurow['q'];
	}
	else
	{
	$needed = $cpurow['amount'];
	}

It still doesn't add up correctly. I need it to group results by all cpu's of the same type, then using the quantity field "q" add all the quantities together for each group.

I will keep trying, but nothing so far.

Posted: Tue Mar 22, 2005 4:15 am
by rsmarsha
Ok i think i must have misunderstood the SUM funtion, i know have this :

Code: Select all

$cpu = "SELECT cpu,SUM(q) as q FROM orders WHERE status='Processing' GROUP BY cpu"; 
$cpuq = mysql_query($cpu, $db_conn) or die("Query $cpu failed".mysql_error());
echo '<tr bgcolor="#FFFFFF">';
echo '<td colspan="2"><b><FONT style="FONT-SIZE: 8pt; size: 2" face=Arial color=black>CPU\'s</b></td>';
echo '</tr>'; 
while ($cpurow = mysql_fetch_array($cpuq))
{
	$needed = $cpurow['q'];
echo '<tr bgcolor="#FFFFFF">';
echo '<td><FONT style="FONT-SIZE: 8pt; size: 2" face=Arial color=#0066FF>'.$cpurow['cpu'].'</td><td align="center"><FONT style="FONT-SIZE: 8pt; size: 2" face=Arial color=#0066FF>'.$needed.'</td>';
echo '</tr>'; 
}
It seems to work at the moment, just going to test it more fully. Does the code look about right? Strange how writing out the last reply gave me the idea of how it could be done.

Oh and sorry for not putting my code in right for the first post.