See: w3cub, php delusions, websitebeaver

PDO supports multiple database drivers. To see list of drivers the server:

foreach( PDO::getAvailableDrivers() as $db) echo $db . "\n";


PDO requires the database as part of the host string, and throws an error if it's missing.

$pdo=new PDO( 'mysql:host=localhost;dbname=database', 'acct', 'pw' ); 

remote host — (port number specified)

$pdo=new PDO( 'mysql:host=somehost;port=12345;dbname=database', 'acct', 'pw' ); 

It's good to specify charset, too. MySQL's utf8 charset had limited unicode support, so utf8mb4 is recommended.,

$host = '';
$db = 'dbname';
$user = 'someuser';
$pw = 'somepw';
$port = 'portnumber';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
$options = [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,// Throw exceptions automatically.
  PDO::ATTR_EMULATE_PREPARES => false // Emulation OFF is usually best.

try {
  $pdo = new PDO($dsn, $user, $pw, $options);
} catch (PDOException $e) {
  throw new PDOException($e->getMessage(), (int)$e->getCode());

Two main fetch modes: PDO::FETCH_ASSOC and PDO::FETCH_OBJ. Set default for convenience, and override as needed.

use a different database

$pdo->exec() can execute SQL in a single function call.
If credentials are good for the other database, switch with USE.

$pdo->exec( 'USE db' );

more about exec()

exec() returns no data, but does return an integer count of modified/deleted rows.
It's preferred for queries that don't need to return a PDOStatment object.

$count = $pdo->exec( "delete from tbl" );
echo "Deleted $count rows.";

$count = $pdo->exec( "alter table tbl add mycol varchar(10)" );
echo "Altered $count columns.";

Regular queries can select from another db as usual.

$pdo->query( "select * from db.tbl" );

simple query

$pdo->query() returns a PDOStatement object, roughly similar to a mysqli resource.
PDOStatement can be used directly after the query, by chaining one of its methods.

Here, its fetchAll() method populates a $rows array.

$rows  = $pdo->query( 'select uwnetid, rank from pdotest' )->fetchAll();
foreach($rows as $row) {
  echo $row['uwnetid'] . ' : ' . $row['rank'];

Or PDOStatement can be preserved for later use, so its methods can be called multiple times.

Here, fetch() is called for each returned row.

$stmt = $pdo->query( 'select name, email from tbl' );
while ( $row = $stmt->fetch() ) {
  echo $row['name'] . ' : ' . $row['email'];

PDOStatement implements a transversable interface. It can be iterated over with foreach(), too.

$stmt = $pdo->query( 'select name, email from tbl' );
foreach($stmt as $row) {
  echo $row['name'] . ' : ' . $row['email'];

Queries that return no data can be run directly.


$sql = "insert into tbl values (NULL,'Doe','John'),(NULL,'Doe','Jane')";
$pdo->query( $sql );
$lastID = $pdo->lastInsertId();
  1. When multiple rows are inserted, as above, lastInsertID() returns the ID of the first inserted row.
  2. lastInsertID() can't be chained to query().
    $lastid = $pdo->query( $sql )->lastInsertId();// undefined method error
    It's a method of the PDO connection, so won't be found in the PDOStatement.


Get a count of affected rows.

$count = $pdo->query( $sql )->rowCount();// use PDOStatement directly

$stmt = $pdo->query( $sql );// preserve statement for later use
$count = $stmt->rowCount();


For simple queries that return a single value, fetchColumn() can be chained to query():

$sql = 'select count(*) from tbl';// get count of rows
$count = $pdo->query( $sql )->fetchColumn();
$sql = 'select max(apptdate) from tbl';// get most recent date
$maxappt = $pdo->query( $sql )->fetchColumn();
$sql = 'select fld from tbl limit 1';// get contents of 1 column
$myField = $pdo->query( $sql )->fetchColumn();

When a query includes variables, use prepared statements, instead.
Then fetchColumn() can't be chained. You need to call additional methods of the PDOStatement, so it must be preserved first.

$sql = 'select count(*) from tbl where user = ? ';
$stmt = $pdo->prepare( $sql );// preserve statement
$stmt->execute( [$username] );// call execute()
$count = $stmt->fetchColumn();// call fetchColumn()

show column information

PDO lacks an equivalent for MySQLi's fetch_field_direct(n). Use MySQL's show columns instead, and parse the Type property.

$cols = $pdo->query( 'show columns from tbl' )->fetchAll();// info about all columns
foreach($cols as $col) echo $col['Type'];// display data type

// include collation, privileges, and comment
$cols = $pdo->query( 'show full columns from tbl' )->fetchAll();

Filter column information with like or where.

$col = $pdo->query( "show columns from tbl like 'cname' " )->fetch();// single column
echo $col['Type'];// varchar(25) etc.

Other conditions:
  where field='cname'// same as above
  where type like 'varchar%'// all columns matching 'varchar'

prepared statements

A query with placeholders is first "prepared" then "executed". So query() is replaced by prepare() and execute().

$pdo->prepare() returns the same PDOStatement object, but without any data.
$stmt->execute() issues the query after sanitizing the variables bound to the placeholders.

PDO allows either positional ( ? ) or named ( :name ) placeholders, which are never quoted.
The two styles can't be mixed in a single query.

Positional placeholders need values in matching order.

$sql = 'select email from tbl where email =  ?  and `date` >  ? '; // positional
$stmt = $pdo->prepare( $sql );
$stmt->execute( [$email, $date] ); // array of values

Named placeholders don't care about order. Key names insure the proper binding.

$sql = 'select email from tbl where email = :email' and `date` > :date; // named
$stmt = $pdo->prepare( $sql );
$stmt->execute( ['date'=>$date, 'email'=>$email] );  // associative array

(Key names don't need a leading colon.)

For queries that return no value, methods can be chained.
Here execute() is called directly after prepare()

$sql = 'delete from tbl where id = ? limit ? ';
$pdo->prepare( $sql )->execute( [$id,  1 ] );

$sql = 'update tbl set fld = ? where id = ? ';
$pdo->prepare( $sql )->execute( [$name, $id] );

But, to get the number of affected rows, methods can't be chained. As above, the statement must be preserved so that we can call 2 of its methods: execute() and rowCount().

$sql = 'update tbl set fld = ? where id = ? ';
$stmt = $pdo->prepare( $sql );
$stmt->execute( [$id, 1] );
$updated = $stmt->rowCount();

If multple placeholders become unwieldy, pass execute() an array variable.

$sql = 'select email from tbl where email =  :email  and `date` >= :date limit :limit';
$stmt = $pdo->prepare( $sql );
$params = [ 
  'email' => $email,
  'date' => $date,
  'limit' => 5
$stmt->execute( $params ); 

one prepare, multiple executions

Multiple updates can be executed after a single prepare():

$users = [
  'weller' => 'Tues 10:00 - 2:00',
  'jscon' => 'Wed 12:00 - 4:00',
  'medinad' => "Fri 11:00 - 3:00"
$stmt = $pdo->prepare( 'update users set office_hours = ? where uwnetid = ? ');
foreach( $users as $uwnetid => $hours ) {
  $stmt->execute( [ $uwnetid, $hours ] );

The above is seldom needed and isn't dramatically faster, given how speedy query parsing has become.

I'd prefer to issue one update with a series of values.
Doubt that's possible with prepared statements.

Placeholders must represent complete strings or numbers.
So conditions such as like and in() need their values treated specially.


PDO can't combine the placeholder with other parts of a like string. This won't work:

$stmt = $pdo->prepare( "select email from tbl where name like '%?%' " );

The placeholder must provide the entire string.

$like = "%$search%";
$stmt = $pdo->prepare( "select email from tbl where name like  ? " );
$stmt->execute( [$like] );


Any string bound to a placeholder becomes a single string literal, so PDO needs a separate placeholder for each in() term.

$in_arr = [1, 2, 3];// 3 elements need 3 placeholders
$in_str = str_repeat('?,', count($arr) - 1) . '?';// create ?,?,? 
$sql = "select * from tbl where fld in( $in_str )";
$stmt = $pdo->prepare($sql);

If other values must be bound, use array_merge() to combine them with in() array.

$sql = "select * from tbl where name = ? and noshow in( $in_str )";
$stmt = $pdo->prepare( $sql );
$allVals = array_merge( ['name'], $in_arr );// Create a single array.
$stmt->execute( $allVals );

when execute() isn't enough

Except for NULL, execute() binds all values as strings. If emulation mode is OFF, MySQL can usually figure it out.

But if the data type must be explicitly set, use bindValue() or bindParam().

bindValue() — binds a value (preferred)

Use if the bound variable won't change or if you want to pass a literal value.

$stmt = $pdo->prepare("select email, lastname from tbl where email =  ?  limit  ? ");
$stmt->bindValue( 1 , $email, PDO::PARAM_STR);
$stmt->bindValue( 2 , 4, PDO::PARAM_INT);// literal value

(Above, positional placeholders need numbered parameters.)

$stmt = $pdo->prepare("select email, lastname from tbl where email = :email limit :limit");
$stmt->bindValue('email', $email, PDO::PARAM_STR);
$stmt->bindValue('limit', 4, PDO::PARAM_INT);// literal value

Data type is optional.
bindParam() could not pass the literal 4, above.

bindParam() — binds a reference

When execute() is inside a loop, bindParam() allows one-time binding before the loop begins. The reference it passes picks up the current value of the variable.

$addresses = ['addr1', 'addr2', 'addr3'];
$stmt = $pdo->prepare( 'select name from tbl where email = ?' );
$stmt->bindParam( 1, $email );
foreach($addresses as $email) {// $email changes with each interation.

bindParam() converts a number to a string after running execute(), which can be hard to debug.


When you expect 1 row, you can fetch it similarly to mysqli's fetch_assoc().

$row = $stmt->frech(PDO::FETCH_ASSOC);

Other common PDO fetch modes.

PDO's default is PDO::FETCH_BOTH, but this can be changed by PDO::ATTR_DEFAULT_FETCH_MODE when the connection is instantiated, as shown above.


Create 1-dimensional array.

$col_arr = $pdo->query( 'select name from tbl' )->fetchAll(PDO::FETCH_COLUMN); 

(Common fetch modes create 2-dimensional arrays.)


Create 1-dimensional array with key-value pairs.

$arr = $pdo->query( 'select id, name from tbl' )->fetchAll(PDO::FETCH_KEY_PAIR); 
foreach($arr as $k => $v) echo "$k : $v";

Keys are built from first column, which must be unique.


Create 2-dimensional array with key : value pairs.

$arr = $pdo->query( 'select id, name, office from tbl' )->fetchAll(PDO::FETCH_UNIQUE); 

First column becomes key, and must be unique.


Create 3-dimensional array. First column provides keys for outer array.

$arr = $pdo->query( 'select rank, name, email from tbl' )->fetchAll(PDO::FETCH_GROUP  ); 
foreach($arr as $rank => $numarr) {// numeric array grouped by `rank` column
  echo "<h2>$rank</h2>";// show value stored in `rank` 
  foreach($numarr as $a) {// walk through elements 0..n
    echo "<p>";
    foreach ($a as $k => $v) echo "$k : $v<br>\n";// display key-value pair
    echo "</p>";

Get individual values: $rank['prof'][0]['name'], or $rank['assocprof'][2]['email'].

2nd loop above could be for($i=0; $i<count($numarr); $i++) , instead.
Then 3rd changes to foreach($numarr[i] as $k => $v).


Instantiate a new class.

class User {};
$users = $pdo->query( 'select * from users' )->fetchAll(PDO::FETCH_CLASS, 'User' );
echo $users[0]->name . ', ' . $users[0]->email;

Creates a new $users array of objects.

If the constructor needs arguments, pass them in an array after the class name.

class User {
  public $dept;
  function public __construct($dept) {
    $this->dept = $dept;
$users = $pdo->query( 'select * from users' )->fetchAll(PDO::FETCH_CLASS, 'User', ['ENGL'] );

The constructor is called after mapping columns to properties.
To call constructor first, combine with PDO::FETCH_PROPS_LATE.

$users = $pdo->query( 'select * from users' )->fetchAll(


No Class is specified, so returns an instance of stdClass.

$stmt = $pdo->prepare( 'select uwnetid, lname, fname from tbl' );
$myNewObj = $stmt->fetch(PDO::FETCH_OBJ);


Instantiate a single object. Constructor can be passed an array of values.

class User {
  private $hours;
  private $office;

  public function __construct($hours, $office) {
    $this->hours = $hours;
    $this->office = $office;

  public function get($k) {
    return $this->$k;

$user = $pdo->query('select * from users limit 1')->fetchObject('User', ['MW 12:00','PDL A-507']);

FETCH_INTO (for setFetchMode() only)

Update an existing object. Replaces values in current object.

class User{};
$user = new User();
$stmt = $pdo->query( 'SELECT uwnetid, lname, rank from pdotest where uwnetid="kgb" ' );
$stmt->setFetchMode(PDO::FETCH_INTO, $user);
$stmt->fetch();// $user->lname = Gillis-Bridges"

$stmt = $pdo->query( 'SELECT uwnetid, lname, rank from pdotest where uwnetid="cicero" ' );
$stmt->setFetchMode(PDO::FETCH_INTO, $user);
$stmt->fetch();// $user->lname = Webster"

PHP serialize()

Save/restore object in/from table. Assume `stashtbl`.`tmpObj` is a text field.

$strObj = serialize( $myObj );
$stmt = $pdo->prepare( 'insert into stashtbl values( ? )' );
$stmt->execute( [$strObj] );// store object as string
$strObj = $pdo->query( 'select tmpObj from stashtbl' )->fetchColumn();
$myObj = unserialize( $strObj );// Restore properties and methods.