PHP Database Access

Content:

  1. Introduction
  2. mysql extension
  3. mysqli extension
    1. Procedural style
    2. Object oriented style
  4. pgsql extension
  5. PDO extension
  6. ORM
    1. Doctrine
    2. Eloquent
  7. Calling stored procedures
  8. NoSQL

Introduction:

PHP comes with a large number of database extensions.

These database extension fall in two categories:

Some of the most widely used database specific extensions are:

The most widely used database independent extensions is:

All the examples below will use the following database table:

CREATE TABLE T1 (
    F1 INTEGER NOT NULL,
    F2 VARCHAR(50),
    PRIMARY KEY(F1)
);

Also note that I am not really a PHP programmer so the code may not be optimal.

mysql extension:

Let us repeat again:

This section only exist to facilitate comparing mysql extension with newer supported extensions. This is often needed when converting exisiting PHP code from using mysql extension to using a newer supported extension. Do not write new code using mysql extension.

Example of code using mysql extension:

<?php
// necesarry to avoid warnings about deprecated functionality
error_reporting(E_ALL ^ E_DEPRECATED);

function get_connection() {
    // connect to server
    $con = mysql_connect('localhost', 'root', '') or die(mysql_error());
    // select database
    mysql_select_db('Test', $con) or die(mysql_error($con));
    // return connection (note: no globals)
    return $con;
}

function t1_get_one($f2) {
    // connect to server and select database
    $con = get_connection();
    // construct SQL statement
    $sqlstr = "SELECT F1 FROM T1 WHERE F2='" . mysql_real_escape_string($f2, $con) . "'";
    // execute SQL statement
    $rs = mysql_query($sqlstr, $con) or die(mysql_error($con));
    // get first row and field F1
    if($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
        $f1 = $row['F1'];   
    } else {
        die("$f2 not found");
    }
    // close connection to server
    mysql_close($con);
    // return value
    return $f1;
}

function t1_get_all() {
    // connect to server and select database
    $con = get_connection();
    // construct SQL statement
    $sqlstr = 'SELECT F1,F2 FROM T1';
    // execute SQL statement
    $rs = mysql_query($sqlstr, $con) or die(mysql_error($con));
    // create array to contain rows
    $res = array();
    // get all rows
    while($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
        $res[] = $row;
    }
    // close connection to server
    mysql_close($con);
    // return array with rows
    return $res;
}

function t1_put($f1, $f2) {
    // connect to server and select database
    $con = get_connection();
    // construct SQL statement
    $sqlstr = "INSERT INTO T1(F1,F2) VALUES(" . (int)$f1 . ",'" . mysql_real_escape_string($f2, $con) . "')";
    // execute SQL statement
    mysql_query($sqlstr, $con) or die(mysql_error($con));
    // close connection to server
    mysql_close($con);
}

function t1_remove($f1) {
    // connect to server and select database
    $con = get_connection();
    // construct SQL statement
    $sqlstr = "DELETE FROM T1 WHERE F1=" . (int)$f1;
    // execute SQL statement
    mysql_query($sqlstr, $con) or die(mysql_error($con));
    // close connection to server
    mysql_close($con);
}

function t1_display($data) {
    $rows = "";
    foreach($data as $row) {
        $f1 = $row['F1'];
        $f2 = $row['F2'];
        $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
    }
    return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
}

function test() {
    $f1 = t1_get_one('BB');
    echo "$f1<br>\r\n";
    $data = t1_get_all();
    echo t1_display($data);
    t1_put(999, 'XXX');
    $data = t1_get_all();
    echo t1_display($data);
    t1_remove(999);
    $data = t1_get_all();
    echo t1_display($data);
}

test();
?>

The comments in the code should explain what the code does.

Note that a lot of code using mysql extension is even worse than this:

The code shown here is not production ready. Among other things the error handling is test style not production style.

mysqli extension:

As mysql extension became obsolete a new mysqli extension was added to PHP.

The mysqli extension can be used in two styles:

I will recommend using mysqli object oriented style over mysqli procedural style. The code looks slightly cleaner and PHP is an object oriented language today. And it is really not that much harder to convert mysql extension code to mysqli object oriented style code than to mysqli procedural style code.

Procedural style

Example of code using mysqli extension procedural style:

<?php
function get_connection() {
    // connect to server and select database
    $con = mysqli_connect('localhost', 'root', '', 'Test') or die(mysqli_connect_error());
    // return connection (note: no globals)
    return $con;
}

function t1_get_one($f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = mysqli_prepare($con, 'SELECT F1 FROM T1 WHERE F2=?') or die(mysqli_error($con));
    // bind input
    mysqli_stmt_bind_param($stmt, 's', $f2);
    // execute
    mysqli_stmt_execute($stmt) or die(mysqli_error(mysqli_error($con)));
    $rs = mysqli_stmt_get_result($stmt);
    // get first row and field F1
    if($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
        $f1 = $row['F1'];   
    } else {
        die("$f2 not found");
    }
    // close statement
    mysqli_stmt_close($stmt);
    // close connection to server
    mysqli_close($con);
    // return value
    return $f1;
}

