use a while mysql_fetch_array and UPDATE during the loop

Question

i am trying to pull data from one table, compare columns to a variable, then if they match, add them and update a field on another table. It seems that the UPDATE only works once inside the while loop, and puts the value of that one time in every row of that column throughout the table. Odd thing is, when I echo it, all the values are correct (unique)..but only when I echo...when I look at table with terminal, all the rows of that column are identical. I have read that you cannot use 2 query at a time...then again...I have read that you can, if the first one is outside the loop...please help...

I want to add field1 and field2 of every row and put the total in the total row.

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']);

echo "values = " . $values;


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


echo $row['user_name'] . " " . $values;
echo "<br />";

}

?>

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 |
+-----------+

the ech totals are exactly what I want. I just need them put inside the total_val column.

Shakti Singh · Accepted Answer · 2011-02-27 08:22:56Z

1

Update the specific (current) row in the while loop

Currently it is doing like this when while loop reach at the end of iteration it has the total 21 and it updates table with value 21 hence all the rows contains 21.

Your update Query is updating all rows every time put here condition for the unique id to update

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

edited Feb 27, 2011 at 8:22

answered Feb 27, 2011 at 8:16

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zdvdla Over a year ago

AWESOME!!! it worked!!! Thank you... I have spent 2 days trying to figure this out...thank you again!!

Shakti Singh Over a year ago

@zdvdla: My pleasure, Your welcome, Give value to the answer by accepting it.

zdvdla Over a year ago

in this instance I have a bit more going on but thought I could do the same thing as above-with the adjustment of changing [id] for user_name, since the ids would not match table to table, but the user_name does...I know I am biting off alot for my first project...but I am really liking this coding stuff...hope I am not pulling on too many shirt tails...thanks for your help....

delphist · Accepted Answer · 2011-02-27 08:15:18Z

0

Current query updates all rows in database.

Try this:

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

answered Feb 27, 2011 at 8:15

delphist

4,5591 gold badge24 silver badges22 bronze badges

Collectives™ on Stack Overflow

use a while mysql_fetch_array and UPDATE during the loop

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related