DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Shahid is an author and co-founder of ssiddique.info. Works at Cerner, Attended Polytechnic University of New York, Lives in Kansas. shahidsidd has posted 1 posts at DZone. You can read more from them at their website. View Full User Profile

Introduction to PDO extension: how to use PDO for different databases

06.14.2014
| 431 views |
  • submit to reddit
/ / MuSQL
$Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); 
/ / PostgreSQL
$Database = new PDO ("pgsql: host = $ host; dbname = $Databasename", $username, $password);
/ / MS SQL
$Database = new PDO ("mssql: host = $ host; dbname = $Databasename", $username, $password); 
/ / SQLite
$Database = new PDO ("sqlite: my / database / path / database.db");
This is a small introduction to PDO extension for beginners Read the full article.
In this topic, we will talk about what a PDO is, why is it necessary and how to work with it. Let's start with the fact that for a long time php-programmers have been discussing the fate of the native functions to work with the database: mysql_connect, mysql_query and others. The fact is that in upcoming versions of PHP data will be erased, these functions will go "Obsolete". Thus php developers should force on other tools to work with the database. Currently, there are two alternatives solutions; mysqli extension and expansion of PDO. In this article we will discuss about PDO  PDO - PHP Data Objects - is a layer that offers a versatile way to work with multiple databases. This layer has several advantages:
  • PDO allows you to work with different databases, such as: MYSQL, Oracle, PostgreSQL and many others.
  • PDO allows you to work with a prepared statement
Now let's talk about all this in more detail. Connecting to a database using PDO. Before you start working with PDO, you must make sure that you have the correct extension - php_pdo_mysql.dll. This can be verified through the function phpinfo(). If everything is okay, you are good to go. First we need to connect to the database: / / MuSQL
$Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); 
/ / PostgreSQL
$Database = new PDO ("pgsql: host = $ host; dbname = $Databasename", $username, $password);
/ / MS SQL
$Database = new PDO ("mssql: host = $ host; dbname = $Databasename", $username, $password); 
/ / SQLite
$Database = new PDO ("sqlite: my / database / path / database.db");
As you can see from the example each database connection string is slightly different from each other You should always use try/catch when you are trying to connect to the unit, so that on exception we do not disclosed any data to the user.
try {    $Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); } catch (PDOException $ e) {     echo "Unable to connect to database"; }
There is no special method to close the connection in PDO, we can close the connection by overriding the variable:
$Database = null;
Queries PDO. Now let's talk about the queries in PDO for the connection we have just created. There are three main methods: Method  $Database-> query This method is used for simple queries, since it is not protected. Returns FALSE if an error occurs.
$Database-> query ("SELECT * FROM site_info");$Database-> query ("DELETE FROM site_info");
Method $Database-> exec This method is used in cases where you do not return any data. The method returns the number of affected records, or FALSE in case of error.
$Res = $Database-> exec ("DELETE FROM site_info");$Database-> prepare
Method is responsible for prepared statements, and it is encouraged to use. / / Anonymous placeholders $Res = $Database-> prepare ("INSERT INTO users (name, phone, city) values ​​(?,?,?)");  / / Registered placeholders
$Res = $Database-> prepare ("INSERT INTO users (name, site, software) values ​​(: name,: site,: software)");
After preparing the query, the query can be executed by 
$Res-> execute ($Data) .
One of the "chips" PDO, as you remember, are just placeholders registered. let's look in more detail as you work with them: Registered placeholders
$Data = array ();$Data ['name'] = 'developer resources';$Data ['site'] = 'ssiddique.info';$Data ['software'] = 'wordpress';$Res = $Database-> prepare ("INSERT INTO site_info(name, site, software) values ​​(: name,: site,: software)");$Res-> execute ($Data);
use of unnamed placeholders are considered good practice, although I personally do not use them at all.
$Data = array ();$Data ['name'] = 'developer resources';$Data ['site'] = 'ssiddique.info';$Data ['software'] = 'wordpress';$Res = $Database-> prepare ("INSERT INTO site_info(name, site, software) values ​​(?,?,?)");$Res-> execute ($Data);
Now lets see how to fetch data  $Res-> fetch - Fetches the next row and returns in the required form. $Res-> fetchAll - returns an array of all selected rows. $Res-> fetchObject - selects the next row and returns it as an object. Method  $Res-> fetch can take additional parameters.
while ($ res = $ res-> fetch ()){    echo $ res-> name;}
To learn more about PDO on the official website of the developers - 

http://us3.php.net//manual/en/book.pdo.php  

This concludes our introduction to PDO. Let me know how do you like it.