product groups with quantity

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

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

product groups with quantity

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

use the SUM aggregate function.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
Post Reply