Friday, August 24, 2007

PHP PDO

PDO :
Sounds like a catchy word, but true its is a very good module that has been available with PHP 5.

In quick terms I would say this is like a generic odbc database abstraction method, IE you could write applications that can work seamlessly with different databases with it.

Here is the explanation from PHP site

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.


How to get it

Its available as PECL and also as a PHP module.

The PHP module is best and performance and speed. For configuring goto PHP site

How do I use it

$db = new PDO('mysql:host=localhost;dbname=$database', $dbuser, $dbpass)
or

class myclass extends pdo{

function __construct(){

parent::construct('mysql:host=localhost;dbname=$database', $dbuser, $dbpass);

}

/***

my code */


}

and now

$db=new myclass();


There are three important things in the PDO we need to understand

PDO object
PDO Statement object
PDO Error handler


PDO object is the object we insantiate

ie $db=new pdo('mysql:host=localhost;dbname=$database', $dbuser, $dbpass);

where $db is the pdo object.

Lets see an example of connecting and updating a record in a table test

$db=new pdo('mysql:host=localhost;dbname=$database', $dbuser, $dbpass);

$db_stmt=$db->prepare("update test set name=:name,address=:address where id=:id");

now $db_stmt is the PDO statement handle.

We had made the sql prepare here this is very effective when you want to update an array of users like

$users=array(
0=>array("name"=>"name1","address"=>"test1","id"=>1),
1=>array("name"=>"name2","address"=>"test2","id"=>2),
2=>array("name"=>"name3","address"=>"test3","id"=>3));

//note you have to

foreach($users as $key=>$user){
foreach($user as $col=>$val){
//bind the value for :name , :address ,:id here

$db_stmt->bindValue(":".$col,$val);
//to my knowledge the bindValue will work for binding all values.
//I see this bindParam is not working for the values in where class
//refer php manual for more options
}
//execute it
$db_stmt->execute();
}

There you go you should have updated 3 records.

you can always execute in the old way of

$db->exec("DELETE FROM test WHERE id=1");

which will return the no of affected rows for this.

Please put comments if you have any doubts.