{{announcement.body}}
{{announcement.title}}

Introduction to PDO Extension: How to Use PDO for Different Databases

DZone 's Guide to

Introduction to PDO Extension: How to Use PDO for Different Databases

In this article, take a look at PDO and see how to use it for different databases.

· Database Zone ·
Free Resource
In this article, 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 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:
PHP
 




xxxxxxxxxx
1
15


 
1
/ / MuSQL
2
 
          
3
    $Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); 
4
 
          
5
/ / PostgreSQL
6
 
          
7
    $Database = new PDO ("pgsql: host = $ host; dbname = $Databasename", $username, $password);
8
 
          
9
/ / MS SQL
10
 
          
11
    $Database = new PDO ("mssql: host = $ host; dbname = $Databasename", $username, $password); 
12
 
          
13
/ / SQLite
14
 
          
15
    $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.
PHP
 




xxxxxxxxxx
1


 
1
try {
2
    $Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); 
3
} 
4
catch (PDOException $ e) { 
5
    echo "Unable to connect to database"; 
6
}



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.
PHP
 




xxxxxxxxxx
1


 
1
$Database-> query ("SELECT * FROM site_info");
2
$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.
PHP
 




xxxxxxxxxx
1


 
1
$Res = $Database-> exec ("DELETE FROM site_info");
2
$Database-> prepare



Method is responsible for prepared statements, and it is encouraged to use.
PHP
 




xxxxxxxxxx
1


 
1
/ / Anonymous placeholders
2
$Res = $Database-> prepare ("INSERT INTO users (name, phone, city) values (?,?,?)"); 
3
/ / Registered placeholders
4
 
          
5
    $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
PHP
 




xxxxxxxxxx
1


 
1
$Data = array ();
2
$Data ['name'] = 'developer resources';
3
$Data ['site'] = 'ssiddique.info';
4
$Data ['software'] = 'wordpress';
5
$Res = $Database-> prepare ("INSERT INTO site_info(name, site, software) values (: name,: site,: software)");
6
$Res-> execute ($Data);



use of unnamed placeholders are considered good practice, although I personally do not use them at all.
PHP
 




xxxxxxxxxx
1


 
1
$Data = array ();
2
$Data ['name'] = 'developer resources';
3
$Data ['site'] = 'ssiddique.info';
4
$Data ['software'] = 'wordpress';
5
$Res = $Database-> prepare ("INSERT INTO site_info(name, site, software) values (?,?,?)");
6
$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.
PHP
 




xxxxxxxxxx
1


 
1
while ($ res = $ res-> fetch ())
2
{
3
    echo $ res-> name;
4
}



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 you like it.
PHP
 




xxxxxxxxxx
1


 
1
/ / MuSQL
2
$Database = new PDO ("mysql: host = $ host; dbname = $Databasename", $username, $password); 
3
/ / PostgreSQL
4
$Database = new PDO ("pgsql: host = $ host; dbname = $Databasename", $username, $password);
5
/ / MS SQL
6
$Database = new PDO ("mssql: host = $ host; dbname = $Databasename", $username, $password); 
7
/ / SQLite
8
$Database = new PDO ("sqlite: my / database / path / database.db");
9
This is a small introduction to PDO extension for beginners Read the full article.



Topics:
database, database access, pdo, php data objects, tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}