Table of contents


What can it do?

  • Prepare/execute, bound parameters
  • Transactions
  • LOBS
  • SQLSTATE standard error codes, flexible error handling
  • Portability attributes to smooth over database specific nuances

Connecting

    try {
    $dbh = new PDO($dsn, $user,
                   $password, $options);
    } catch (PDOException $e) {
    die("Failed to connect:" .
        $e->getMessage();
    }

DSNs

  • mysql:host=name;dbname=dbname
  • pgsql:host=name dbname=dbname
  • odbc:odbc_dsn
  • oci:dbname=dbname;charset=charset
  • sqlite:/path/to/file

Connection Management

    try {
    $dbh = new PDO($dsn, $user, $pw);
    // use the database here
    // ...
    // done; release
    $dbh = null;
    } catch (PDOException $e) {
    die($e->getMessage();
    }

Error Handling

  • Maps error codes to ANSI SQLSTATE (5 character text string)
    • also provides the native db error information
  • Three error handling strategies
    • silent (default)
    • warning
    • exception

PDO::ERRMODE_SILENT

    // The default mode
    if (!dbh->query($sql)) {
      echo $dbh->errorCode(), "<br>";
      $info = $dbh->errorInfo();
      // $info[0] == $dbh->errorCode()
      //             SQLSTATE error code
      // $info[1] is driver specific err code
      // $info[2] is driver specific
      //             error message
    }

PDO::ERRMODE_WARNING

    $dbh->setAttribute(PDO::ATTR_ERRMODE,
                       PDO::ERRMODE_WARNING);

  • Behaves the same as silent mode
  • Raises an E_WARNING as errors are detected
  • Can suppress with @ operator as usual

PDO::ERRMODE_EXCEPTION

    $dbh->setAttribute(PDO::ATTR_ERRMODE,
                       PDO::ERRMODE_EXCEPTION);
    try {
      $dbh->exec($sql);
    } catch (PDOException $e) {
      // display warning message
      print $e->getMessage();
      $info = $e->errorInfo;
      // $info[0] == $e->code
      //             SQLSTATE error code
      // $info[1] driver specific error code
      // $info[2] driver specific error string
    }

Get Data

    $dbh = new PDO($dsn);
    $stmt = $dbh->prepare(
                     "SELECT * FROM FOO");
    $stmt->execute();
    while ($row = $stmt->fetch()) {
      print_r($row);
    }
    $stmt = null;

Forward-only cursors

  • a.k.a. “unbuffered” queries in mysql parlance
  • They are the default cursor type
  • rowCount() doesn’t have meaning
  • FAST!
  • Other queries are likely to block
  • You must fetch all remaining data before launching another query
  • $stmt→closeCursor();

Buffered Queries

    $dbh = new PDO($dsn);
    $stmt = $dbh->query("SELECT * FROM FOO");
    $rows = $stmt->fetchAll();
    $count = count($rows);
    foreach ($rows as $row) {
      print_r($row);
    }

Fetch modes

  • $stmt→fetch(PDO::FETCH_BOTH);
    1. Array with numeric and string keys
    2. default option
  • PDO::FETCH_NUM
    1. numeric keys only
  • PDO::FETCH_ASSOC
    1. string keys only
  • PDO::FETCH_OBJ
    1. stdClass object
    2. $obj→name == ‘name’ column
  • PDO::FETCH_CLASS
    1. You choose the class
  • PDO::FETCH_INTO
    1. You provide the object
  • PDO::FETCH_COLUMN
    1. Fetches a column (example later)
  • PDO::FETCH_BOUND
    1. Only fetches into bound variables
  • PDO::FETCH_FUNC
    1. Returns the result filtered through a callback
  • see the manual for more

Iterators

    $dbh = new PDO($dsn);
    $stmt = $dbh->query(
               "SELECT name FROM FOO",
               PDO::FETCH_COLUMN, 0);
    foreach ($stmt as $name) {
      echo "Name: $name\n";
    }
    $stmt = null;

Changing data

    $deleted = $dbh->exec( "DELETE FROM FOO WHERE 1");
    $changes = $dbh->exec(
      "UPDATE FOO SET active=1 "
     ."WHERE NAME LIKE '%joe%'");

Autonumber/sequences

    $dbh->exec( "insert into foo values (...)");
    echo $dbh->lastInsertId();
    $dbh->exec( "insert into foo values (...)");
    echo $dbh->lastInsertId("seqname");

Its up to you to call the right one for your db!

Prepared Statements

    // No need to manually quote data here
    $stmt = $dbh->prepare(
       "INSERT INTO CREDITS (extension, name)"
      ."VALUES (:extension, :name)");
    $stmt->execute(array(
       'extension' => 'xdebug',
       'name'      => 'Derick Rethans'
    ));

Prepared Statements

    // No need to manually quote data here
    $stmt = $dbh->prepare(
       "INSERT INTO CREDITS (extension, name)"
      ."VALUES (?, ?)");
    $stmt->execute(array(
                      'xdebug',
                      'Derick Rethans'
    ));

$db->quote()

  • If you really must quote things “by-hand”
  • $db→quote() adds quotes and proper escaping as needed
  • But doesn’t do anything in the ODBC driver!
  • Best to use prepared statements

Transactions

    $dbh->beginTransaction();
    try {
      $dbh->query("UPDATE ...");
      $dbh->query("UPDATE ...");
      $dbh->commit();
    } catch (PDOException $e) {
      $dbh->rollBack();
    }

Stored Procedures

    $stmt = $dbh->prepare( "CALL sp_set_string(?)");
    $stmt->execute(array('foo'));
    $stmt = $dbh->prepare( "CALL sp_set_string(?)");
    $stmt->bindValue(1, 'foo');
    $stmt->execute();

OUT parameters

    $stmt = $dbh->prepare( "CALL sp_get_string(?)");
    $stmt->bindParam(1, $ret, PDO::PARAM_STR, 4000);
    if ($stmt->execute()) {
      echo "Got $ret\n";
    }

IN/OUT parameters

    $stmt = $dbh->prepare(
               "call @sp_inout(?)");
    $val = "My input data";
    $stmt->bindParam(1, $val,
                     PDO::PARAM_STR|
                     PDO::PARAM_INPUT_OUTPUT,
                     4000);
    if ($stmt->execute()) {
      echo "Got $val\n";
    }

Multi-rowset queries

    $stmt = $dbh->query("call sp_multi_results()");
    do {
      while ($row = $stmt->fetch()) {
        print_r($row);
      }
    } while ($stmt->nextRowset());

Binding columns

    $stmt = $dbh->prepare(
       "SELECT extension, name from CREDITS");
    if ($stmt->execute()) {
      $stmt->bindColumn('extension', $ext);
      $stmt->bindColumn('name', $name);
      while ($stmt->fetch(PDO::FETCH_BOUND)) {
        echo "Extension: $ext\n";
        echo "Author:    $name\n";
      }
    }

Oracle style NULLs

  • Oracle translates empty strings into NULLs
    • $dbh→setAttribute(PDO::ATTR_ORACLE_NULLS, true)
  • Translates empty strings into NULLs when fetching data
  • But won’t change them on insert

LOBs

  • Large objects are usually >4kb in size
  • Nice to avoid fetching them until you need them
  • Mature RDBMS offer LOB APIs for this
  • PDO exposes LOBs as Streams

Fetching an image

    $stmt = $dbh->prepare(
       "select contenttype, imagedata"
      ." from images where id=?");
    $stmt->execute(array($_GET['id']));
    $stmt->bindColumn(1, $type,
                      PDO::PARAM_STR, 256);
    $stmt->bindColumn(2, $lob,
                      PDO::PARAM_LOB);
    $stmt->fetch(PDO::FETCH_BOUND);
    header("Content-Type: $type");
    fpassthru($lob);

Uploading an image

    $stmt = $db->prepare("insert into images "
       . "(id, contenttype, imagedata)"
       . " values (?,?,?)");
    $id = get_new_id();
    $fp = fopen($_FILES['file']['tmp_name'],'rb');
    $stmt->bindParam(1, $id);
    $stmt->bindParam(2, $_FILES['file']['type']);
    $stmt->bindParam(3, $fp, PDO::PARAM_LOB);
    $stmt->execute();

Scrollable Cursors

  • Allow random access to a rowset
  • Higher resource usage than forward-only cursors
  • Can be used for positioned updates (more useful for CLI/GUI apps)

Positioned updates

  • An open (scrollable) cursor can be used to target a row for another query
  • Name your cursor by setting PDO::ATTR_CURSOR_NAME during prepare()
  • UPDATE foo set bar = ? WHERE CURRENT OF cursor_name