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
– Sean, Commented Jul 21, 2015 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
– Sean, Commented Jul 21, 2015 at 17:42

Alex Tartan · Accepted Answer · 2015-07-21 17:07:26Z

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, 2015 at 17:07

answered Jul 21, 2015 at 16:30

Alex Tartan

6,84410 gold badges37 silver badges47 bronze badges

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

11 Comments

showdev Over a year ago

You only need to prepare the query once. Then bind and/or execute with different parameters upon each iteration. See this post.

Sean Over a year ago

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.

user3733831 Over a year ago

I tried it like this. But it doesn't work for me.. problem is still same

Alex Tartan Over a year ago

Where do you have $restaurant_id defined? Also it's not needed in the SET ...

user3733831 Over a year ago

it comed session. in this case it is 4

|

Collectives™ on Stack Overflow

UPDATE query through foreach loop in mysql/php

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related