PDO cheatsheet

http://slashnode.com/pdo-for-elegant-php-database-access/

Connecting

$conn = new PDO('mysql:host=localhost;dbname=someDb', $username, $password);

Simple query

$query = $conn->query('
        SELECT * FROM myTable WHERE name = ' . $conn->quote($name) . '
');
$result = $query->fetch();

while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {

Constants

PDO::FETCH_NAMED if you work multiple tables.

PDO::ATTR_FETCH_TABLE_NAMES. This returns the table names prefixes to the fields in a result

DB::$c->setAttribute(\PDO::ATTR_FETCH_TABLE_NAMES, true);

$books = DB::fetch("select * from books left join author on books.author_id = author.id");

...will return something like:

...
["book.title"]=>"Harry Potter"
["author.name"]=>"J K Rowling"
...

PDO::FETCH_OBJ Is used to fetch into a new instance of an unnamed ("anonymous") object

PDO::FETCH_CLASS Is used to fetch into a new instance of an existing class (the column names should match existing properties, or __set should be used to accept all properties). The constructor of the class will be called after the properties are set.

class Post {
        public $id;
        public $text;
        public $user_id;
}

    $statement = $db->prepare("select * from posts");
$statement->execute();
$statement->setFetchMode(\PDO::FETCH_CLASS, 'Post');
$posts = $statement->fetchAll();

Call PDO::FETCH_PROPS_LATE if you want to call MyRow constructor AFTER filling fetched properties $stmt->setFetchMode(\PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'MyRow');

PDO::FETCH_CLASSTYPE Used with FETCH_CLASS (bitwise OR), the name of the class to create an instance of is in the first column, instead of supplied to the function.

PDO::FETCH_INTO Is used with the same type of class as FETCH_CLASS (must handle all columns as property names), but updates an existing object as opposed to creating a new one.

PDO::FETCH_LAZY I don't know what this does.

 Functions

PDOStatement::fetch The regular get-a-row command. I don't know how to use this with FETCH_CLASS or FETCH_INTO since there does not be any way to pass the class name/instance.

PDOStatement::fetchObject A way to do FETCH_CLASS or FETCH_INTO, including passing constructor args. With no args is a shorthand for FETCH_OBJ.

PDOStatement::setFetchMode A way to set the default fetch mode, so that PDOStatment::fetch can be called without args.

Bind paramaters

Is there a performance difference if an integer is quoted as a string? Answer

Named binding (in order of speed):

  • quickest: $stmt->bindValue(":param", 5, PDO::PARAM_INT);)
  • $stmt->bindValue(":param", 5);)
  • slowest: $stmt->bindValue(":param", 5, PDO::PARAM_STR);)