function t1_get_all() {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = mysqli_prepare($con, 'SELECT F1,F2 FROM T1') or die(mysqli_error($con));
    // execute
    mysqli_stmt_execute($stmt) or die(mysqli_error($con));
    $rs = mysqli_stmt_get_result($stmt);
    // create array to contain rows
    $res = array();
    // get all rows
    while($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
        $res[] = $row;
    }
    // close statement
    mysqli_stmt_close($stmt);
    // close connection to server
    mysqli_close($con);
    // return array with rows
    return $res;
}

function t1_put($f1, $f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = mysqli_prepare($con, 'INSERT INTO T1(F1,F2) VALUES(?,?)') or die(mysqli_error($con));
    // bind input
    mysqli_stmt_bind_param($stmt, 'is', $f1, $f2);
    // execute
    mysqli_stmt_execute($stmt) or die(mysqli_error($con));
    // close statement
    mysqli_stmt_close($stmt);
    // close connection to server
    mysqli_close($con);
}

function t1_remove($f1) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = mysqli_prepare($con, 'DELETE FROM T1 WHERE F1=?') or die(mysqli_error($con));
    // bind input
    mysqli_stmt_bind_param($stmt, 'i', $f1);
    // execute
    mysqli_stmt_execute($stmt) or die(mysqli_error($con));
    // close statement
    mysqli_stmt_close($stmt);
    // close connection to server
    mysqli_close($con);
}

function t1_display($data) {
    $rows = "";
    foreach($data as $row) {
        $f1 = $row['F1'];
        $f2 = $row['F2'];
        $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
    }
    return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
}

function test() {
    $f1 = t1_get_one('BB');
    echo "$f1<br>\r\n";
    $data = t1_get_all();
    echo t1_display($data);
    t1_put(999, 'XXX');
    $data = t1_get_all();
    echo t1_display($data);
    t1_remove(999);
    $data = t1_get_all();
    echo t1_display($data);
}

test();
?>

Note that the code uses prepared statement for everything. That is strongly recommended. Otherwise it is easy to forget one place and become vulnerable to SQL injection.

For more info on prepared statement see here.

The code shown here is not production ready. Among other things the error handling is test style not production style.

Object oriented style

Example of code using mysqli extension object oriented style:

<?php
function get_connection() {
    // connect to server and select database
    $con = new mysqli('localhost', 'root', '', 'Test');
    if(mysqli_connect_errno()) {
        die(mysqli_connect_error());
    }
    // return connection (note: no globals)
    return $con;
}

function t1_get_one($f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('SELECT F1 FROM T1 WHERE F2=?') or die(mysqli_error($con));
    // bind input
    $stmt->bind_param('s', $f2);
    // execute
    $stmt->execute() or die(mysqli_error($con));
    $rs = $stmt->get_result();
    // get first row and field F1
    if($row = $rs->fetch_array(MYSQLI_ASSOC)) {
        $f1 = $row['F1'];   
    } else {
        die("$f2 not found");
    }
    // close statement
    $stmt->close();
    // close connection to server
    $con->close();
    // return value
    return $f1;
}

function t1_get_all() {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('SELECT F1,F2 FROM T1') or die(mysqli_error($con));
    // execute
    $stmt->execute() or die(mysqli_error($con));
    $rs = $stmt->get_result();
    // create array to contain rows
    $res = array();
    // get all rows
    while($row = $rs->fetch_array(MYSQLI_ASSOC)) {
        $res[] = $row;
    }
    // close statement
    $stmt->close();
    // close connection to server
    $con->close();
    // return array with rows
    return $res;
}

function t1_put($f1, $f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('INSERT INTO T1(F1,F2) VALUES(?,?)') or die(mysqli_error($con));
    // bind input
    $stmt->bind_param('is', $f1, $f2);
    // execute
    $stmt->execute() or die(mysqli_error($con));
    // close statement
    $stmt->close();
    // close connection to server
    $con->close();
}

function t1_remove($f1) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('DELETE FROM T1 WHERE F1=?') or die(mysqli_error($con));
    // bind input
    $stmt->bind_param('i', $f1);
    // execute
    $stmt->execute() or die(mysqli_error($con));
    // close statement
    $stmt->close();
    // close connection to server
    $con->close();
}

function t1_display($data) {
    $rows = "";
    foreach($data as $row) {
        $f1 = $row['F1'];
        $f2 = $row['F2'];
        $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
    }
    return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
}

function test() {
    $f1 = t1_get_one('BB');
    echo "$f1<br>\r\n";
    $data = t1_get_all();
    echo t1_display($data);
    t1_put(999, 'XXX');
    $data = t1_get_all();
    echo t1_display($data);
    t1_remove(999);
    $data = t1_get_all();
    echo t1_display($data);
}

test();
?>

Note that the code uses prepared statement for everything. That is strongly recommended. Otherwise it is easy to forget one place and become vulnerable to SQL injection.

For more info on prepared statement see here.

The code shown here is not production ready. Among other things the error handling is test style not production style.

When using an object oriented database API then it was rather obvious also to write ones own code object oriented.

Example:

