How to dynamically build an array for a PDO prepared statement

Question

I am using PDO prepared queries to search a student's table

The user can select what field/s they wish to search by - firstname, lastname, date of birth, gender, or any combination of these.

I am trying to build an array on the fly to be executed by the prepared statement.

e.g. if the user selects lastname and dob, how do I generate the following array?

array(':lastname' => $lastname, ':dob' => $dob);

If I add the values as per usual:

$my_array[] = "[:lastname] => $lastname";   
$my_array[] = "[:dob] => $dob";

And execute the query:

$stmt = $db_conn->prepare($query);
$stmt->execute($my_array);

I get an error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\htdocs\pdo\students\search.php on line 67

Any suggestions gratefully received

Answer 1

Use string indexes.

$dbh = new PDO("sqlite::memory:");
$stmt = $dbh->prepare("SELECT :foo, :bar");
$params = array();

// Add parameters
$params[":foo"] = 1;
$params[":bar"] = 2;

// Overwrite parameters
$params[":foo"] = 42;

// Execute
$stmt->execute($params);

Answer 2

example:

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));

ex:$my_array[':calories']=>$calories

as per your format it will create the array as below.

Array
(
    [0] => :lastname => 
    [1] => :dob => 
)

so update the format as below.

$my_array=array(":lastname" => $lastname,":dob" => $dob);