Pdo Bind Unknown Number of Parameters

PDO bind unknown number of parameters?

You can build the "IN (...)" string dynamically:

$in_string = '(';
foreach ( $array_of_parameters as $parameter ) {
    $in_string .= ':' . chr($i + 97) . ','; // Get the ASCII character
}
$in_string = substr($in_string, 0, -1) . ')';

$statement = $db->prepare("SELECT blah FROM blah_table WHERE blahID IN ($in_string)");

PHP - secure PDO prepared statement with an unknown number of parameters

For such problems, the database query builder solutions would be better.

Returning to the question: you know the number of parameters, so you can create query template.

<?php
$wq = $pq = [];
$allowed_keys = ['name', 'occupation', '...'];

foreach ($arguments AS $key => $value) {
  if(in_array($key, $allowed_keys))
    $pq[] = "$key = :$key" ;
  else throw new Exception('Go away hacker!');
}
foreach ($filter AS $key => $value)
  if(in_array($key, $allowed_keys))
    $wq[] = "$key = :$key" ;

$q = "UPDATE $table SET ". join(', ', $pq);
if(!empty($wq))
  $q .= 'WHERE '. join(' AND ', $wq);

In results you get query pattern like:

UPDATE workers SET name = :name, occupation = :occupation WHERE employeeId = :employeeId;

Such folding is allowed (if you have "secure" keys to these tables)

$allowed_keys or better $allowed_column_names

Mysqli bind - unknown number of parameters

Prepared statements probably aren't the best way to do this. You can assemble a query just as you are doing, as long as you make sure that you escape any user input. For example:

<?php

// assuming $db is a mysqli object

$query = "SELECT * FROM table WHERE";
if(isset($_POST['r1']))
  $query = $query."id = '".$db->real_escape_string($_POST['r1'])."'";
if(isset($_POST['r2']))
  $query = $query." AND par2 = '".$db->real_escape_string($_POST['r2'])."'";

$result = $db->query($query);
?>

Don't forget to add any required error checking.

PHP pdo: Invalid Parameter Number when using a parameter twice?

Alternatively, you could change your settings to PDO::ATTR_EMULATE_PREPARES => true. This will allow you to bind the same named parameter multiple times by preparing the statements in PDO itself, rather than on the MySQL server.

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

Is it possible to bind a PDO parameter to the member variable of an object?

PHP stores the reference of the variable to use it. When you call $qry->bindParam(1, $obj->qux, PDO::PARAM_INT), the reference stored is the reference of the member of the instanciated class.

When you change the member $obj->qux, the reference is still the same than the one stored in your $obj. However, if you reinstanciate $obj to a new class, then every references are changed, but your old object is still in memory ! So when you assign a new value to the new $obj->qux, it is not the same variable used, so running $qry->execute will use the old value.

I hope I've been clear enough.

PDO prepared statement bind parameters once for different queries

If I have multiple queries where the parameters are exactly the same, do I need to bind the same parameters again using

Yes, of course.

Parameters are bound to each query, not to PDO or a database globally.

On a side note, with PDO you don't have to bind variables explicitly, so there is a solution to your "problem": just don't bind at all but send your data directly into execute() as it shown in the Dharman's excellent answer

---

Related Topics