MySQL update query is not working correctly in a LOOP

Question

I am having a very annoying problem related to updating multiple table rows in a loop. My code updates only first row and not the others. In fact it throws an error while updating other rows.

Here is my prototype brief code to the point:

// This query returns multiple rows
$query = "SELECT * FROM `table_name` ...";
$rs = mysqli_query($con, $query);

if(mysqli_num_rows($rs) != 0)
{
    while($row = mysqli_fetch_array($rs))
    {
        $column_name= $row['column_name'];
        ...
        ...
        ...

        // This update query only updates first row which matches the given condition. On second loop, it throws an error.
        $update = "UPDATE `table_name` SET `column_name` = '" . mysqli_real_escape_string($con, $column_name) . "', ... ";
        $rs = mysqli_query($con, $update);
    }
}

So my above code runs successfully only once. On second attempt, it outputs following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\test\update.php on line 11

This is my first ever attempt to run update query in a loop. I never used it before. I think I might be doing it all wrong way. What would be the solution to fix my issue or any better way to achieve the same?

EDIT

OOPS!!! Again unnecessary $rs after update query and it drove me crazy!

Possible duplicate of [mysql\_fetch\_array()/mysql\_fetch\_assoc()/mysql\_fetch\_row()/mysql\_num\_rows etc... expects parameter 1 to be resource](https://stackoverflow.com/questions/2973202/mysql-fetch-array-mysql-fetch-assoc-mysql-fetch-row-mysql-num-rows-etc) — Qirel, Apr 08 '18 at 14:09
you overwrote `$rs` in your loop when you performed an update. Try using a secondary var like `$rs2` — mcv, Apr 08 '18 at 14:10
It's also not a good idea to do queries in a loop. Chances are you can reduce this down to a single query instead, but it's hard for us to tell, as you have snipped out all the relevant part of the code. — Qirel, Apr 08 '18 at 14:10
@Qirel how can we do that in a single query. I am quite interested to know that. — Sachin, Apr 08 '18 at 14:24

score 2 · Accepted Answer · answered Apr 08 '18 at 14:10

2

You are overriding the $rs value after the first update. So you can't do another mysqli_fetch_array($rs) after that, because $rs is not the result of the first mysqli_query().

$rs2 = mysqli_query($con, $update);

answered Apr 08 '18 at 14:10

Syscall

19,327
10
37
52

MySQL update query is not working correctly in a LOOP

1 Answers1