Concatenate query in MySqli prepared statement PHP

Question

I've a list of item I want to insert in my table with a concetenation of INSERT queries. I'm using MySQL and PHP.

I receive list of exercises names with POST (separated by commas), and want to save them in DB, associating them to an ID (i receive too from POST).

Instead of running several INSERT queries, I would like to prepare a unique query and just use one run, so that the entire query runs or fails.

Here is my code.

$exList= $_REQUEST['exList']; 
$routineID = $_REQUEST['routineID'];
$arrayEx = explode(',', $exList);
$countEx=count($arrayEx);
$placeholdersEx = implode(',', array_fill(0, $countEx, '?'));
$bindStrEx = str_repeat('si', $countEx);
$queryEx = str_repeat('INSERT INTO exercises_x_routines(exerciseID, routineID) VALUES(?,?);' ,$countEx);
$arrayRoutine = array_fill(0,$countEx,$routineID);

$stmt = mysqli_prepare($con, substr($queryEx, 0, -1));
mysqli_stmt_bind_param($stmt, $bindStrEx, ...$arrayEx,...$arrayRoutine));

if (!mysqli_stmt_execute($stmt))
    {
        //print mysqli_error($con);
        $output[]=array("code" => 3003, "error" => mysqli_error($con));
        print(json_encode($output));
        mysqli_close($con);
        exit;
    }

However, I don't know why but query execution is not successful and I don't get any error description from mysqli_error.

With `str_repeat()` it looks like you are attempting to build multiple `INSERT` statements into one string. `mysqli_stmt_execute()` will not allow that - you can only execute one single statement at a time. Check `mysqli_error($con);` just after `mysqli_prepare()` - I believe it should have issued an error in the prepare, before attempting to execute. — Michael Berkowski, Jan 24 '21 at 23:35
@user1922860 https://stackoverflow.com/a/48940643/2943403 , https://stackoverflow.com/q/33970314/2943403 — mickmackusa, Jan 25 '21 at 02:52

Nick · Accepted Answer · 2021-01-25T11:17:17.420

1

You can't prepare multiple queries. Instead, you could prepare the single query once and execute it multiple times:

$exList= $_REQUEST['exList']; 
$routineID = $_REQUEST['routineID'];
$arrayEx = explode(',', $exList);

$queryEx = 'INSERT INTO exercises_x_routines(exerciseID, routineID) VALUES(?,?)';
$stmt = $con->prepare($queryEx);
$stmt->bind_param('si', $ex, $routineID);
foreach ($arrayEx as $ex) {
    if (!$stmt->execute()) {
        $output[]=array("code" => 3003, "error" => $con->error);
        print(json_encode($output));
        $con->close();
        exit;
    }
}

If you want to effectively make this a single insert with no change to the table if any insert fails, you can use a transaction:

$con->beginTransaction();
foreach ($arrayEx as $ex) {
    if (!$stmt->execute()) {
        $output[]=array("code" => 3003, "error" => $con->error);
        print(json_encode($output));
        $con->rollBack();
        $con->close();
        exit;
    }
}
$con->commit();

edited Jan 25 '21 at 11:17

answered Jan 24 '21 at 23:40

Nick

138,499
22
57
95

To be clear, it is possible to batch insert multiple rows using one prepared statement and one execution. – mickmackusa Jan 25 '21 at 02:52
@mickmackusa absolutely, that would be a perfectly valid answer to the question too – Nick Jan 25 '21 at 02:53
I am sure this page is a duplicate, so answering would be inappropriate. – mickmackusa Jan 25 '21 at 02:53
@mickmackusa Feel free to spend the time finding one... – Nick Jan 25 '21 at 02:54
Here's one I'm not too proud of: https://stackoverflow.com/a/48940643/2943403 but I am confident that it is a duplicate of an earlier question. – mickmackusa Jan 25 '21 at 02:54
I would worry that you might run into the prepared statement parameter limit (although that seems to be 65535 so perhaps unlikely). – Nick Jan 25 '21 at 02:58
Your answer as a dupe: https://stackoverflow.com/a/11084885/2943403 – mickmackusa Jan 25 '21 at 02:59
that's a pretty poor dupe too – Nick Jan 25 '21 at 03:00
We must improve old content instead of bloating the site. As you know, this will be a multi-duplicate question. – mickmackusa Jan 25 '21 at 03:02
Note that because this question only has one value that varies per insert, the solution is different to both the dupes you propose (use of `foreach` to directly load the bound parameter) – Nick Jan 25 '21 at 03:10
Ah, the "snowflake defense" -- I do not subscribe to this because then Stack Overflow would not need Dupe closures anymore. – mickmackusa Jan 25 '21 at 03:34
OP says: "_ I would like to prepare a unique query and just use one run, so that the entire query runs or fails._" – mickmackusa Jan 25 '21 at 05:19
Thanks @nick fro your answer. I already did it in my previous version however, if one of the runs fails how can i rollback all the previous INSERT queries? Any way to prepare all the queries separated and then commit all at once? – user1922860 Jan 25 '21 at 11:03
@mickmackusa how would you batch insert multiple rows using one prepared statement and one execution? – user1922860 Jan 25 '21 at 11:08
@user1922860 you could use the approach described in this answer: https://stackoverflow.com/a/48940643/9473764; the other possibility would be to start a transaction and then if any insert failed do a rollback. – Nick Jan 25 '21 at 11:08
1

@user1922860 see my edit, that wraps all the inserts into a transaction that can be rolled back if any fail, or committed if they all succeed. – Nick Jan 25 '21 at 11:17

Concatenate query in MySqli prepared statement PHP

1 Answers1