php binding dynamic number of variables for batch insert query

Question

I have a web service where a user passes up a dynamic number of questions.

On the php side I am using explode with the ? to strip out each question. I then need to do a batch insert.

What I've done so far is as follows:

$checkInQs = explode("?", trim($_POST['checkInQs'], "?"));
    $checkInSql = "INSERT INTO CheckListQs (ID, GeofenceID, type, question) VALUES ";
    $checkInInsertQuery = array();
    $checkInInsertData = array();
    foreach($checkInQs as $q){
         $checkInInsertQuery[] = "('',?, 1, ?)";
         $checkInData[] = $geofenceID;
         $checkInData[] = $q;
    }

Based on another similar example, the following would be how to finish it off with pdo:

if (!empty($checkInInsertQuery)) {
        $checkInSql .= implode(', ', $checkInInsertQuery);
        $stmt = $db->prepare($checkInSql);
        $stmt->execute($checkInData);
    }

I'm not really sure how to bind the parameters in my case. I'm using procedural binding. I would usually bind parameters like so:

mysqli_stmt_bind_param($stmt, "is", $geofenceID, $question);
mysqli_stmt_execute($stmt);

I think the type part is as simple as:

$bindVar = '';

for ($i = 0; $i < count($checkInQs); $i++){
    $bindVar .= "is";
}

But not I'm not sure how to manage passing in the rest of the data?

user2363025 · Accepted Answer · 2016-01-26 14:24:46Z

0

In the end, I chose to make use of transactions, commits and rollbacks to get my desired outcome:

mysqli_query($con, "start transaction;");

$allQueriesOK = true;
$checkInQs = explode("?", trim($_POST['checkInQs'], "?"));
$checkInSql = "INSERT INTO CheckListQuestions (ID, GeofenceID, type, question) VALUES ('',?,0,?)";
mysqli_stmt_prepare($stmt, $checkInSql);

foreach ($checkInQs as $q) {            
    mysqli_stmt_bind_param($stmt, "is", $geofenceID, $q);
        if (!mysqli_stmt_execute($stmt)){
            $allQueriesOK = false;
            $message = mysqli_error($con);
            break;
        }
    }

    mysqli_stmt_close($stmt);

    if ($allQueriesOK){
        mysqli_query($con, "commit;");
    }
    else{
        mysqli_rollback($con);
    }

answered Jan 26, 2016 at 14:24

user2363025

6,53720 gold badges56 silver badges90 bronze badges

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

2 Comments

erik258 Over a year ago

I'd be surprised if that was anywhere near as fast as a bulk insert, but out of curiosity, did it perform well for you?

user2363025 Over a year ago

@DanFarrell It was fine for what I needed. Usually there would only be 3-5 inserts per ws call so it worked out OK for me. I'd still happily change to a bulk insert prepared statement if I got an answer on how to safely bind the parameters to a dynamic query

Collectives™ on Stack Overflow

php binding dynamic number of variables for batch insert query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related