PDO

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

connect

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 = 'some.host.some.where';
$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_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  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()

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

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

$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.

lastInsertID()

$sql = "insert into tbl values (NULL,'Doe','John'),(NULL,'Doe','Jane')";
$pdo->query( $sql );
$lastID = $pdo->lastInsertId();

rowCount()

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

fetchColumn()

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.

like

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

in()

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);
$stmt->execute($in_arr);

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().

$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
$stmt->execute();

$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
$stmt->execute();

$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.
  $stmt->execute();
}

process

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.

FETCH_COLUMN

Create 1-dimensional array.

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

(Common fetch modes create 2-dimensional arrays.)

FETCH_KEY_PAIR

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.

FETCH_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.

FETCH_GROUP

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

FETCH_CLASS

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(
  PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'User', ['ENGL']
);

FETCH_OBJ

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

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

fetchObject()

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.