<?php
class T1 {
    public $f1;
    public $f2;
    public function __construct($f1, $f2) {
        $this->f1 = $f1;
        $this->f2 = $f2;
    }
}

interface T1DatabaseAccess {
    public function get_one($f2);
    public function get_all();
    public function put($o);
    public function remove($f1);
}

class T1DatabaseAccessMysqli implements T1DatabaseAccess {
    private $server;
    private $username;
    private $password;
    private $database;
    public function __construct($server, $username, $password, $database) {
        $this->server = $server; 
        $this->username = $username; 
        $this->password = $password; 
        $this->database = $database; 
    }
    private function get_connection() {
        // connect to server and select database
        $con = new mysqli($this->server, $this->username, $this->password, $this->database);
        if(mysqli_connect_errno()) {
            die(mysqli_connect_error());
        }
        // return connection (note: no globals)
        return $con;
    }
    public function get_one($f2) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('SELECT F1,F2 FROM T1 WHERE F2=?') or die(mysqli_error($con));
        // bind input
        $stmt->bind_param('s', $f2);
        // execute
        $stmt->execute() or die(mysqli_error($con));
        $rs = $stmt->get_result();
        // get first row and field F1
        if($row = $rs->fetch_array(MYSQLI_ASSOC)) {
            $res = new T1($row['F1'], $row['F2']);  
        } else {
            die("$f2 not found");
        }
        // close statement
        $stmt->close();
        // close connection to server
        $con->close();
        // return value
        return $res;
    }
    public function get_all() {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('SELECT F1,F2 FROM T1') or die(mysqli_error($con));
        // execute
        $stmt->execute() or die(mysqli_error($con));
        $rs = $stmt->get_result();
        // create array to contain rows
        $res = array();
        // get all rows
        while($row = $rs->fetch_array(MYSQLI_ASSOC)) {
            $res[] = new T1($row['F1'], $row['F2']);
        }
        // close statement
        $stmt->close();
        // close connection to server
        $con->close();
        // return array with rows
        return $res;
    }
    public function put($o) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('INSERT INTO T1(F1,F2) VALUES(?,?)') or die(mysqli_error($con));
        // bind input
        $stmt->bind_param('is', $o->f1, $o->f2);
        // execute
        $stmt->execute() or die(mysqli_error($con));
        // close statement
        $stmt->close();
        // close connection to server
        $con->close();
    }
    public function remove($f1) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('DELETE FROM T1 WHERE F1=?') or die(mysqli_error($con));
        // bind input
        $stmt->bind_param('i', $f1);
        // execute
        $stmt->execute() or die(mysqli_error($con));
        // close statement
        $stmt->close();
        // close connection to server
        $con->close();
    }
}

class T1DatabaseAccessFactory {
    const MYSQLI = 'mysqli';
    public static function get_database_access($id, $server, $username, $password, $database) {
        if($id === T1DatabaseAccessFactory::MYSQLI) {
            return new T1DatabaseAccessMysqli($server, $username, $password, $database);
        } else {
            throw new Exception("Unknown T1DatabaseAccess id: $id");
        }
    }
}

interface T1Presenter {
    public function display($data);
}

class T1PresenterHtmlTable implements T1Presenter {
    public function display($data) {
        $rows = "";
        foreach($data as $row) {
            $f1 = $row->f1;
            $f2 = $row->f2;
            $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
        }
        return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
    }
}

class T1PresenterFactory {
    const HTML_TABLE = 'HTML Table';
    public static function get_presenter($id) {
        if($id === T1PresenterFactory::HTML_TABLE) {
            return new T1PresenterHtmlTable()   ;       
        } else {
            throw new Exception("Unknown T1Presenter id: $id");
        }
    }
}

function test() {
    $data_access = T1DatabaseAccessFactory::get_database_access(T1DatabaseAccessFactory::MYSQLI, 'localhost', 'root', '', 'Test');
    $presenter = T1PresenterFactory::get_presenter(T1PresenterFactory::HTML_TABLE);
    $f1 = $data_access->get_one('BB')->f1;
    echo "$f1<br>\r\n";
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->put(new T1(999, 'XXX'));
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->remove(999);
    $data = $data_access->get_all();
    echo $presenter->display($data);
}

test();
?>

pgsql extension:

Procedural code:

<?php
function get_connection() {
    // connect to server and select database
    $con = pg_connect('host=localhost port=5432 dbname=Test user=postgres password=xxxxxx') or die(pg_last_error());
    // return connection (note: no globals)
    return $con;
}

function t1_get_one($f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    pg_prepare($con, 'stmt_get_one', 'SELECT f1 FROM t1 WHERE f2=$1') or die(pg_last_error($con));
    // execute
    $rs = pg_execute($con, 'stmt_get_one', array($f2)) or die(pg_last_error($con));
    // get first row and field F1
    if($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
        $res = $row['f1'];
    } else {
        die("$f2 not found");
    }
    // close
    pg_query('DEALLOCATE stmt_get_one');
    pg_close($con);
    // return value
    return $res;
}

