foreach with mysqli_multi_query issue

Question

I have the below code which works fine and updates each record contained in the array:

$check_list = isset($_POST['check_list']) ? $_POST['check_list'] : array();  
foreach($check_list as $check_list) {
$query = "UPDATE `stock` SET `signature_id` = 0, 
                              user_id = 0, 
                             `status_id` = 1 
            WHERE `id` = '$check_list'";
$result = mysqli_query($conn, $query);

I now need it to execute multiple queries for each result in the array so I have changed the code to the following using mysqli_multi_query:

$check_list = isset($_POST['check_list']) ? $_POST['check_list'] : array();  
foreach($check_list as $check_list) {
    $query = "UPDATE `stock` SET `signature_id` = 0, 
                                  user_id = 0, 
                                 `status_id` = 1 
                WHERE `id` = '$check_list';
               INSERT INTO `returned`
                        (`id`, `stock_id`, `signature_id`, 
                        `user_id`, `timestamp`) 
                VALUES ('','$check_list','$id',
                        '$user_id',now())";
    $result = mysqli_multi_query($conn, $query);

But it now only executes one UPDATE and one INSERT for the first record in the array, and ignores the others

Answer 1

@RiggsFolly is giving the best advice about prepared parameterised statements and transactions due to re-usability and security, but if you want/need to stay with mysqli_multi_query, (because you don't want to transition to a new querying process mid-project or because it is otherwise unappealing to you) here is how mysqli_multi_query can serve you:

Query Combination:

If the SET values stay the same and only the id's are different, all UPDATE queries can be merged into a single query. If the values are static you can use implode(), if not you can chose between using a (verbose/ugly) CASE statement in the SET clause of a single query, or create multiple UPDATE queries as in your original post.

$queries="UPDATE `stock` SET `signature_id`=0,`user_id`=0,`status_id`=1 WHERE `id` IN (".implode(',',$check_list).");";

Likewise with the INSERT queries, they can all be merged into one statement with implode() or a foreach loop that only extends the VALUE portion.

$queries.="INSERT INTO `returned` (`stock_id`,`signature_id`,`user_id`,`timestamp`) VALUES ('".implode("','$id','$user_id',now()),('",$check_list)."','$id','$user_id',now());";

or

$queries.="INSERT INTO `returned` (`stock_id`,`signature_id`,`user_id`,`timestamp`) VALUES ";
foreach($check_list as $k=>$check_list){
    // manipulate $id and $user_id as needed
    $queries.=($k==0?"":",")."('$check_list','$id','$user_id',now())";
}

Failure Awareness:

If you don't need any kind of indication of success then a one-liner will do (keep this outside of any loops of course):

mysqli_multi_query($conn,$queries)

otherwise, you'll need a slightly larger block of code:

if(mysqli_multi_query($conn,$queries)){
    do{
        echo "<br>Rows = ",mysqli_affected_rows($conn);
    } while(mysqli_more_results($conn) && mysqli_next_result($conn));
}
if($mysqli_error=mysqli_error($conn)){
    echo "<br>Syntax Error: $mysqli_error";
}   

I have tested my solution using implode() for both queries and was successful using:

$check_list=array(1,3,5,6,10,11);

and a database setup of:

CREATE TABLE `stock` (
    id int(10) NOT NULL AUTO_INCREMENT,
    signature_id int(10) NOT NULL,
    user_id int(10) NOT NULL,
    status_id int(10) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE `returned` (
    id int(10) NOT NULL AUTO_INCREMENT,
    stock_id int(10) NOT NULL,
    signature_id int(10) NOT NULL,
    user_id int(10) NOT NULL,
    `timestamp` datetime NOT NULL,
    PRIMARY KEY (id)
);

/* Declaring your `id` columns with AUTO_INCREMENT means you can omit them from your INSERT query. */
/* Depending on your mysql version, creating a default datetime for `timestamp` may be possible which then would permit omitting `timestamp` from your INSERT query too. */

INSERT INTO `stock` (`signature_id`,`user_id`,`status_id`) VALUES
    (1,1,1),
    (2,2,2),
    (3,3,3),
    (4,4,4),
    (5,5,5),
    (6,6,6),
    (7,7,7),
    (8,8,8),
    (9,9,9),
    (10,10,10),
    (11,11,11),
    (12,12,12);

The built query looks like this:

UPDATE `stock` SET `signature_id`=0,`user_id`=0,`status_id`=1 WHERE `id` IN (1,3,5,6,10,11);INSERT INTO `returned` (`stock_id`,`signature_id`,`user_id`,`timestamp`) VALUES ('1','','',now()),('3','','',now()),('5','','',now()),('6','','',now()),('10','','',now()),('11','','',now());