NYPHP Con: “Introduction to PDO”

PDO ships with PHP 5.1. You do not need to install via PECL

These are my notes from a presentation by Ilia Alshanetsky. This post may contain inaccuracies, typos, boo-boos, etc.

PDO stands for “PHP Data Objects Layer”: A data-access abstraction layer.

• Common interface for any number of database systems
• PHP extension
• Designed to simplify features.
• Only available to PHP 5 or 5.1; requires a level of OOPHP that’s only available to PHP 5

Why PDO?

• Consistency of development across databases; code is the same for MySQL, PostgreSQL, Oracle, etc.
• Current APIs don’t take full advantage of new PHP’s features
• Current APIs don’t take full advantage of all of the features available in the database

Installation

• Need to install PDO, made up of the core and the drivers.
• Can install using PECL.
• Can install by recompiling PHP

Using PDO

• Create an instance of the object.
• PDO uses a single connection string as the lone parameter.
  • Connection failure handling: Instantiation failure causes an exception error. Use inside of a try…catch block to retrieve and log the error number.
  • Functions available to report errors

Persistent Connections

• Persistent connections help speed up web apps, particularly with Oracle and PostgreSQL databases
• Create persistent connections using a class constant ATTR_PERSISTENT
• Will need to figure out how many connections you will have at one time (MySQL makes quick connections. Don’t really need to make a persistent one, but likely won’t hurt)

Direct Query Execution using PDO

• Queries that modify information use the exec() method.
• Return value is the number of rows affected by the operation. Returns boolean FALSE on error (INSERT, UPDATE, DELETE).
• Queries that select information use the query() method.

Problems with direct queries

• Query needs to be interpreted on each execution. Can be a waste for frequently repeated queries.
• Security issues with un-escaped user input can lead to SQL injection.
• Escaping: quote() method. Handles special characters in PDO. Quote automatically adds single quotes to wrap the query: "SELECT * FROM USERS WHERE".quote($variable)." LIMIT 0,1"

Prepared statements

• Compile once and execute many times
• Separation between structure and input to prevents SQL injections
• Often faster than query()/exec() even for single runs.
• Uses wildcards/tokens to hold variables; those variables are handled as a parameter, making injection attacks much less likely (bound parameters)

Retrieving information using PDO

• Array (Numeric or associative)
• String (for single column result sets; ex: selecting an id from a user login table)
• Fetch into a class
• Objects
  • Ability to retrieve data into an object
  • Ability to retrieve data into an exisiting object
• Callback Function
• Lazy fetching
• Iterators

Result Iteration - Fastest way of retrieving data through PDO

$res=$db->query("select * from users",PDO::FETCH_ASSOC);
foreach($res as $row){
   // returns an associated array
}

Lazy fetches

• Lazy fetches results in a form object, but holds the populating properties until they are actually used.
• Prevents PHP from fetching the data until you use it.

Related links

• PDO Functions
• Prepared Statements and Bound Parameters (from an article about using ext/mysqli)