Binding Multiple Values in Pdo

Binding multiple values in pdo

You can always bind values within the arguments of execute() as long as you're fine with the values being treated as PDO::PARAM_STR (string).

$result_set = $pdo->prepare("INSERT INTO `users` (`username`, `password`, `first_name`, `last_name`) VALUES (:username, :password, :first_name, :last_name)");
$result_set->execute(array(
    ':username' => '~user',
    ':password' => '~pass',
    ':first_name' => '~John',
    ':last_name' => '~Doe'
));

You can use the array passed just like any array:

$user = "Nile";
$pdo->execute(array(":user" => $user));

PDO bind multiple values in one statement

Binding them all as part of the execute...

   $stmnt->execute([':userID'=> $userID,
         ':toUser'=> $toUser,
         ':subject' => $subject,
         ':msg' => $msg,
         ':sentOn' => $sentOn]);

PDO and binding multiple value sets during insert - recently

Just create your query text wtih ? placeholders as:

INSERT INTO table (firstName, lastName) VALUES (?, ?),(?, ?),(?, ?)

And execute it. Sample code can be:

$data = ['Joe', 'Smith','Fred','Sampson','Lisa','Pearce'];
$placeholders = ['(?, ?)', '(?, ?)', '(?, ?)'];  // but you should define this data according to your data
$query = 'INSERT INTO table (firstName, lastName) VALUES ' . implode(',', $placeholders);
$stmt = $dbh->prepare($query);
$stmt->execute($data);

PDO multiple parameter binding for prepared statement

Do it at execute time?

$stmt->execute(array(':name' => $name, etc....))

Using the formal bindParam() really only makes sense if you're going to be executing the statement in a loop and will be changing the values in $name and the other variables in the loop. If it's a fire/forget single-execution query, might as well just pass the values in the execute() call and skip the formal binding - it's a lot of extra work for basically no benefit.

PDO: Using same bind variable multiple times

Problem was the quotes around the :status. Removed quotes and all is good.

PDO / mySQL How to bind multiple values to store multiple rows

Check if $i is less than the number of items rather than less than a fixed value.

        for ($i=0;$i<count($item[0]);$i++) {

        if (!empty($item[0][$i])) {

alternatively you could use foreach:

        foreach ($item[0] as $value) {

        if (!empty($value)) {

php PDO query with several parameters to bind

I do it by passing an array, and using question marks as my place holders

I also recommend working up a set of generic functions or a db handler class that you can simply pass a query and array (or maybe a 2nd array with db connection info) and get back an array with a true or false at element 0 and either an error message at element 1 or data from element 1 on (in the case ofa select).

Here's a snippet of mine, modified to take out all the other handling, but it shows how I paramaterize and prepare the query.

    <?php
    $query="insert into tablename(first_name,last_name) values(?,?)";
    $array=array("John","Doe");

    $dbconn = new PDO('mysql:host=' . $hostname . ';port=' . $dbPort . ';dbname=' . $dbName . ';charset=utf8', $username, $password, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
    $dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $result = $dbconn->prepare($query);
    $result->execute($arr);
    if (!$result) {
        $retVal[] = false;
        $retVal[] = "some sort of error on execute";
        $retVal[] = $dbconn->errorInfo();
        return;
    }
    $retVal[] = true;
    $retVal[] = $dbconn->lastInsertId();

    return;

    ?>

PDO Binding Multiple Insert Statements to Mapped Array Values

I would do it this way, it's much simpler:

$cols = implode(',', array_keys($order_item[0]));

function colon_prefix($param) { return ":$param"; }

$params = implode(',', array_map("colon_prefix", array_keys($order_item[0])));

$sql = "INSERT INTO orders_items ($cols) VALUES ($params)";

$stmt = $db->prepare($sql);

foreach ($order_item as $item) {
    $stmt->execute($item);
}

You can re-execute a prepared statement multiple times, passing a different array to each execution. This is just as good as the multi-query code, and much easier to code and easier to read.

I never use multi-query (multiple statements in one SQL query string). It's complicated to get right, and there's no benefit to doing it.

When you bind values for params, at one time you had to put a colon prefix on the key, to match the parameter placeholder. But they fixed that so long ago that even the PHP version they fixed it in is now deprecated. You can use plain strings as the bind keys.

You don't need to use bindValue() at all, just pass an array to execute().

Also, the code above assumes your column names don't need to be delimited. That is, they are not SQL reserved words, and they don't contain special characters like whitespace, punctuation, or international characters.

---

Related Topics