PHP PDO Cheatsheet

PDO_Cheatsheet.php

<?php

/**
 * Reference: 
 * http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059
 */

function connect() {
  /**
   * No matter what error mode you set, 
   * an error connecting will always produce an exception, 
   * and creating a connection should always 
   * be contained in a try/catch block.
   */
  try {
    // MS SQL Server with PDO_DBLIB.
    $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");
    
    // Sybase with PDO_DBLIB.
    $DBH = new PDO("sybase:host=$host;dbname=$dbname, $user, $pass");
   
    // MySQL with PDO_MYSQL.
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
   
    // SQLite database.
    $DBH = new PDO("sqlite:my/database/path/database.db");
  }
  catch(PDOException $e) {
    echo $e->getMessage();
  }
}

function  error_reporting_mode() {
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT ); // default
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}

function prepare_statements() {
  // No placeholders - ripe for SQL injection!
  // We use ->query() here instead of ->prepare() because there are no bound values.
  $STH = $DBH->query("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)");
   
  // Unnamed placeholders.
  $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");
   
  // Named placeholders.
  $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
}

function unnamed_placeholders() {
  
  /// Alternative 1 ///
  
  // Assign variables to each place holder, indexed 1-3.
  $STH->bindParam(1, $name);
  $STH->bindParam(2, $addr);
  $STH->bindParam(3, $city);
   
  // Insert one row.
  $name = "Daniel"
  $addr = "1 Wicked Way";
  $city = "Arlington Heights";
  $STH->execute();
   
  // Insert another row but with different values.
  $name = "Steve"
  $addr = "5 Circle Drive";
  $city = "Schaumburg";
  $STH->execute();
  
  /// Alternative 2 ///
  
  // The data we want to insert.
  $data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');
  
  $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");
  $STH->execute($data);
}

function named_placeholders() {

  // Alternative 1 ///
  
  // The first argument is the placeholder's name.
  // Notice that named placeholders always start with a colon.
  $STH->bindParam(':name', $name);

  /// Alternative 2 ///

  // The data we want to insert.
  $data = [
    'name' => 'Cathy',
    'addr' => '9 Dark and Twisty',
    'city' => 'Cardiff',
  ];
  
  // The shortcut!
  $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
  $STH->execute($data);
  
  /// Alternative 3 ///
  
  // Ability to insert objects directly into DB, assuming properties match named fields.
  
  // Example of a simple object.
  class Person {
    public $name;
    public $addr;
    public $city;
    
    function __construct($n, $a, $c) {
      $this->name = $n;
      $this->addr = $a;
      $this->city = $c;
    }
    // etc...
  }
   
  $cathy = new Person('Cathy', '9 Dark and Twisty', 'Cardiff');
   
  // Here's the fun part:
  $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
  $STH->execute((array)$cathy);
}

function select_data_modes() {
  /**
   * PDO::FETCH_ASSOC: Returns array indexed by column name
   * PDO::FETCH_BOTH (default): Returns array indexed by both column name & number
   * PDO::FETCH_BOUND: Assigns values of columns to variables set with ->bindColumn()
   * PDO::FETCH_CLASS: Assigns values of columns to properties of named class.
   *   Will create properties if matching properties do not exist
   * PDO::FETCH_INTO: Updates existing instance of named class
   * PDO::FETCH_LAZY: Combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating object variable names as they are used
   * PDO::FETCH_NUM: Returns array indexed by column number
   * PDO::FETCH_OBJ: Returns anonymous object w/ property names corresponding to column names
   */
  
  /**
   * These three will cover most situations: FETCH_ASSOC, FETCH_CLASS, FETCH_OBJ.
   */
  
  $STH->setFetchMode(PDO::FETCH_ASSOC);
}

/**
 * This fetch type creates an associative array, indexed by column name.
 */
function fetch_assoc() {
  // Use ->query() method here since there are no variable values in SELECT statement.
  $STH = $DBH->query("SELECT name, addr, city FROM folks");
  
  // Set the fetch mode.
  $STH->setFetchMode(PDO::FETCH_ASSOC);
   
  while ($row = $STH->fetch()) {
    echo $row['name'], "\n";
    echo $row['addr'], "\n";
    echo $row['city'], "\n";
  }
}

/**
 * This fetch type creates an object of stdClass for each row of fetched data. 
 */
function fetch_obj() {
  // Create the statement.
  $STH = $DBH->query("SELECT name, addr, city FROM folks");
  
  // Setting the fetch mode.
  $STH->setFetchMode(PDO::FETCH_OBJ);
  
  // Showing the results.
  while ($row = $STH->fetch()) {
    echo $row->name, "\n";
    echo $row->addr, "\n";
    echo $row->city, "\n";
  }
}

/**
 * This fetch method allows you to fetch data directly into a class of your choosing.
 * The properties of your object are set BEFORE the constructor is called.
 * If properties matching column names don't exist, those properties will be created (as public) for you.
 * So if your data needs any transformation after it comes out of the DB,
 *   it can be done automatically by your object as each object is created (via __consruct() method).
 */
function fetch_class() {
  class SecretPerson {
    public $name;
    public $addr;
    public $city;
    public $other_data;
    
    function __construct($other = '') {
      // will be called after object has valid data in its properties
      $this->address = preg_replace('/[a-z]/', 'x', $this->address);
      $this->other_data = $other;
    }
  }
  
  $STH = $DBH->query("SELECT name, addr, city FROM folks");
  $STH->setFetchMode(PDO::FETCH_CLASS, "secret_person");
  
  while ($obj = $STH->fetch()) {
    echo $obj->addr;
  }
  
  /// Alternaive: Late properties fetch ///
  
  // The __construct() method will be invoked before intantiating properties.
  $STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "secret_person");
  
  /// Alternative: Pass arguments to constructor ///
  
  // You can pass arguments to the constructor when fetching data into objects.
  $STH->setFetchMode(PDO::FETCH_CLASS, "secret_person", ["stuff"]);
  
  /// Alternative: ///
  
  // If you need to pass different data to constructor for each object, set fetch mode inside fetch method.
  $i = 0;
  while($rowObj = $STH->fetch(PDO::FETCH_CLASS, "secret_person", [$i])) {
    // ...
    $i++;
  }
}

function some_other_useful_methods() {
  // Transaction Processing.
  $DBH->beginTransaction();
  $DBH->commit();
  $DBH->rollBack();
  
  // Retrieves ID field for last insert query (should be called inside transaction, if applicable).
  $DBH->lastInsertId();
  
  // Use exec() for operations that cannot return data other than affected rows.
  $DBH->exec("DELETE FROM folks WHERE 1");
  $DBH->exec("SET time_zone = '-8:00'");
  
  // Add quotes around strings to use in queries.
  // This is your fallback if you're not using prepared statements!
  $safe = $DBH->quote($unsafe);
  
  // Returns an integer indicating number of rows affected by an operation.
  $rows_affected = $STH->rowCount();
  
  // In a known version of PDO, rowCount() doesn't work for SELECT statements. If so, use the following instead:
  $sql = "SELECT COUNT(*) FROM folks";
  if ($STH = $DBH->query($sql)) {
    // Check the row count.
    if ($STH->fetchColumn() > 0) {
      // Use a real SELECT here, because there's data now.
    }
    else {
      echo "No rows matched the query.";
    }
  }
}

function close_connection() {
  // Close the connection.
  $DBH = null;
}