Make a UPDATE query through foreach loop

[thara]

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.  

[Barand]

You shouldn't be updating the restaurant id in the update.

 

Your query sets all the records for the restaurant to each cuisine id in turn, so you then up with them all equal to the last one in the loop.

 

Easiest way is to delete the restaurant_cuisine records for the resturant then insert records for each cuisine.

[thara]

@Barand, Is there a way to use DELETE and INSERT in single query?

 

Thank you.

[Barand]

None that I am aware of.