Background

Seasoned (read: old) PHP programmer definitely must have used this MySQL extension most of the time. Build a quick and dirty demo application or proof of concept application, this extension is pretty straightforward. The MySQL extension is the oldest of the three and was the original option developers used to communicate with MySQL. However, this extension is now being deprecated in PHP 5.5 and removed in PHP 7.0. So the choice now is between the other two alternatives because of improvements made in newer releases of both PHP and MySQL and security issues.

MySQLi is the “improved” extension for working with MySQL databases. It takes advantage of features that are available in newer versions of the MySQL server, exposes both a function-oriented and an object-oriented interface to the developer and a does few other nifty things. If PDO isn’t right for your project, then this is the extension you should be using.

Then most recent extension is PDO, which stands for “PHP Data Objects.” PDO offers an API that consolidates most of the functionality that was previously spread across the major database access extensions, i.e. MySQL, PostgreSQL, SQLite, MSSQL, etc. The interface exposes high-level objects for the programmer to work with database connections, queries and result sets, and low-level drivers perform communication and resource handling with the database server. A lot of discussion and work is going into PDO and it’s considered the appropriate method of working with databases in modern, professional code.

Migration to PDO

I've done some migration from MySQL extension to MySQL "Improved" extension projects. From what I've gone through, conversion to MySQLi is an awful lot of work. So the best way to go is using PDO extension. In this post, I assume we already have PDO extension installed. I will share my experience on PDO Installation steps in another post.

Accessing MySQL database from PHP could be divided into 4 steps:

1. Connect to database server and choose the database to be manipulated
2. Build the query to be processed by database server and send the query
3. Process returned response from database server
4. Free the resource used by the connection and close the database connection

Using the old MySQL Extension, those steps can be achieved using these lines of codes;

<?php
// Step 1: Establish a connection and choose database
$db = mysql_connect("localhost", "your_username", "secret_password");
mysql_select_db("some_database", $db);

// Step 2: Build the query and send to database server
$query = "SELECT * FROM foo WHERE bar = '" . mysql_real_escape_string($zip) . "'";
$result = mysql_query($query, $db);

// Step 3: Process the response from database server
while($row = myql_fetch_assoc($result)) {
    print_r($row);
}

// Step 4: Free used resources
mysql_free_result($result);
mysql_close($db);

With PDO, the same process can be followed and looks like this:

<?php
// Step 1: Establish a connection and choose database
$db = new PDO("mysql:host=localhost;dbname=some_database", "your_username", "your_password");

// Step 2: Build the query and send to database server
$query = "SELECT * FROM foo WHERE bar = " . $db->quote($zip);
$result = $db->query($query);

// Step 3: Process the response from database server
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

// Step 4: Free used resources
$result->closeCursor();
$db = null;

Summary

Although most of the time I uses Eloquent ORM (with or without Laravel), sometime I still need a quick script to interact with database server. Using PDO is more recommended because:

• It allows more secure interaction between your application and database server
• Similar syntax between database engines