Select and update the same row using while loop

Question

There is a looping query (while loop) to select 2 column values from a table, then the code sums the 2 values, what I need is to update another column with the summed value> Here is the code:

$stmt = $conn->prepare('select value1, value2, valuse3  from  eg.table limit ? ');
$stmt->bind_param('i',$limit);
$stmt->execute();
$stmt->bind_result($value1, $value2,$value3);
$arr = array();
while($stmt->fetch()) {
$row = array();
array_push($arr, $row);
$newvalue=($value1+$value2);

$stmt1 = $conn->prepare("UPDATE eg.table SET eg.column=? WHERE  eg2.column=?");
$stmt1->bind_param('ii',$newvalue , $value3);
$stmt1->execute();
}

When I used this queries I got this error: Fatal error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\so\new\socialrank.php on line 13

If you have another solution to achieve the job using Mysqli it's okay. Any help will be appreciated, Thanks in advance.

It's usually bad practice to execute SQL queries inside loops. It would be better to build a bulk query in the loop and execute it once afterward. — Stuart Wagner, May 31 '15 at 02:11
Apparently your statement could not be prepared and the operation returned false (hence boolean). Apart from that, I don't really understand what you're trying to achieve. I assume valuse is a typo for value? And where did table eg2 in the WHERE clause come from? — Tomaso Albinoni, May 31 '15 at 02:18
@StuartWagner I agree with you, but I don't know how can I do that (I mean the bulk query), I thought about using JOIN but I even didn't imagine how can I write it to select and update after making a calculating process. — DevManX, May 31 '15 at 02:36
@TomasoAlbinoni You know, when I perform each query alone both of them work! My mind now is 0, no solutions come to it :) so if you have a suggested solution, please post it. — DevManX, May 31 '15 at 02:39
As I said, I don't understand what you're trying to do. So until you explain, I won't have a solution. — Tomaso Albinoni, May 31 '15 at 02:49

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

I got the solution. This query do the same thing I was looking for:

$sql = ("UPDATE eg.table AS c1, eg.table AS c2 
SET c1.eg.column = (c2.value1 + c2.value2)
WHERE c2.eg2.column = c1.eg2.column
");

Note: this solution is inspired by @CakePHP answer

edited May 23 '17 at 12:30

Community

1
1

answered May 31 '15 at 05:34

DevManX

476
3
14

Select and update the same row using while loop

1 Answers1