PDO - Insert Statements

<?php
$stmt = $db->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();

<?php
//THIS WILL NOT WORK!
$time = 'NOW()';
$name = 'BOB';
$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(?, ?)");
$stmt->execute(array($time, $name));

This does not work, you need to put the function in the query as normal:

<?php
$name = 'BOB';
$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(NOW(), ?)");
$stmt->execute(array($name));

You can bind arguments into SQL functions however:

<?php
$name = 'BOB';
$password = 'badpass';
$stmt = $db->prepare("INSERT INTO table(`hexvalue`, `password`) VALUES(HEX(?), PASSWORD(?))");
$stmt->execute(array($name, $password));

Also note that this does NOT work for LIKE statements:

<?php
//THIS DOES NOT WORK
$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE %?%");
$stmt->bindParam(1, $search, PDO::PARAM_STR);
$stmt->execute();

So do this instead:

<?php
$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();

Note we used bindValue and not bindParam. Trying to bind a parameter by reference will generate a Fatal Error and this cannot be caught by PDOException either.

Prepared statements excel in being called multiple times in a row with different values. Because the sql statement gets compiled first, it can be called multiple times in a row with different arguments, and you'll get a big speed increase vs calling mysql_query over and over again!

Typically this is done by binding parameters with bindParam. bindParam is much like bindValue except instead of binding the value of a variable, it binds the variable itself, so that if the variable changes, it will be read at the time of execute.

<?php
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
  $stmt->execute();
}