function t1_get_all() {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    pg_prepare($con, 'stmt_get_all', 'SELECT f1,f2 FROM t1') or die(pg_last_error($con));
    // execute
    $rs = pg_execute($con, 'stmt_get_all', array()) or die(pg_last_error($con));
    // create array to contain rows
    $res = array();
    // get all rows
    while($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
        $res[] = $row;
    }
    // close
    pg_query('DEALLOCATE stmt_get_all');
    pg_close($con);
    // return array with rows
    return $res;
}

function t1_put($f1, $f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    pg_prepare($con, 'stmt_put', 'INSERT INTO t1(f1,f2) VALUES($1,$2)') or die(pg_last_error($con));
    // execute
    pg_execute($con, 'stmt_put', array($f1, $f2)) or die(pg_last_error($con));
    // close
    pg_query('DEALLOCATE stmt_put');
    pg_close($con);
}

function t1_remove($f1) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    pg_prepare($con, 'stmt_remove', 'DELETE FROM t1 WHERE f1=$1') or die(pg_last_error($con));
    // execute
    pg_execute($con, 'stmt_remove', array($f1)) or die(pg_last_error($con));
    // close
    pg_query('DEALLOCATE stmt_remove');
    pg_close($con);
}

function t1_display($data) {
    $rows = "";
    foreach($data as $row) {
        $f1 = $row['f1'];
        $f2 = $row['f2'];
        $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
    }
    return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";
}

function test() {
    $f1 = t1_get_one('BB');
    echo "$f1<br>\r\n";
    $data = t1_get_all();
    echo t1_display($data);
    t1_put(999, 'XXX');
    $data = t1_get_all();
    echo t1_display($data);
    t1_remove(999);
    $data = t1_get_all();
    echo t1_display($data);
}

test();
?>

Object oriented code:

<?php
class T1 {
    public $f1;
    public $f2;
    public function __construct($f1, $f2) {
        $this->f1 = $f1;
        $this->f2 = $f2;
    }
}

interface T1DatabaseAccess {
    public function get_one($f2);
    public function get_all();
    public function put($o);
    public function remove($f1);
}

class T1DatabaseAccessPgsql implements T1DatabaseAccess {
    private $server;
    private $username;
    private $password;
    private $database;
    public function __construct($server, $username, $password, $database) {
        $this->server = $server;
        $this->username = $username;
        $this->password = $password;
        $this->database = $database;
    }
    private function get_connection() {
        // connect to server and select database
        $con = pg_connect(sprintf('host=%s port=5432 dbname=%s user=%s password=%s', $this->server, $this->database, $this->username, $this->password)) or die(pg_last_error());
        // return connection (note: no globals)
        return $con;
    }
    public function get_one($f2) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        pg_prepare($con, 'stmt_get_one', 'SELECT f1,f2 FROM t1 WHERE f2=$1') or die(pg_last_error($con));
        // execute
        $rs = pg_execute($con, 'stmt_get_one', array($f2)) or die(pg_last_error($con));
        // get first row and field F1
        if($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
            $res = new T1($row['f1'], $row['f2']);  
        } else {
            die("$f2 not found");
        }
        // close
        pg_query('DEALLOCATE stmt_get_one');
        pg_close($con);
        // return value
        return $res;
    }
    public function get_all() {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        pg_prepare($con, 'stmt_get_all', 'SELECT f1,f2 FROM t1') or die(pg_last_error($con));
        // execute
        $rs = pg_execute($con, 'stmt_get_all', array()) or die(pg_last_error($con));
        // create array to contain rows
        $res = array();
        // get all rows
        while($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
            $res[] = new T1($row['f1'], $row['f2']); 
        }
        // close
        pg_query('DEALLOCATE stmt_get_all');
        pg_close($con);
        // return array with rows
        return $res;
    }
    public function put($o) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        pg_prepare($con, 'stmt_put', 'INSERT INTO t1(f1,f2) VALUES($1,$2)') or die(pg_last_error($con));
        // execute
        pg_execute($con, 'stmt_put', array($o->f1, $o->f2)) or die(pg_last_error($con));
        // close
        pg_query('DEALLOCATE stmt_put');
        pg_close($con);
    }
    public function remove($f1) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        pg_prepare($con, 'stmt_remove', 'DELETE FROM t1 WHERE f1=$1') or die(pg_last_error($con));
        // execute
        pg_execute($con, 'stmt_remove', array($f1)) or die(pg_last_error($con));
        // close
        pg_query('DEALLOCATE stmt_remove');
        pg_close($con);
    }
}

class T1DatabaseAccessFactory {
    const PGSQL = 'pgsql';
    public static function get_database_access($id, $server, $username, $password, $database) {
        if($id === T1DatabaseAccessFactory::PGSQL) {
            return new T1DatabaseAccessPgsql($server, $username, $password, $database);
        } else {
            throw new Exception("Unknown T1DatabaseAccess id: $id");
        }
    }
}

interface T1Presenter {
    public function display($data);
}

class T1PresenterHtmlTable implements T1Presenter {
    public function display($data) {
        $rows = "";
        foreach($data as $row) {
            $f1 = $row->f1;
            $f2 = $row->f2;
            $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
        }
        return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";
    }
}

