PHP Data Object (PDO) Database Connection Script

PDO Connection Script

We have new way to connect and use Database with PHP 5 or new versions.

Here I am providing a simple PHP script to connect and use Database with PDO connection.

You might think why to use PDO?

The answer is here we can use multiple databases with PDO, so like if we plan to change our database it will be quick and easy whereas with MySQLi we can’t change our database we can only use and stick with MySQL.

You can install PDO from: http://php.net/manual/en/pdo.installation.php

Let’s get started with our script:

`database_config.php:`

<?php
// database Connection variables
define('HOST', 'localhost'); // Database host name eg. localhost
define('USER', 'root'); // Database user. eg. root ( if your on local server)
define('PASSWORD', 'password'); // Database user password  (if password is not set for your root user then keep it empty )
define('DATABASE', 'databasename'); // Database name
define('CHARSET', 'utf8');

function DB()
{
    try {
        static $instance;
        if ($instance === null) {
            $opt = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => FALSE,
            );
            $dsn = 'mysql:host=' . HOST . ';dbname=' . DATABASE . ';charset=' . CHARSET;
            $instance = new PDO($dsn, USER, PASSWORD, $opt);
        }
        return $instance;
    } catch (PDOException $e) {
        return "Error!: " . $e->getMessage();
    }
}
?>

You can always call `DB()` function from the script to get connection object and use throughout the script

Thinking about how to call this function and use, no worries?

Here is script to select/fetch records from database using PDO connection with PHP function:

<?php

require __DIR_ . '/database.php';

class Demo
{
    public function getRecords($user_id)
    {
        try {
            $db = DB();
            $query = $db->prepare("SELECT user_id, name FROM users WHERE user_id=:user_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query->fetch(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }
}
?>

Create Object of `Demo` class and call `getRecords()` function to display records

<?php

$object = new Demo();

$record = $object->getRecords(1);

echo $record->user_id." ";
echo $record->name;

?>

If you have any issue with using PDO connection script, feel free to ask questions using comment box below.