Adding Field Results

[unplugged_web]

I want to be able to add all of the entries in one field and then subtract the total from the total of another field. I know how to subtract the results but I don't know how to get the results if that makes sense.So that's my question, how do I add all of the entries from a whole field together?Thanks

[justsomeguy]

How do you have more than one entry in a field? Can you show any code? Are we talking form submission here?

[unplugged_web]

How do you have more than one entry in a field? Can you show any code? Are we talking form submission here?

Sorry I my mistake I meant row not field. I've got a row with the cost of an item in it and another row with the postage. I want the total cost of every item and the total cost of ever item's postage.

[justsomeguy]

OK, it sounds like loops plus addition, which shouldn't be very difficult. Do you have any code yet?

[unplugged_web]

OK, it sounds like loops plus addition, which shouldn't be very difficult. Do you have any code yet?

So far I've got:

<? php$sql="select * from item_details";$sql="select * from postage_details";?>

and then within the html of the page I've got:

<?=$row[item_price'] - $row['pp_price'] ?>

but that only gives me the total of the two rows, I don't know how to add all of the entries together first.Thanks

[justsomeguy]

You just need to set up a loop that loops through all of the rows, the same way you loop through any MySQL result, and keep a variable to hold the total that you add to each time through the loop.

[unplugged_web]

You just need to set up a loop that loops through all of the rows, the same way you loop through any MySQL result, and keep a variable to hold the total that you add to each time through the loop.

Sorry but how do I do that?

[justsomeguy]

$result = mysql_query(...);$total = 0;while ($row = mysql_fetch_assoc($result)){  $total += $row['field'];}

[unplugged_web]

$result = mysql_query(...);$total = 0;while ($row = mysql_fetch_assoc($result)){  $total += $row['field'];}

okay, thanks that will add the result before I subtract them?Also I've noticed that for the postage table some of the entries have multiple postage amounts - can I only add the highest postage amount for each product id?thank you for helping

[Ingolme]

It can be done in SQL, rather than only PHP:

$q = mysql_query("SELECT SUM(column1)-SUM(column2) AS total FROM table");$results = mysql_fetch_array($q);echo "The difference between the two columns is: " . $results['total'];

[justsomeguy]

okay, thanks that will add the result before I subtract them?

That will add all values in a single column throughout the entire row set.

can I only add the highest postage amount for each product id?

You can do whatever you want, use if statements to figure out which one is the highest.

[unplugged_web]

It can be done in SQL, rather than only PHP:

$q = mysql_query("SELECT SUM(column1)-SUM(column2) AS total FROM table");$results = mysql_fetch_array($q);echo "The difference between the two columns is: " . $results['total'];

okay now I'm confused. This is what I've now got

$result = mysql_query("select * from items");$total = 0;while ($row = mysql_fetch_assoc($result)){  $total += $row['item_price'];}$result = mysql_query("select * from postage");$total = 0;while ($row = mysql_fetch_assoc($result)){  $total += $row['pp_price'];}

But that won't work out if the same product id has more that one postage entry and only add the highest postage amount for each product id?

[justsomeguy]

What that code does is select all rows from the items table, and then calculates the total of the item_price column, then sets the total back to 0, then gets every row from the postage table, then calculates the total of all of the pp_price fields. So you end up with the total of all pp_price fields in the postage table. Is that what you're trying to do?

[unplugged_web]

What that code does is select all rows from the items table, and then calculates the total of the item_price column, then sets the total back to 0, then gets every row from the postage table, then calculates the total of all of the pp_price fields. So you end up with the total of all pp_price fields in the postage table. Is that what you're trying to do?

Yep that's exactly what I'm looking for, but how do I use the if statement to get only the highest pp_price for each product_id? Also after doing that l can then use this to display the results:

<?=$row[item_price'] - $row['pp_price'] ?>

And how would I get the results to dynamically load into a page link thisThanks

[justsomeguy]

I don't know how your database is structured, but you can probably use a SQL statement.SELECT product_id, MAX(pp_price) AS pp_price FROM postage GROUP BY product_idAssuming the product ID is stored in that table, that will return each product ID in the table and the highest price for it.

[unplugged_web]

I don't know how your database is structured, but you can probably use a SQL statement.SELECT product_id, MAX(pp_price) AS pp_price FROM postage GROUP BY product_idAssuming the product ID is stored in that table, that will return each product ID in the table and the highest price for it.

Brilliant. Thank you