class T1PresenterFactory {
    const HTML_TABLE = 'HTML Table';
    public static function get_presenter($id) {
        if($id === T1PresenterFactory::HTML_TABLE) {
            return new T1PresenterHtmlTable()   ;
        } else {
            throw new Exception("Unknown T1Presenter id: $id");
        }
    }
}

function test() {
    $data_access = T1DatabaseAccessFactory::get_database_access(T1DatabaseAccessFactory::PGSQL, 'localhost', 'postgres', 'xxxxxx', 'Test');
    $presenter = T1PresenterFactory::get_presenter(T1PresenterFactory::HTML_TABLE);
    $f1 = $data_access->get_one('BB')->f1;
    echo "$f1<br>\r\n";
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->put(new T1(999, 'XXX'));
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->remove(999);
    $data = $data_access->get_all();
    echo $presenter->display($data);
}

test();
?>

PDO extension

PDO (PHP Data Objects) is a newer extension supporting multiple databases not just MySQL.

Assuming one does not use database specific SQL then changing database is just a matter of changing the connection string.

Example of code using PDO extension:

<?php
function get_connection() {
    // connect to server and select database
    $con = new PDO('mysql:host=localhost;dbname=Test', 'root', '');
    // use exceptions instead of error results
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // get columns by name
    $con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    // return connection (note: no globals)
    return $con;
}

function t1_get_one($f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('SELECT F1 FROM T1 WHERE F2=:F2');
    // execute with parameters
    $stmt->execute(array(':F2' => $f2));
    // get first row and field F1
    if($row = $stmt->fetch()) {
        $f1 = $row['F1'];   
    } else {
        die("$f2 not found");
    }
    // return value
    return $f1;
}

function t1_get_all() {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('SELECT F1,F2 FROM T1');
    // execute with no parameters
    $stmt->execute(array());
    // create array to contain rows
    $res = array();
    // get all rows
    while($row = $stmt->fetch()) {
        $res[] = $row;
    }
    // return array with rows
    return $res;
}

function t1_put($f1, $f2) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('INSERT INTO T1(F1,F2) VALUES(:F1,:F2)');
    // execute with parameters
    $stmt->execute(array(':F1' => $f1, ':F2' => $f2));
}

function t1_remove($f1) {
    // connect to server and select database
    $con = get_connection();
    // prepare SQL statement
    $stmt = $con->prepare('DELETE FROM T1 WHERE F1=:F1');
    // execute with parameters
    $stmt->execute(array(':F1' => $f1));
}

function t1_display($data) {
    $rows = "";
    foreach($data as $row) {
        $f1 = $row['F1'];
        $f2 = $row['F2'];
        $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
    }
    return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
}

function test() {
    $f1 = t1_get_one('BB');
    echo "$f1<br>\r\n";
    $data = t1_get_all();
    echo t1_display($data);
    t1_put(999, 'XXX');
    $data = t1_get_all();
    echo t1_display($data);
    t1_remove(999);
    $data = t1_get_all();
    echo t1_display($data);
}

test();
?>

Note that the code uses prepared statement for everything. That is strongly recommended. Otherwise it is easy to forget one place and become vulnerable to SQL injection.

For more info on prepared statement see here.

The code shown here is not production ready. Among other things it does not handle exceptions at all.

When using an object oriented database API then it was rather obvious also to write ones own code object oriented.

Example:

<?php
class T1 {
    public $f1;
    public $f2;
    public function __construct($f1, $f2) {
        $this->f1 = $f1;
        $this->f2 = $f2;
    }
}

interface T1DatabaseAccess {
    public function get_one($f2);
    public function get_all();
    public function put($o);
    public function remove($f1);
}

class T1DatabaseAccessPDO implements T1DatabaseAccess {
    private $constr;
    private $username;
    private $password;
    public function __construct($constr, $username, $password) {
        $this->constr = $constr; 
        $this->username = $username; 
        $this->password = $password; 
    }
    private function get_connection() {
        // connect to server and select database
        $con = new PDO($this->constr, $this->username, $this->password);
        // use exceptions instead of error results
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // get columns by name
        $con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        // return connection (note: no globals)
        return $con;
    }
    public function get_one($f2) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('SELECT F1,F2 FROM T1 WHERE F2=:F2');
        // execute with parameters
        $stmt->execute(array(':F2' => $f2));
        // get first row and field F1
        if($row = $stmt->fetch()) {
            $res = new T1($row['F1'], $row['F2']);  
        } else {
            die("$f2 not found");
        }
        // return value
        return $res;
    }
    public function get_all() {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('SELECT F1,F2 FROM T1');
        // execute with no parameters
        $stmt->execute(array());
        // create array to contain rows
        $res = array();
        // get all rows
        while($row = $stmt->fetch()) {
            $res[] = new T1($row['F1'], $row['F2']);
        }
        // return array with rows
        return $res;
    }
    public function put($o) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('INSERT INTO T1(F1,F2) VALUES(:F1,:F2)');
        // execute with parameters
        $stmt->execute(array(':F1' => $o->f1, ':F2' => $o->f2));
    }
    public function remove($f1) {
        // connect to server and select database
        $con = $this->get_connection();
        // prepare SQL statement
        $stmt = $con->prepare('DELETE FROM T1 WHERE F1=:F1');
        // execute with parameters
        $stmt->execute(array(':F1' => $f1));
    }
}

