PHP PDO with MySQL, PostgreSQL or SQLite

I use PHP daily, and so should you. The fact that you’re here, says you know it too.

PHP is a good language (and web ecosystem) that doesn’t force you to use any flavor of the week framework or design pattern, dreamt up by some self-made benevolent superhero zen guru who understands everything, and who we should all listen to and respect for their infinite other-worldy wisdom, and they just happen to work at some or other big internet company. Or some self-proclaimed university genius (idiot) show-off on the tenure track paddling their pseudo-intellectual snake oil and pretending to be useful. We know you’re definitely not cogs in the corporate machine, trying to control our coding thoughts. Definitely.

You do what you want, how you want, anything you want.

Making things more simple is better than making things more complicated. Complicated isn’t cool. It’s complicated. Ask anyone.

PDO (PHP Data Objects) is a PHP database driver library, that you can use to connect to, and do stuff on, MySQL, PostgreSQL, SQLite, and many other databases. You use regular SQL queries to communicate with the database.

PDO is just boilerplate code to connect to a database, send queries to it, and get results from it. You don’t have to use PDO if you don’t want to, but I recommend using PDO, instead of a custom library to access the database. When you want to change to another type of database server, all the connection boilerplate is already done, and the SQL code should still work, as long as you don’t use uber-fancy SQL from the year 30,000.

Keep it simple.

First, define your constants.

define('MYSQL_HOST', 'localhost');
define('MYSQL_DATABASE', '{your_database_name}');
define('MYSQL_USERNAME', '{your_username}');
define('MYSQL_PASSWORD', '{your_password}'); 

Then, connect to the database:

function get_db() {
    try {
        static $db = null;
        if (!!$db) return $db;
        $db = new PDO('mysql:host=' . MYSQL_HOST . ';dbname=' . MYSQL_DATABASE, MYSQL_USERNAME, MYSQL_PASSWORD);
        if (!$db) {
            error_log('get_db(): !$db');
            $db = false;
            db_error(func_get_args());
        }
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $db;
    }
    catch (PDOException $e) {
        error_log('get_db() exception: ' . $e->getMessage());
        $db = false;
        db_error(func_get_args());
    }
}

# $st ::= PDOStatement
function db_error($args, $st = null) {
    $db = get_db();
    error_log('$db->errorInfo(): ' . (!!$db ? print_r($db->errorInfo(), true) : '!$db'));
    error_log('$st->errorInfo(): ' . (!!$st ? print_r($st->errorInfo(), true) : '!$st'));
    if ($args) error_log(print_r($args, true));
} 

Write a helper method to send a query to the database and get results back:

# $params ::= [param1 => val1, param2 => val2, ...]
function query($query, $params = []) {
    $st = null;
    try {
        $db = get_db();
        foreach ($params as $key => &$value)
            $query = preg_replace("/:$key/", $db->quote($value), $query);
        $st = $db->prepare($query);
        if (!$st) {
            error_log('query(): !$st - Invalid query: ' . $query);
            db_error(func_get_args());
        }
        $st->execute();
        return $st;
    }
    catch (PDOException $e) {
        error_log('query() exception: '. $e->getMessage());
        db_error(func_get_args(), $st);
    }
}

Write a utility method to fetch a single row of data from the database:

function fetch($query) {
    $st = null;
    try {
        $st = call_user_func_array('query', func_get_args());
        $result = $st->fetch(PDO::FETCH_CLASS);
        return $result;
    }
    catch (PDOException $e) {
        error_log('fetch() exception: ' . $e->getMessage());
        db_error(func_get_args(), $st);
    }
}

To select a row of data from the database using a SQL SELECT statement:

$row = fetch('SELECT name, surname FROM users WHERE id = :id', ['id' => 123]);
if ($row)
    echo 'Hello ', $row->name, ' ', $row->surname; 

Write a utility method to fetch an array of rows from the database:

function fetchAll($query) {
    $st = null;
    try {
        $st = call_user_func_array('query', func_get_args());
        $result = $st->fetchAll(PDO::FETCH_CLASS);
        return $result;
    }
    catch (PDOException $e) {
        error_log('fetchAll() exception: ' . $e->getMessage());
        db_error(func_get_args(), $st);
    }
}

To select an array of data rows from the database using a SQL SELECT statement:

$row = fetchAll('SELECT name, surname FROM users');
foreach ($rows as $row)
    echo 'Hello ', $row->name, ' ', $row->surname; 

Write a utility method to execute a SQL insert, update or delete statement, which seldom returns a useful result from the database:

function execute($query) {
    $st = null;
    try {
        call_user_func_array('query', func_get_args());
    }
    catch (PDOException $e) {
        error_log('execute() exception: ' . $e->getMessage());
        db_error(func_get_args(), $st);
    }
}

To execute a delete or update on the database:

execute('UPDATE users SET age = age + inc', ['inc' => 20]);
execute('DELETE FROM users'); 

To get the auto-incremented ID of the last row that was inserted with your PDO connection:

function last_insert_id() {
    try {
        $db = get_db();
        return $db->lastInsertId();
    }
    catch (PDOException $e) {
        error_log('last_insert_id() exception: ' . $e->getMessage());
        db_error(func_get_args(), $st);
    }
}

To insert a row and get the row’s auto-incremented ID:

execute('INSERT INTO users (name, surname, age) VALUES ('John', :surname, 40)', ['surname' => 'Doe']);
echo last_insert_id();

execute('INSERT INTO users SET name = 'Jane', surname = :surname, age = 35', ['surname' => 'Smith']);
echo last_insert_id();