UPDATE in WHILE loop only adding last value and not all values

[zdvdla]

 

I am trying to get info from a table, add rows in that table, and put the added value back into the table in another field...I want to add field1 and field2 of every row and put the total in the total row.

Heres what the echo gives me

 

Susan 14

 

Mary 18

 

Bob 13

 

Sam 21

 

heres the database

mysql> SELECT total_val FROM table;

 

+-----------+

| total_val |

+-----------+

|        21 |

|        21 |

|        21 |

|        21 |

+-----------+

 

 

 

heres the code....

 

 

<?php

 

require("connection.php");

 

 

mysql_select_db("database", $connection);

echo "<br />";

 

$result = mysql_query("SELECT * FROM table");

 

while($row = mysql_fetch_array($result))

  {

 

  $values = ($row['field1'] + $row['field2']);

 

 

 

 

$sql=mysql_query("UPDATE table SET total = '$values'");

 

 

  echo $row['user_name'] . " " . $values;

  echo "<br />";

   

  }

 

?>

 

also note that if I put a INT in place of $values - the echo changes to match...why??

 

PLEASE help...I have been working on this for 2 entire days....

 

 

 

[Pikachu2000]

There should be no reason to store the total of 2 fields in a third field. You can get that figure on the fly.

[sasa]

you  need WHERE part in your update query

without this query update all rows

[zdvdla]

thank you for your reply...i am very new to this and was needing to sort the table by the third field. I don't have enough knowledge to do what I needed...could you help me understand the best way to do that?

 

What I really need to do:

 

I have a table that is populated by a form that users send in.

I need to compare that user data(user table) so I have another table(compare table) that compares certain fields to those the user sent in.

The compare table is based on another user form, via pulldown menu as well. Which will be inserted periodically, and not completely at once. Eventually will be complete, but during the process, at any time, I want to see the status so I compare the current (incomplete compare table) with the user(complete user table).

If some of those fields match, I need to add some of the user fields together, and display the result in a DEC sorted manner.

 

I have no clue how to do this so here's what I did...

 

I created a form that users send in data via pulldown menus.

This data gets inserted to a table.(user table)

Another form creates the compare table.

Since the compare table will be completed over a span of time,( I didn't know how to compare and sort well),

I created another table to hold the current comparable values of both tables.

After comparing, is the compare is true, I needed to add some of the values in specific fields in the user table, then display them in DEC order.

 

i am certain that you will have a better way to do it, but it's my first php project ever and it was due today...and it worked...I won't tell you how many hours I spent on it, but it was ridiculous.

Any advice on how to refine this would be greatly appreciated.

Thank you..

 

oh and btw...

the answer to my question above:

 

$sql=mysql_query("UPDATE table SET total_val = '$values' WHERE id=$row['id']");

 

needed that where statement - otherwise it was filling in the entire column......

 

 

[zdvdla]

thanks sasa! I was writing a reply when you sent yours...thanks for input...you were right!

[sasa]

in SQL you can ORDER BY (field1 + field2)