[SOLVED] WHILE ERROR....

[wmguk]

Hey,

 

I have a script for a shopping cart im building.. its getting there now, basically i'm now trying to put the order details in tables and calculate them...

 

TABLE : ORDERS_DETAIL

 

ID | PRODNAME | PRODCODE | PRODPRICE | PRODQTY

5 | TEST ITEM | TEST0010 | 200   | 2

5 | TEST ID   | TEST0014 | 100   | 4

5 | DIFF ITEM | TEST0017 | 300   | 5

 

so total for order 5 is (2 x 200) + (4 x 100) + (5 x 300)

 

subtotal is 2300

 

TABLE : ORDERS

 

ID | NAME | SUBTOTAL | DEL | TOTAL

5  | JOHN  |  2300        | 40  |  2340

 

my code currently works out the subtotal for each row, not the whole order...

 

what i need to do is say, whats the total for the first row, what the total for the second row etc etc etc and add them together...

 

How can I do that?

 

this is my code:

if ($cart) {
	$items = explode(',',$cart);
	$contents = array();
		foreach ($items as $item) 	{
			$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
									}

		foreach ($contents as $id=>$qty) {
			$productsql = "SELECT * FROM products WHERE code = '$id'";
			$productresult = mysql_query($productsql);
			while($prod = mysql_fetch_array($productresult)) 
			{
			$pcode = $prod['code'];
			$pname = $prod['name'];
			$pprice = $prod['price'];
			}

			//FIND SUBTOTAL FOR EACH ITEM
			$subt = $pprice * $qty;

	//INSERT DETAILS - MULTI LINE RESULT		
	$ins2 =  "INSERT INTO orders_details (order_id, prodname, prodcode, prodprice, prodqty)
				VALUES ('$ordid', '$pname', '$pcode', '$pprice', '$qty')";
	mysql_query($ins2);

		}

	//INSERT ORDER - SINGLE LINE RESULT	
$ins = 	"INSERT INTO orders (order_id, name, add1, add2, town, county, postcode, subtotal, delivery, total, ponum)
		VALUES ('$ordid', '$name', '$add1', '$add2', '$town', '$county', '$postcode', '$subt', '$del', '$tot', '$ponum')";
mysql_query($ins);

		}

[wmguk]

any thoughts on this?

 

i know what i need to do, just not how to do it..

 

i think i need to run a while there is a product with a qty then = the item subtotal...

while there is an item subtotal add them together = order subtotal....

 

but i just cant see how to run it...

[dgoosens]

i guess the easiest way would be to get all the products of a particular ID

 

SELECT * FROM ORDERS_DETAIL WHERE ID = '$id'

 

and then loop through them with a WHILE

and do something like this

$total = 0;
while($res = mysqli_fetch_array...)
{
   $total += $res[PRODPRICE ]*$res[PRODQTY];
}

 

then, after the while loop, you insert all this into ORDERS

[wmguk]

i thought I could do this before the main insert as this is the actual inserting page, but yes, i guess write it all to the DB, then query and do the add, then write the calculation as an update...

 

 

hmmmm

 

interesting

 

thank you

[dgoosens]

well,

you can... it doesn't change anything

 

just update your $total with every $subt at the end of your loop

 

$total += $subt;

 

and upload the whole lot to the DB afterwards...

[redarrow]

Why not use a join and get all the info then sum with that join then update database.

 

would that work using SUM and joins?

[wmguk]

ah its cool thank you all for your help! its working!!