UPDATE query through foreach loop in mysql/php

Question

I do have an array something like this:

[cuisines] => Array
    (
        [0] => 17
        [1] => 20
        [2] => 23
        [3] => 26
    )

Now I need to update mysql table with these values. All values belong to one user.

So I tried it like this:

if (isset($_POST['cuisines'])) {    
    $cuisines = $_POST['cuisines'];         
} else {
    $error_alert[] = "Please select at least one cuisine";
}   

if (empty($error_alert)) { // If everything's OK... 

    // Make the update query:
    $sql = 'UPDATE restaurant_cuisines 
                        SET restaurant_id = ?
                            , cuisine_id = ?  
                    WHERE restaurant_id = ?'; 

    $stmt = $mysqli->prepare($sql);
    // Bind the variables:
    $stmt->bind_param('iii', $restaurant_id, $cuisine_id, $restaurant_id);

    foreach ($cuisines as $value) {
        $cuisine_id = $value;
        // Execute the query:
        $stmt->execute();       
    }   

    // Print a message based upon the result:
    if ($stmt->affected_rows >= 1) {
        echo 'updated';

    } 
    // Close the statement:
    $stmt->close();
    unset($stmt);
}

But this query not updating mysql correctly. This is what I get running this script.

mysql> select * from restaurant_cuisines where restaurant_id = 4;
+---------------+------------+
| restaurant_id | cuisine_id |
+---------------+------------+
|             4 |         26 |
|             4 |         26 |
|             4 |         26 |
+---------------+------------+
3 rows in set (0.00 sec)

What would be the problem of this script? Hope somebody may help me out.

Thank you.

`...SET restaurant_id = ?, ... WHERE restaurant_id = ?';`? Why are you trying to update a value with the value that you have in your `WHERE` clause. If the value is the same it will never change. If the value was changed, it will not be in the table, so it will never change. — Sean, Jul 21 '15 at 16:36
What is your desired result? It looks like it is working to me. You are updating the same `restaurant_id` in your loop, so it will end with the last array value. — Sean, Jul 21 '15 at 17:42

Alex Tartan · Answer 1 · 2015-07-21T17:07:26.670

2

You need to bind your parameters in the loop:

// Delete old entries:
$sqlDelete = 'DELETE FROM restaurant_cuisines WHERE restaurant_id = ?'; 
$stmtDelete = $mysqli->prepare($sqlDelete);
$stmtDelete->bind_param($restaurant_id);
$stmtDelete->execute(); 
$stmtDelete->close();
unset($stmtDelete);  

// now prepare to insert new values
$sqlInsert = 'INSERT INTO restaurant_cuisines (restaurant_id,cuisine_id) 
              VALUES (?,?)'; 
$stmtInsert = $mysqli->prepare($sqlInsert);

foreach ($cuisines as $value) {

    // Bind the variables:
    $stmtInsert->bind_param($restaurant_id, $value);

    // Execute the query:
    $stmtInsert->execute();    

    // Print a message based upon the result:
    if ($stmtInsert->affected_rows >= 1) {
        echo 'updated';
    }   
}   
// Close the statement:
$stmtInsert->close();
unset($stmtInsert);

edited Jul 21 '15 at 17:07

answered Jul 21 '15 at 16:30

Alex Tartan

6,736
10
34
45

1

You only need to prepare the query once. Then bind and/or execute with different parameters upon each iteration. See [this post](http://stackoverflow.com/questions/11484630/when-making-the-same-pdo-query-with-changing-parameters-do-i-call-prepare-e). – showdev Jul 21 '15 at 16:31
this is wrong. the purpose of `->prepare()`/->bind_param()` is that it is only called once, outside a loop, and then you only need to change the param value(s) in the loop to excecute. – Sean Jul 21 '15 at 16:32
I tried it like this. But it doesn't work for me.. problem is still same – user3733831 Jul 21 '15 at 16:41
Where do you have `$restaurant_id` defined? Also it's not needed in the `SET ... ` – Alex Tartan Jul 21 '15 at 16:42
it comed session. in this case it is `4` – user3733831 Jul 21 '15 at 16:43
Only updating 3 rows with last value of `cuisines` array. – user3733831 Jul 21 '15 at 16:45
My updated foreach - `foreach ($cuisines as $value) { $stmt->bind_param('ii', $value, $restaurant_id); $stmt->execute(); }` – user3733831 Jul 21 '15 at 16:52
@AlexTartan. I too think like that.. Probably it will be the solution. Before doing it, I looked for a solution with `update`. Thats why I ask this question. – user3733831 Jul 21 '15 at 16:54
Updated with "delete and insert" – Alex Tartan Jul 21 '15 at 17:05
@AlexTartan look again at the OP's logic. ie. http://stackoverflow.com/users/3733831/user3733831 && http://stackoverflow.com/questions/31544603/update-query-through-foreach-loop-in-mysql-php?noredirect=1#comment51049837_31545319 They are updating in a loop with the same `$restaurant_id` for every query, so the rows will always end up with the last array value. – Sean Jul 21 '15 at 17:48
@Sean You're right and I was missing the point. I think that's why Alex chose a "delete and re-insert" method. – showdev Jul 21 '15 at 17:58

UPDATE query through foreach loop in mysql/php

1 Answers1