Php PDO connection with MySQL database

PDO: PHP Data Objects – is a database access layer providing a uniform method of access to multiple databases. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive.

Database Support

The extension can support any database that a PDO driver has been written for. At the time of this writing, the following database drivers are available:

  • 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 )

All of these drivers are not necessarily available on your system; here’s a quick way to find out which drivers you have:

 print_r(PDO::getAvailableDrivers());

Connecting

Different databases may have slightly different connection methods. Below, the method to connect to some of the most popular databases are shown.

$host = '127.0.0.1';
$user = 'my_db_username';
$pass = 'my_db_password';
$dbname = 'my_db_name';

try {
  # MS SQL Server and Sybase with PDO_DBLIB
  $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");
  $DBH = new PDO("sybase:host=$host;dbname=$dbname, $user, $pass");
 
  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
 
  # SQLite Database
  $DBH = new PDO("sqlite:my/database/path/database.db");
}
catch(PDOException $e) {
    echo $e->gt;getMessage();
}

Please take note of the try/catch block – you should always wrap your PDO operations in a try/catch, and use the exception mechanism

Close connection

You can close any connection by setting the handle to null.

# close the connection
$DBH = null;

Example

Copy and paste the below code, save in a file and run.

<?php
    $host = '127.0.0.1';
    $user = 'root';
    $pass = '';
    $dbname = 'test';
    $charset = 'utf8mb4';
    try {
        $dbh = new PDO("mysql:host=".$host.";dbname=".$dbname."; charset=".$charset, $user, $pass);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $data = getData($dbh);
        var_dump($data);
    }
    catch( PDOException $pe ) {
        echo $pe->getMessage();
    }

    function getData($dbh) {
       $stmt = $dbh->query("SELECT * FROM table_name");
       return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
?>

Comments

Be the first to comment