class T1DatabaseAccessFactory {
    const PDO = 'PDO';
    public static function get_database_access($id, $constr, $username, $password) {
        if($id === T1DatabaseAccessFactory::PDO) {
            return new T1DatabaseAccessPDO($constr, $username, $password);
        } else {
            throw new Exception("Unknown T1DatabaseAccess id: $id");
        }
    }
}

interface T1Presenter {
    public function display($data);
}

class T1PresenterHtmlTable implements T1Presenter {
    public function display($data) {
        $rows = "";
        foreach($data as $row) {
            $f1 = $row->f1;
            $f2 = $row->f2;
            $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
        }
        return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";   
    }
}

class T1PresenterFactory {
    const HTML_TABLE = 'HTML Table';
    public static function get_presenter($id) {
        if($id === T1PresenterFactory::HTML_TABLE) {
            return new T1PresenterHtmlTable()   ;       
        } else {
            throw new Exception("Unknown T1Presenter id: $id");
        }
    }
}

function test() {
    $data_access = T1DatabaseAccessFactory::get_database_access(T1DatabaseAccessFactory::PDO, 'mysql:host=localhost;dbname=Test', 'root', '');
    $presenter = T1PresenterFactory::get_presenter(T1PresenterFactory::HTML_TABLE);
    $f1 = $data_access->get_one('BB')->f1;
    echo "$f1<br>\r\n";
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->put(new T1(999, 'XXX'));
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->remove(999);
    $data = $data_access->get_all();
    echo $presenter->display($data);
}

test();
?>

ORM

ORM or O/R-Mapper or Object Relational Mapper frameworks are frameworks that maps between an object model and a relational data model. They enable the application code to deal only with the object model and then the ORM translate to relational database and SQL. Consider it "SQL free" database programming.

ORM is not as popular in PHP as in Java or .NET, but it is starting to gain some traction.

Doctrine

Doctrine is one of the most popular PHP ORM frameworks.

Like many ORM frameworks it is heavily inspired by Hibernate for Java.

Example of code using Doctrine:

<?php
spl_autoload_register(function ($clznam) {
    include $clznam . '.php';
});

use Doctrine\ORM\Tools\Setup;
use Doctrine\ORM\EntityManager;

class T1 {
    public $f1;
    public $f2;
    public function __construct($f1, $f2) {
        $this->f1 = $f1;
        $this->f2 = $f2;
    }
}

interface T1DatabaseAccess {
    public function get_one($f2);
    public function get_all();
    public function put($o);
    public function remove($f1);
}

class T1DatabaseAccessDoctrine implements T1DatabaseAccess {
    private $em;
    public function __construct($conattr) {
        // connect to server, select database and setup mapping
        $mapping = Setup::createXMLMetadataConfiguration(array(__DIR__));
        $this->em = EntityManager::create($conattr, $mapping);
    }
    public function get_one($f2) {
        // execute DQL statement
        $q = $this->em->createQuery('SELECT t1 FROM T1 t1 WHERE t1.f2=:f2');
        $q->setParameter('f2', $f2);
        $res = $q->getResult();
        // return value
        if(count($res) > 0) {
            return $res[0];       
        } else {
            die("$f2 not found");
        }
    }
    public function get_all() {
        // execute DQL statement
        $q = $this->em->createQuery('SELECT t1 FROM T1 t1');
        $res = $q->getResult();
        // return collection with rows
        return $res;
    }
    public function put($o) {
        // persist and flush
        $this->em->persist($o);
        $this->em->flush();
    }
    public function remove($f1) {
        // find, remove and flush
        $entity = $this->em->find('T1', $f1);
        $this->em->remove($entity);
        $this->em->flush();
    }
}

class T1DatabaseAccessFactory {
    const DOCTRINE = 'DOCTRINE';
    public static function get_database_access($id, $conattr) {
        if($id === T1DatabaseAccessFactory::DOCTRINE) {
            return new T1DatabaseAccessDoctrine($conattr);
        } else {
            throw new Exception("Unknown T1DatabaseAccess id: $id");
        }
    }
}

interface T1Presenter {
    public function display($data);
}

class T1PresenterHtmlTable implements T1Presenter {
    public function display($data) {
        $rows = "";
        foreach($data as $row) {
            $f1 = $row->f1;
            $f2 = $row->f2;
            $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
        }
        return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";
    }
}

class T1PresenterFactory {
    const HTML_TABLE = 'HTML Table';
    public static function get_presenter($id) {
        if($id === T1PresenterFactory::HTML_TABLE) {
            return new T1PresenterHtmlTable()   ;
        } else {
            throw new Exception("Unknown T1Presenter id: $id");
        }
    }
}

