How to use PDO-MySQL with PHP

PDO is a PHP extension that allows multiple PHP's database connections by creating a single cohesive interface. It allows code portability and compatibility across various platforms. It can be considered as a database access layer providing a uniform method of access to multiple databases. By using PDO you can switch between different databases and platforms just by modifying the connection string.

One of the big benefits of PDO is that the interface is consistent across multiple databases. Functions for prepared statements  take some of the hassle out of escaping all your query strings. PDO also has greater portability  than mysql_connect.

PDO also allows to rollback transactions that have not been committed implicitly or in case the autocommit option is turned off. In case you are implementing a DDL statement, you must turn the auto commit off to implement rollback.

PDO extension supports multiple databases including:

• PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )

• PDO_FIREBIRD ( Firebird/Interbase 6 )

• PDO_IBM ( IBM DB2 )

• PDO_INFORMIX ( IBM Informix Dynamic Server )

• PDO_MYSQL ( MySQL 3.x/4.x/5.x )

• PDO_OCI ( Oracle Call Interface )

• PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )

• PDO_PGSQL ( PostgreSQL )

• PDO_SQLITE ( SQLite 3 and SQLite 2 )

• PDO_4D ( 4D )

To see if the PDO driver is available for your database, check phpinfo() and you should have a section named PDO and another pdo_mysql or pdo_sqlite depending on your choice of database. You may also check the available drivers with the static method PDO::getAvailableDrivers().

<?php
foreach(PDO::getAvailableDrivers() as $driver)
    {
    echo $driver.'<br />';
    }
?>

To enable PDO simply configure –enable-pdo and –with-pdo_sqlite –with_pdo_mysql or whatever database needs supporting by PDO.

After connecting to your database of choice, much of the PDO methods are similar. This is why PDO is such a powerful and useful tool for PHP. Here is how you connect with MySql using PDO.

<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

After establishing connection to the database, now let us discuss how to implement queries using PDO, we will be discussing only few common ones here.

$dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';
    /*** INSERT data ***/
    $count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'troy')");
    /*** echo the number of affected rows ***/
    echo $count;
    /*** close the database connection ***/
    $dbh = null;

And here is a simple select statement:

$dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';
    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM animals";
    foreach ($dbh->query($sql) as $row)
        {
        print $row['animal_type'] .' - '. $row['animal_name'] . '<br />';
        }
    /*** close the database connection ***/
    $dbh = null;

To update a table or field in your database, here is what you would need to do :

$dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';
    /*** INSERT data ***/
    $count = $dbh->exec("UPDATE animals SET animal_name='bruce' WHERE animal_name='troy'");
    /*** echo the number of affected rows ***/
    echo $count;
    /*** close the database connection ***/
    $dbh = null;

And here is the delete query:

<?php
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
?>