I just read about PHP Data Object(PDO) and found it really interesting and useful, so I decided to share this with everyone.
Introduction to PDO:
To explain it better let me take a hypothetical situation, suppose you are working on a project which is almost complete and then you come to know that the client has switched to PostgreSQL database instead of MySQL for which you have coded. So you are completely screwed, because you need to change almost each and every page of project where ever you have written code to interact with database, if you have used MySQL specific methods to interact with database(which we generally do).
Would not it be great if there were some way to write your code more abstractly, so that when you write a code for fetching data from database ? It would be smart enough to detect what type of database you are trying to connect to? That’s what PHP Data Object(PDO) attempts to do.
PDO is an extension to PHP core library. It provides a data-abstraction layer so that you can use same set of functions to interact with database irrespective of the type of database you are using.
1. You must be using PHP5 or later in order to use PDO.
2. You should already have php_pdo.dll (for windows) present in the php/ext directory, you just need to uncomment the line extension=php_pdo.dll in thephp.ini file, in Unix/Linux based system it should have enabled by default.
3. Next, you need a pdo driver for every database type, which you can find here http://www.php.net/pdo-drivers.
As you need to call mysql_connect method to connect to MySQL database with parameters host, username and password. Once you are connected to database then you need to supply database name to mysql_selet_db method to select a database. Similarly in PDO you need to create a PDO object by supplying DSN (Data Source Name), username and password, it will do both the steps for you.
Example: // Store the DSN information in a variable
$dsn = ‘mysql:host=localhost;dbname=test’ ;
// Creating PDO Object
$pdo = new PDO($dsn, ‘dbuser’, ‘dbPassword’);
// Write a query
$query = “SELECT * FROM users”;
$stmt = $pdo->prepare($query);
// and finally
$stmt->execute(); Now if you want to change the database type you just need to change the DSN information and you are done.
Once you have executed a SQL query the result will be stored in PDOStatement object($stmt in this case). You can get the values using fetch and fetchAll method on PDOStatement object. There are some predefined CONSTANT to assist you in getting the values in different formats.