William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Tag Archives: PDO

Comparison of MySQL API options for PHP

Comparison of MySQL API options for PHP

The comparison table is from php.net, put here for easy retrieving:

  PHP’s mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP’s MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No

Summary: consider to use mysqli Extension first, then PDO, then MySQL native Extension.

PDO, PDO-MySQL

I used PDO for somewhere, right now extract its document for a short summary:

1. What is PDO?

PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.

Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.

While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn’t allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL’s support for Multiple Statements. PDO is implemented using the PHP extension framework, its source code is located in the directory ext/pdo.

2. What is the PDO MYSQL driver?

The PDO MYSQL driver is not an API as such, at least from the PHP programmer’s perspective. In fact the PDO MYSQL driver sits in the layer below PDO itself and provides MySQL-specific functionality. The programmer still calls the PDO API, but PDO uses the PDO MYSQL driver to carry out communication with the MySQL server.

The PDO MYSQL driver is one of several available PDO drivers. Other PDO drivers available include those for the Firebird and PostgreSQL database servers.

The PDO MYSQL driver is implemented using the PHP extension framework. Its source code is located in the directory ext/pdo_mysql. The following is an example of prepared statements.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

PHP PDO

PDO is PHP Data Objects. It is an option for database access, besides MySQLi, mdb2.

1. How to activate PDO

PDO is installed with PHP by default; by edit PHP config file to activate it.
In my CentOS server env, the config file is at /etc/php.d/pdo.ini.

// in /etc/php.d/pdo.ini:
// Enable pdo extension module
extension=pdo.so
...
// restart web server, and PDO can be found by:
$ php -r 'phpinfo();' |grep -i pdo
//enabled: mysql,odbc,pgsql,sqlite

2. injection security

A benefit to use PDO is anti-injection. It has prepared statements and placeholder to escape the input strings, like this:


$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
//using bindParam()
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
$stmt->execute();
//or:
$stmt->execute(array($name, $value));

This is very cool, useful for registry, login checking, etc in which security is more important.

3. Singleton

DB access needs Singleton feature. A singleton PDO wrapper example:


class DB {    
 private static $instance=null; 
 private function __construct() {}
 public static function getInstance() { 
  if(!self::$instance){ 
  	// use constant variables.
	self::$instance = new PDO(DB_DSN, DB_USER, DB_PASS); 
	self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
  } 
  return self::$instance; 
 } 

 /* Passes on any static calls to this class onto the singleton PDO instance */ 
 final public static function __callStatic($method,$args) 
 { 		
  $instance = self::getInstance(); 

  return call_user_func_array(array($instance, $method), $args); 	
 }   
}

// Use it:
$stmt = DB :: prepare ( "SELECT 'something' ;" ) ;
$stmt -> execute( ) ;
var_dump ( $stmt->fetchAll ( ) ) ;
$stmt -> closeCursor( ) ;

4. PDO vs.MySQLi

The following is a comparation between PDO and MySQLi:

PDO MySQLi
Database support 12 different drivers MySQL only
API OOP OOP + procedural
Connection Easy Easy
Named parameters Yes No
Object mapping Yes Yes
Prepared statements
(client side)
Yes No
Performance Fast Fast
Stored procedures Yes Yes