For accessing databases in a Web application, PHP provides two interfaces, PDO and MySQLi, to access a database in a web application. As indicated by its name, MySQLi works only with MySQL database.

PDO stands for PHP Data Objects which is a consistent object-oriented interface for accessing 12 different Databases. This means once we know how to program with PDO for accessing one Database type, the same code will work for most other database types too.

PDO API Extension

By default, PDO is enabled in PHP installations except with two extensions that must be installed: PDO and a driver for the database to be accessed.

All the current supporting PDO drivers are available at php.net-PDO Driver

To install MySQL driver, install php-mysql package along with a PHP installation.

Windows system with an xampp installation

The latest xampp (by March 2019) should include PHP 7.0. The extension, pdo_mysql, has been included in the installation.

To find whether PDO extension is enabled or not, open the PHP information page at http://locahost:80/dashboard/phpinfo.php. (You may need to replace 80 with your port number.)

On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have 15 matches for pdo.

The following blocks on the page confirms that two PDO drivers have been pre-installed for MySQL and SQLite and both drivers have been enabled.

png


If the PDO driver is not enabled, open the PHP initialization file, php/php.ini, at the xampp directory. Uncomment (remove the starting semicolon) the line for the Database type. The following shows two PDO drivers are enabled for MySQL and SQLight, respectively.

extension=php_mysqli.dll
;extension=php_oci8_12c.dll  ; Use with Oracle Database 12c Instant Client
;extension=php_openssl.dll
;extension=php_pdo_firebird.dll
extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
extension=php_pdo_sqlite.dll

MacOS with AMPPS

To find whether PDO extension is enabled or not, open the PHP information page at http://localhost/cgi-bin/phpinfo.cgi. On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have over 20 matches for pdo.


(AWS) EC2 instance running Ubuntu with Apache installation

You can install PHP 7.0 and PDO for MySQL by the command:

$ sudo apt-get install -y php7.0 libapache2-mod-php7.0 php7.0-mysql

If you want to install Apache, PHP and MySQL, all three one time, run the commands:

$ sudo apt-get install -y mysql-server mysql-client
$ sudo mysql_secure_installation
$ sudo apt-get install -y php7.0 apache2 libapache2-mod-php7.0 php7.0-mysql

To see what extensions available, run the command sudo apt search php7.0-*.


The following part shows a Web database in MySQL by using PDO.

Creating a MySQL database

In order to show the code for Web databases by using PDO, be sure to have your database service running and a database is ready.

If you need a sample database, follow two posts below to build a MySQL database counselor in a local MySQL server from xampp.

PDO will be used to view the counselor table in the sample database.

Script 1: login.php

1
2
3
4
5
6
7
<?php
define('DB_DRIVER', 'mysql');
define('DB_HOST', "localhost");
define("DB_USER", "dbusername");
define("DB_PASSWORD", "dbpassword");
define("DB_DATABASE", "counselor");
?>

Script 2: mysql_error.php

1
2
3
4
5
6
<?php
function mysql_error()
{
	echo 'We are sorry.<p>Fatal error</p>Please click the back button or contact the administrator at admin@site.com';
}
?>

Script 3: access_database.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<?php
require_once('login.php');
require_once('mysql_error.php');

//$dsn = 'mysql:host=localhost:3306;dbname=counselor';
$dsn = DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE;

try { 
    // 1.Make a PDO object  
    $pdo = new PDO($dsn, DB_USER, DB_PASSWORD);
    if(!$pdo) die('Fatal Error');

    // 2.Compose a SQL query 
    $tablename = 'counselor';
    $sql = "select * from $tablename";

    // 3.Run the query
    $result = $pdo->query($sql);   

    // 4.Parse the query result
    // PDO::FETCH_ASSOC: Return next row as an array indexed by column name
    // 4.1 Get table column names
    $colnames = array_keys($result->fetch(PDO::FETCH_ASSOC));
    // 4.2 Print each column name
    foreach ($colnames as $key => $name) {
        echo "<p>$key: $name</p>";
    }
    // 4.3 Fetch all rows from $result    
    $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    // Print each row array
    foreach ($rows as $key => $row) {
        echo "<p>";
        foreach ($row as $key => $value) {
            echo "<br>";
	     echo htmlspecialchars($key)." : ".htmlspecialchars($value);
        }
        echo '</p><hr>';
    }
	// 5. Close a connection
	$result->close();
	$pdo->close();	
} catch (Exception $ex) {
    echo($ex->getMessage());
    mysql_error();
}

Specify a Data Source Name

Line 6 tspecifies a data source name in the variable $dsn. The resulting dsn string is

	mysql:host=localhost;dbname=counselor

Instantiate a new PDO Object

Line 10 creates a new PDO object for a database connection associated with a particular data source name and database user credential.

<?php
	$pdo = new PDO($dsn, DB_USER, DB_PASSWORD);

Compose a SQL Statement

After a PDO object is available, lines 14 and 15 create a SQL statement in a string variable $sql.

<?php
    $tablename = 'counselor';
    $sql = "select * from $tablename";

Execute the Statement

Line 18 shows the script that runs a SQL statement in $sql and stores the result in $result.

<?php
	$result = $pdo->query($sql);

Fetch the Query Results

Lines 23 through 38 are the script for parsing $result to get the database table column names and print each row in the query result.

Line 23

<?php
$colnames = array_keys($result->fetch(PDO::FETCH_ASSOC));

$result->fetch(PDO::FETCH_ASSOC) fetches the first result row into an array; then extracts the keys of the array into $colnames. The array keys are the database table column names.

Fetch Style:

A call to the fetch method on $result with a PDO constant PDO::FETCH_ASSOC for a fetch style,

$result->fetch(PDO::FETCH_ASSOC)

will fetch the next row into a PHP array, whose elements are indexed by the database table column names. Take the counselor table as an example, the array from fetching the first row in $result is:

Array
(
    [counselor_id] => 1
    [first_name] => Jake
    [nick_name] => The Snake
    [last_name] => Roberts
    [telephone] => 412 565-5656
    [email] => snake@ifpwafcad.com
    [member_since] => 2003-09-10	
)

Refer to php.net for other fetch styles.


At the lines 25 through 27, a foreach statement scans each element in the array $colnames and prints both key and element in an HTML paragraph.

<?php
    foreach ($colnames as $key => $name) {
        echo "<p>$key: $name</p>";
    }

The following scrip, at the lines 28 through 36, will print every row returned from running the query $sql, in an HTML paragraph with each column in one line.

<?php
    // 4.3 Fetch all rows from $result    
    $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    // Print each row array
    foreach ($rows as $key => $row) {
        echo "<p>";
        foreach ($row as $key => $value) {
            echo "<br>";
	     echo htmlspecialchars($key)." : ".htmlspecialchars($value);
        }
        echo '</p><hr>';
    }

Note: Everything is displayed in plain text without styles. Custom style sheets can be added to format the output.

For example, to custom each row with the following look,

png

Add the following embedded style sheets after the PHP block.

<style> 
    p {
        font-family: georgia, serif; 
        font-size: 1.2em;
        padding: 1em;
        margin: 0 auto;
        width: 50%;
        border: 2px solid #ff9900;
        border-radius: 1em;
        background-color: snow;
    }
    p:hover {
        background-color: lightsalmon;
    }
    hr {
        color: #ff9900; 
        height: 1px; 
    }        
</style>

Test the Page

Test the PHP page in a Web server that supports PHP.