Saturday, June 20, 2015

How to migrate from the MySQL Extension to PDO

My team's start-up has a web application that is currently using MySQLi Extensions.

Switching to PDO has many advantages:
  • offers a consistent API to work with a variety of databases
  • exposes high-level objects for the programmer to work with database connections
  • low-level drivers perform communication and resource handling with the database server

Basic Workflow

The basic workflow for working with a database can be thought of as a 5-step process:
  1. Establish a connection to the database server and select the database you’ll be working with
  2. Construct a query to send the server
  3. Send the query
  4. Iterate over the returned result rows
  5. Free the resources used by the result and possibly the database connection
With PDO, the same process can be followed and looks like this:

// Step 1: Establish a connection
$db = new PDO("mysql:host=localhost;dbname=testdb", "testusr", "secretpass");
// Step 2: Construct a query
$query = "SELECT * FROM foo WHERE bar = " . $db->quote($zip);
// Step 3: Send the query
$result = $db->query($query);
// Step 4: Iterate over the results
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
// Step 5: Free used resources
$db = null;