function test() {
    $data_access = T1DatabaseAccessFactory::get_database_access(T1DatabaseAccessFactory::DOCTRINE,
                                                                array('driver' => 'pdo_mysql', 'host' => 'localhost', 'dbname' => 'Test', 'user' => 'root', 'password' => ''));
    $presenter = T1PresenterFactory::get_presenter(T1PresenterFactory::HTML_TABLE);
    $f1 = $data_access->get_one('BB')->f1;
    echo "$f1<br>\r\n";
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->put(new T1(999, 'XXX'));
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->remove(999);
    $data = $data_access->get_all();
    echo $presenter->display($data);
}

test();
?>

The T1DatabaseAccessDoctrine has become totall trivial due to the power of ORM.

Note that this code use XML to define the mapping. Doctrine does also support using annotations for that.

T1.dcm.xml:

<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping
                          http://raw.github.com/doctrine/doctrine2/master/doctrine-mapping.xsd">
    <entity name="T1" table="t1">
        <id name="f1" type="integer" column="f1"/>
        <field name="f2" type="string" column="f2"/>
    </entity>
</doctrine-mapping>

The example uses DQL (Doctrine Query Languuage) for the queries:

    public function get_one($f2) {
        // execute DQL statement
        $q = $this->em->createQuery('SELECT t1 FROM T1 t1 WHERE t1.f2=:f2');
        $q->setParameter('f2', $f2);
        $res = $q->getResult();
        // return value
        if(count($res) > 0) {
            return $res[0];       
        } else {
            die("$f2 not found");
        }
    }
    public function get_all() {
        // execute DQL statement
        $q = $this->em->createQuery('SELECT t1 FROM T1 t1');
        $res = $q->getResult();
        // return collection with rows
        return $res;
    }

It is also possible to use SQL:

use Doctrine\ORM\Query\ResultSetMappingBuilder;
...
    public function get_one($f2) {
        // execute SQL statement
        $rsmap = new ResultSetMappingBuilder($this->em);
        $rsmap->addRootEntityFromClassMetadata('T1', 't1');
        $q = $this->em->createNativeQuery('SELECT f1,f2 FROM t1 WHERE t1.f2=:f2', $rsmap);
        $q->setParameter('f2', $f2);
        $res = $q->getResult();
        // return value
        if(count($res) > 0) {
            return $res[0];       
        } else {
            die("$f2 not found");
        }
    }
    public function get_all() {
        // execute SQL statement
        $rsmap = new ResultSetMappingBuilder($this->em);
        $rsmap->addRootEntityFromClassMetadata('T1', 't1');
        $q = $this->em->createNativeQuery('SELECT f1,f2 FROM t1', $rsmap);
        $res = $q->getResult();
        // return collection with rows
        return $res;
    }

and QueryBuilder:

    public function get_one($f2) {
        // execute query builder
        $qb = $this->em->createQueryBuilder();
        $qb->select('t1')->from('T1', 't1')->where('t1.f2=:f2');
        $q = $qb->getQuery();
        $q->setParameter('f2', $f2);
        $res = $q->getResult();
        // return value
        if(count($res) > 0) {
            return $res[0];       
        } else {
            die("$f2 not found");
        }
    }
    public function get_all() {
        // execute query builder
        $qb = $this->em->createQueryBuilder();
        $qb->select('t1')->from('T1', 't1');
        $q = $qb->getQuery();
        $res = $q->getResult();
        // return collection with rows
        return $res;
    }

It is recommended to use DQL or QueryBuilder not SQL.

The code shown here is not production ready. Among other things it does not handle exceptions at all.

Eloquent:

Eloquent comes with the widely used PHP framework Laravel and is therefore obviously relevant.

It is possible to use Eloquent without Laravel though.

Eloquent use database first approach not code first approach.

Example of code using Eloquent:

<?php
require "vendor/autoload.php";

use Illuminate\Database\Capsule\Manager;
use Illuminate\Database\Eloquent\Model;

// data class - note that due to database first approach fields are dynamically defined based on what is in database
class T1 extends Model {
    protected $table = 't1'; // table in database
    public $primaryKey = 'f1'; // primnary key
    protected $fillable = ['f1', 'f2']; // list of modifiable fields
    public $timestamps = false; // do not expect two timestamp fields
}

interface T1DatabaseAccess {
    public function get_one($f2);
    public function get_all();
    public function put($o);
    public function remove($f1);
}

class T1DatabaseAccessEloquent implements T1DatabaseAccess {
    private $mgr;
    function __construct($conattr) {
        // connect to database
        $this->mgr = new Manager();
        $this->mgr->addConnection($conattr);
        $this->mgr->setAsGlobal();
        $this->mgr->bootEloquent();
        
    }
    public function get_one($f2) {
        // do WHERE and return result object
        return T1::where('f2', '=', $f2)->firstOrFail();
    }
    public function get_all() {
        // return array of objects
        return T1::all();
    }
    public function put($o) {
        // save object
        $o->save();
    }
    public function remove($f1) {
        // lookup object based on primary key and delete
        $o = T1::find($f1);
        $o->delete();
    }
}

