For Web databases, 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 2017) 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

(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.

Building 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.

A Sample PDO Script

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
<?php
define('DB_DRIVER', 'mysql');
define('DB_HOST', "localhost:3306");
define("DB_USER", "dbusername");
define("DB_PASSWORD", "dbpassword");
define("DB_DATABASE", "counselor");

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

try { 
    // Make a PDO object  
    $pdo = new PDO($dsn, DB_USER, DB_PASSWORD);
    // Compose a query in SQL 
    $tablename = 'counselor';
    $sql = "select * from $tablename";
    // Run the query
    $result = $pdo->query($sql);   
    // Parse the query result
	// PDO::FETCH_ASSOC: Return next row as an array indexed by column name
    // Get table column names
    $colnames = array_keys($result->fetch(PDO::FETCH_ASSOC));
    // Print each column name
    foreach ($colnames as $key => $name) {
        echo "<p>$key: $name</p>";
    }
    // Fetch all rows from the query 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>$key: $value";
        }
        echo '</p><hr>';
    }
} catch (Exception $ex) {
    echo($ex->getMessage());
}
?>

Specify a Data Source Name

Line 2 through 9 specifies a data source name in the variable $dsn. The resulting dsn string is

	mysql:host=localhost:3306;dbname=counselor

Instantiate a PDO Object

Line 13 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 has been instantiated, lines 15 and 16 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 22 through 37 are the script for parsing $result to get the table column names and print each row in the query result.

Line 22

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

fetches the first row into an array; then extracts the array keys, which are the table column names, into $colnames.

Fetch Styles:

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 as an array indexed by table column names as returned in the result set $result, in this case, which is alike the following:

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 24 through 26, a foreach statement scans each element in the array $array 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
    // Fetch all rows from the query 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>$key: $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.

To test the PHP page in xampp, you may follow the steps in the post Developing a PHP Application in NetBeans IDE