Binding multiple values in pdo

Question

Is there's an easy way of binding multiple values in PDO without repitition ? Take a look at the following code :

$result_set = $pdo->prepare("INSERT INTO `users` (`username`, `password`, `first_name`, `last_name`) VALUES (:username, :password, :first_name, :last_name)");

$result_set->bindValue(':username', '~user');
$result_set->bindValue(':password', '~pass');
$result_set->bindValue(':first_name', '~John');
$result_set->bindValue(':last_name', '~Doe');

$result_set->execute();

Here, I binded values in a repepeated way which is 4 times. So is there's an easy way of binding multiple values in PDO ?

jeremy · Accepted Answer · 2013-09-17T00:13:23.633

42

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));

edited Sep 17 '13 at 00:13

answered Sep 10 '12 at 02:34

jeremy

9,965
4
39
59

How is this valid? The `execute` method doesn't take any parameters? – Brett Jan 25 '16 at 15:20
1

@Brett have you checked the specs? it does... to pass parameters to the query you can either use [PDOStatement::bindParam() or pass it as a parameter through the `execute()` method](http://php.net/manual/en/pdostatement.execute.php). – jeremy Jan 25 '16 at 15:22
My bad, I was thinking you were talking about [this](http://php.net/manual/en/mysqli-stmt.execute.php). – Brett Jan 25 '16 at 15:26
Am not sure why you say it will treat it as a string cos I believe if you cast the values it should not treat them as strings but as what they are cast to, no? – mw509 Oct 21 '21 at 23:30
@mw509 the documentation (https://www.php.net/manual/en/pdostatement.execute.php) specifies this regarding `PDO::PARAM_STR` – jeremy Dec 20 '21 at 23:47

score 6 · Answer 2 · answered Sep 10 '12 at 02:33

6

If you want to bind based on type (string, int, etc), then no. If you're fine with binding everything as a string:

$stmt = $db->prepare("...");
$stmt->execute(array(
    'foo' => 'bar',
    'something' => 'else',
    'third' => 'thing',
));

answered Sep 10 '12 at 02:33

Corbin

33,060
6
68
78

1

Will this work with variables if i changed `bar` to `$bar` in which a string of `'bar'` is assigned ? – Yousuf Memon Sep 10 '12 at 02:35

equazcion · Answer 3 · 2014-04-25T17:01:24.867

To truly never type anything twice, you can use an array to supply the data, and use a function on that same array to output the binding portion of the MySQL query. For example:

function bindFields($fields){
    end($fields); $lastField = key($fields);
    $bindString = ' ';
    foreach($fields as $field => $data){ 
        $bindString .= $field . '=:' . $field; 
        $bindString .= ($field === $lastField ? ' ' : ',');
    }
    return $bindString;
}

The data and column names come from a single associative array ($data). Then, use bindFields($data) to generate a string of column = :column pairs to concatenate into the MySQL query:

$data = array(
    'a_column_name' => 'column data string',
    'another_column_name' => 'another column data string'
);

$query = "INSERT INTO tablename SET" . bindFields($data);

$result = $PDO->prepare($query);
$result->execute($data);

bindFields($data) output:

 a_column_name=:a_column_name,another_column_name=:another_column_name

I had originally posted this as a separate question/answer ([here](http://stackoverflow.com/questions/23282160/is-there-a-shortcut-for-binding-named-pdo-params-for-mysql-inserts/23282161)) before finding this question, but have now marked that as a duplicate. — equazcion, Apr 25 '14 at 16:44

Binding multiple values in pdo

3 Answers3

Linked

Related