class T1DatabaseAccessFactory {
    const ELOQUENT = 'Eloquent';
    public static function get_database_access($id, $conattr) {
        if($id === T1DatabaseAccessFactory::ELOQUENT) {
            return new T1DatabaseAccessEloquent($conattr);
        } else {
            throw new Exception("Unknown T1DatabaseAccess id: $id");
        }
    }
}

interface T1Presenter {
    public function display($data);
}

class T1PresenterHtmlTable implements T1Presenter {
    public function display($data) {
        $rows = "";
        foreach($data as $row) {
            $f1 = $row->f1;
            $f2 = $row->f2;
            $rows .= sprintf("<tr>\r\n<td>$f1</td>\r\n<td>$f2</td>\r\n</tr>\r\n");
        }
        return "<table>\r\n<tr>\r\n<th>F1</th>\r\n<th>F2</th>\r\n</tr>\r\n$rows</table>\r\n";
    }
}

class T1PresenterFactory {
    const HTML_TABLE = 'HTML Table';
    public static function get_presenter($id) {
        if($id === T1PresenterFactory::HTML_TABLE) {
            return new T1PresenterHtmlTable()   ;
        } else {
            throw new Exception("Unknown T1Presenter id: $id");
        }
    }
}

function test() {
    $data_access = T1DatabaseAccessFactory::get_database_access(T1DatabaseAccessFactory::ELOQUENT,
            array('driver' => 'mysql', 'host' => 'localhost', 'database' => 'Test', 'user' => 'root', 'password' => ''));
    $presenter = T1PresenterFactory::get_presenter(T1PresenterFactory::HTML_TABLE);
    $f1 = $data_access->get_one('BB')->f1;
    echo "$f1<br>\r\n";
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->put(T1::create(array('f1' => 999, 'f2' => 'XXX')));
    $data = $data_access->get_all();
    echo $presenter->display($data);
    $data_access->remove(999);
    $data = $data_access->get_all();
    echo $presenter->display($data);
}

test();
?>

The code shown here is not production ready. Among other things it does not handle exceptions at all.

Calling stored procedures:

The usage of stored procedures are not universally considered a good idea. But the reality is that they are sometimes used. Especially in Oracle DB and MS SQLServer environments.

The basic API for accessing stored procedures is the same as for using plain SQL statements with parameters, but stored procedures do come with a few special features that require special handling in the API.

Two such features are:

The following example illustrates how to handle that.

Stored procedures (for MS SQLServer):

CREATE PROCEDURE usp_multi @arg INTEGER
AS
BEGIN
    SELECT @arg+1 AS v
    SELECT 2*@arg AS v
END;
GO
CREATE PROCEDURE usp_return @inarg INTEGER, @outarg INTEGER OUT
AS
BEGIN
    SELECT @inarg+1 AS v
    SELECT @outarg = @inarg+2 
    RETURN @inarg+3
END;
GO

Yes - they are trivial, but they will illustrate the points just fine.

Code:

<?php
function testMultiReseultSets() {
    try {
    	$con = new PDO('sqlsrv:server=ARNEPC4;database=Test', '', '');
    	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $con->prepare('{ CALL usp_multi (?) }');
        $arg = 123;
        $stmt->bindParam(1, $arg, PDO::PARAM_INT);
        $stmt->execute();
        $rs = $stmt->fetchAll();   
        foreach($rs as $row) {
            echo $row[0] . "\r\n";
        }
        $stmt->nextRowset(); // switch from first to second result set
        $rs = $stmt->fetchAll();
        foreach($rs as $row) {
            echo $row[0] . "\r\n";
        }
    } catch (PDOException $ex) {
    	die($ex->getMessage());
    }
}

function testMultiReturnTypes() {
    try {
        $con = new PDO('sqlsrv:server=ARNEPC4;database=Test', '', '');
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $con->prepare('{ ? = CALL usp_return (?, ?) }');
        $retval = -1;
        $stmt->bindParam(1, $retval, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4); // return value
        $inarg = 123;
        $stmt->bindParam(2, $inarg, PDO::PARAM_INT); // in argument
        $outarg = -1;
        $stmt->bindParam(3, $outarg, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4); // out argument
        $stmt->execute();
        $rs = $stmt->fetchAll();
        foreach($rs as $row) {
            echo $row[0] . "\r\n";
        }
        $stmt->nextRowset(); // necessary before return value and out arg are available for unknown reasons
        echo "return value = $retval\r\n";
        echo "out parameter = $outarg\r\n";
    } catch (PDOException $ex) {
        die($ex->getMessage());
    }
}

testMultiReseultSets();
testMultiReturnTypes();

?>

MySQL more common than MS SQLServer in PHP *and* MySQL also supports stored procedured. But stored procedures are rather uncommon in MySQL unlike MS SQLServer, so that is why the example is using MS SQLServer.

NoSQL:

All of the above are for relational (SQL) databases. For NoSQL databases see:

Article history:

Version Date Description
1.0 August 6th 2016 Initial version
1.1 October 7th 2016 Add content overview
1.2 May 21st 2018 Add section on ORM/Doctrine
1.3 August 8th 2018 Add section on calling stored procedures
1.4 April 5th 2020 Add section on pgsql extension
1.5 June 16th 2022 Add section on Eloquent

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj