Translation of the article Why you should be using PHP's PDO for Database Access.

Many PHP developers are used to using the mysql and mysqli extensions for working with databases. But from version 5.1 in PHP there is a more convenient way - PHP Data Objects . This class, abbreviated as PDO, provides methods for working with objects and prepared statements that will significantly increase your productivity!

Introduction to PDO

"PDO - PHP Data Objects is an interlayer that offers a universal way to work with multiple databases."

It leaves the developer to the care of the syntax features of various DBMSs, but makes the process of switching between platforms much less painful. Often this only requires changing the connection string to the database.

This article is written for people who use mysql and mysqli to help them transition to a more powerful and flexible PDO.

DBMS Support

This extension can support any database management system for which there is a PDO driver. At the time of this writing, the following drivers are available:

• PDO_CUBRID (CUBRID)
• 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_SQLSRV (Microsoft SQL Server)
• PDO_4D (4D)

However, not all of them are on your server. See the list of available drivers like this:

print_r(PDO::getAvailableDrivers());

. . , , SQLite.

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

, try/catch – PDO- ( ).

$DBH «database handle» .

null.

#    
$DBH = null;

php.net.

PDO

PDO , try/catch. , PDO :

$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );  
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

, .

PDO::ERRMODE_SILENT

. , , mysql mysqli. DRY .

PDO::ERRMODE_WARNING

Warning . .

PDO::ERRMODE_EXCEPTION

. , . , , :

#      
try {  
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);  
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
  
  # !  DELECT  SELECT!  
  $DBH->prepare('DELECT name FROM people')->execute();  
}  
catch(PDOException $e) {  
    echo ",   .";  
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);  
}

SQL- , . - , - .

Insert Update

. PDO . ( UPDATE, INSERT)

:

# STH  "Statement Handle"  
$STH = $DBH->prepare("INSERT INTO folks ( first_name ) values ( 'Cathy' )");  
$STH->execute();

- exec(), prepared statements. SQL-, .

Prepared Statements

prepared statements SQL-.

Prepared statement — SQL-, . SQL- , placeholder’.

prepared statements.

#  placeholders -  SQL- !  
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)");  
  
#  placeholders  
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); 
 
#  placeholders 
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");

, . placeholder’ , prepared statements.

placeholder’

#    placeholder,    1  3  
$STH->bindParam(1, $name);  
$STH->bindParam(2, $addr);  
$STH->bindParam(3, $city);  
  
#     
$name = "Daniel"  
$addr = "1 Wicked Way";  
$city = "Arlington Heights";  
$STH->execute();  
  
#    ,      
$name = "Steve"  
$addr = "5 Circle Drive";  
$city = "Schaumburg";  
$STH->execute();

. placeholder’ ( 2-4). . , .

SQL- , . :

#  ,    
$data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');  
  
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");  
$STH->execute($data);

$data[0] placeholder’, $data[1] — , .. : , .

placeholder’

#     placeholder’
#     
#     
$STH->bindParam(':name', $name);

, . , , placeholder’.

# ,     
$data = array( 'name' => 'Cathy', 'addr' => '9 Dark and Twisty', 'city' => 'Cardiff' );  
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");  
$STH->execute($data);

placeholder’ , . , , :

#      
class person {  
    public $name;  
    public $addr;  
    public $city;  
  
    function __construct($n,$a,$c) {  
        $this->name = $n;  
        $this->addr = $a;  
        $this->city = $c;  
    }  
    #  ...  
}  
  
$cathy = new person('Cathy','9 Dark and Twisty','Cardiff');  
  
#      
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");  
$STH->execute((array)$cathy);

execute() , .

->fetch(). , . :

• PDO::FETCH_ASSOC:
• PDO::FETCH_BOTH ( ): ,
• PDO::FETCH_BOUND: , ->bindColumn()
• PDO::FETCH_CLASS: . - ,
• PDO::FETCH_INTO:
• PDO::FETCH_LAZY: PDO::FETCH_BOTH PDO::FETCH_OBJ
• PDO::FETCH_NUM:
• PDO::FETCH_OBJ: ,

: FETCH_ASSOC, FETCH_CLASS, FETCH_OBJ. , :

$STH->setFetchMode(PDO::FETCH_ASSOC);

->fetch().

FETCH_ASSOC

. , mysql/mysqli.

#      placeholder’,
#     query()  
$STH = $DBH->query('SELECT name, addr, city from folks');  
  
#   
$STH->setFetchMode(PDO::FETCH_ASSOC);  
  
while($row = $STH->fetch()) {  
    echo $row['name'] . "\n";  
    echo $row['addr'] . "\n";  
    echo $row['city'] . "\n";  
}

while() .

FETCH_OBJ

std .

#  
$STH = $DBH->query('SELECT name, addr, city from folks');  
  
#     
$STH->setFetchMode(PDO::FETCH_OBJ);  
  
#  
while($row = $STH->fetch()) {  
    echo $row->name . "\n";  
    echo $row->addr . "\n";  
    echo $row->city . "\n";  
}

FETCH_CLASS

fetch_class . . , , , ( public).

, .

, .

class secret_person {  
    public $name;  
    public $addr;  
    public $city;  
    public $other_data;  
  
    function __construct($other = '') {  
        $this->addr = preg_replace('/[a-z]/', 'x', $this->addr);  
        $this->other_data = $other;  
    }  
}

x. :

$STH = $DBH->query('SELECT name, addr, city from folks');  
$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person');  
  
while($obj = $STH->fetch()) {  
    echo $obj->addr;  
}

’5 Rosebud’, ’5 Rxxxxxx’.

, , . PDO .

$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'secret_person');

, (PDO::FETCH_PROPS_LATE), , .

, :

$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person', array('stuff'));

:

$i = 0;  
while($rowObj =  $STH->fetch(PDO::FETCH_CLASS, 'secret_person', array($i))) {  
    // - 
    $i++;
}

( ) PDO ( !), .

$DBH->lastInsertId();

->lastInsertId() id . , ( $DBH), ($STH).

$DBH->exec('DELETE FROM folks WHERE 1');  
$DBH->exec("SET time_zone = '-8:00'");

->exec() , , .

$safe = $DBH->quote($unsafe);

->quote() , . , prepared statements.

$rows_affected = $STH->rowCount();

->rowCount() , . , SELECT- PHP 5.1.6. PHP , :

$sql = "SELECT COUNT(*) FROM folks";  
if ($STH = $DBH->query($sql)) {  
    #     
    if ($STH->fetchColumn() > 0) {  
    	  #    ,    !  
    }  
    else {  
        #    ,      
    }  
}

, - mysql mysqli.