PHP PDO

Connecting to MySQL With PDO

When connecting to PDO use exception handling to prevent the display of potentially sensitive information in the event of failure. This example handles the exception by triggering an error and killing the program.

// This example uses defined values for the connection arguments.
define('DBHOST', 'localhost');
define('DBNAME', 'mydb');
define('DBUSER', 'myuser');
define('DBPASS', 'mypass');

// Create a database connection or trigger an error and die.
try {
  $dbh = new PDO('mysql:host=' . DBHOST . ';dbname=' . DBNAME, DBUSER, DBPASS);
} 
catch(PDOException $e) {
  trigger_error($e -> getMessage());
  die();
}

Prepared Statements

$id = 12;
$email = 'email@example.com';
$sql = 'select * FROM t WHERE  > id = :id AND email = :email';
$sth = $dbh->prepare($sql);
$sth->bindParam(':id', $id, PDO::PARAM_INT);
$sth->bindParam(':email', $email, PDO::PARAM_STR);
$sth->execute();
$data = $sth->fetchAll();

Binding an Arbitrary Number of Parameters

This is an example of binding an arbitrary number of parameters using a keyed array. I've found this to be more reliable than the string of question marks method.

It presumes a few things:

1. You have already made a PDO object, $dbh.

2. The data you plan to insert is stored in an array ($data) where for each array item the key is the name of your database column ($col) and its $value is a valid value to be inserted.

// Expected form of $data array
$data = array(
  'col1' => 12,
  'col2' => 'email@example.com',
  'col3' => 1345852187
);
// Array keys are imploded to form a string like: col1, col2, col3
$cols = implode(',', array_keys($data));
// Generate the needed placeholders in the form of :col1, :col2, etc.
// Note, you can't just use $cols in $placholders' stead; It won't work. 
foreach ($data as $col => $value) {
  $placeholders[] = ":$col";
}
$placeholders = implode(',', $placeholders);
$sql = "INSERT INTO systemstatus ($cols) VALUES ($placeholders)";
// Initiate a prepared statement.
$stmt = $dbh->prepare($sql);
// Iterate over $data binding parameters as we go. 
foreach ($data as $col => $value) {
  $stmt->bindParam(":$col", $data[$col]);
}
// Execute the query.
$stmt->execute();