PDO multiple parameter binding for prepared statement

Question

I have a prepared statement in PDO with multiple parameters, is there a way to bind the parameters in group, or chain the calls so as to avoid tediously calling bindParam for every item ?

What it looks like right now (I have even more parametrized queries elsewhere):

$stmt = $pdo->prepare("INSERT INTO users (name, pass, mail, created, timezone_name, hash_pass, salt) VALUES (:name, :pass, :mail, :created, :timezone, :hashed, :salt") ;

$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':pass', $pass, PDO::PARAM_STR);
$stmt->bindParam(':mail', $mail, PDO::PARAM_STR);
$stmt->bindParam(':created', $date, PDO::PARAM_INT);
$stmt->bindParam(':timezone', $timezone, PDO::PARAM_STR);
$stmt->bindParam(':hashed', $hash, PDO::PARAM_STR);
$stmt->bindParam(':salt', $salt, PDO::PARAM_STR);

$stmt->execute();

@blackbird57 hmm. Could you build any multidimensional array wich will contain parameter, key and value per each item which needs to be binded — Sergey Belyakov, Feb 10 '16 at 15:26

score 4 · Accepted Answer · answered Feb 10 '16 at 15:23

4

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.

answered Feb 10 '16 at 15:23

Marc B

356,200
43
426
500

Will this implicitly bind them though ? What I'm trying to do is have PDO properly escape them to protect against injections – blackbird Feb 10 '16 at 15:31
there's no binding at all, except for the duration of the `execute()` call. The end-result is the same as if you had bound those values. you still get the injection defense and whatnot, just no permanent link between a placeholder and a php var. – Marc B Feb 10 '16 at 17:14

Daan · Answer 2 · 2016-02-10T15:28:22.557

Yes there is an alternative:

$stmt = $pdo->prepare("INSERT INTO users (name, pass, mail, created, timezone_name, hash_pass, salt) VALUES (?, ?, ?, ?, ?, ?, ?)") ;
$values = [$name, $pass, $mail, $date, $timezone, $hash, $salt];
$stmt->execute($values);

You have positional parameters the one I'm showing you and you have named parameters, Marc B's example.

Choose which one suits you.

Side note: you can never mix positional and named parameters.

Tom · Answer 3 · 2016-02-10T15:25:26.273

0

You can do it like this:

$stmt = $pdo->prepare("INSERT INTO users (name, pass, mail, created, timezone_name, hash_pass, salt) VALUES (:name, :pass, :mail, :created, :timezone, :hashed, :salt");

$stmt->execute([
    ':name' => $name,
    ':pass' => $pass,
    ':mail' => $mail,
    ':created' => $created,
    ':timezone' => $timezone,
    ':hashed' => $hashed,
    ':salt' => $salt
]);

edited Feb 10 '16 at 15:25

answered Feb 10 '16 at 15:23

Tom

606
7
28

You should read http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks and your elements needs to be separated with commas - not my downvote though. – Qirel Feb 10 '16 at 15:24
How does this bind the parameters ? – blackbird Feb 10 '16 at 15:24
@blackbird57 I fixed the quotes and commas now. – Tom Feb 10 '16 at 15:26
@Qirel Thanks, It is fixed now. – Tom Feb 10 '16 at 15:27

PDO multiple parameter binding for prepared statement

3 Answers3