What is PDO & why should I use it?

Question

People keep on mentioning that I should be using PDO in my PHP when dealing with MySQL, I have never heard of this before.

What is PDO? How is it used and what are the pros and cons?

Thanks,

Answer 1

Consider PDO as a built in class that comes packaged with PHP to make it very easier for you to interact with your database. while developing a PHP Application you need to take care of lots of things like establish a connection, create query, to fetch the result convert resource into an array, escape MySQL Injection using mysql_real_escape_string() now that is a lot of things to be taken care of, least but not the last consider a situation where you want to jump from mysql to mysqli or MSSQL for that you need to go through each and every function and change every line of code to suit the need. PDO eradicate all this problem by providing one centralized class.

To elaborate have a look at below code.

to establish a connection to MySQL Using PDO :

$dbh = new PDO('mysql:host='.HOST.';dbname='.DATABASE,USERNAME,PASSWORD); 

that's it, the connection is established and you could reuse $dbh for performing queries for example to fetch the result from a table user you just need two line of code.

$sth = $dbh->query('SELECT id,name,email FROM users');
$user = $sth->fetch(PDO::FETCH_ASSOC);

Now $user will have all the values fetched as an associative array.

To Insert value into the database you need to do the following.

$sth = $dbh->prepare('INSERT INTO users(name,email) VALUES(:name, :email)');
$sth->bindParam(':name', 'My Name');
$sth->bindParam(':email', 'email@email.com');
$sth->execute();

The above code is using named placeholder, this way PDO will keep you safe from many vulnerabilities as it will keep you away from MySQL Injection. to get you started have a look at this tutorial by netttus, they have explained it very nicely, this article will explain all your dilemmas regarding PDO

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

Answer 2

PDO what is it?

PDO stands for PHP Data Objects. This is an interface that allows PHP scripts to query a database via SQL queries.

PDO is an extension that is added to PHP so that its various functionalities are available in the language. It constitutes an abstraction interface of the database, that is to say that all of its functions can be used to execute SQL queries whatever the DBMS. In other words, if the web application is based on the MySQL DBMS, we can migrate to the PostgreSQL DBMS without modifying the source code (some minor modifications are required).

The abstraction of the database is a strong point compared to the old methods of accessing them. Moreover, it constitutes the ultimate advantage of the PDO, without being the only one.

The proper functioning of PDO depends on the availability of the database driver. This must be supported to be able to query the desired DBMS.

To declare the SBGD MySQL driver for example, go to the php.ini file and add the following line (if it is not already declared): extension = php_pdo_mysql.dll Note that the following line must also appear: extension = php_pdo.dll This step is necessary for PHP versions lower than 5.3. Using the PDO (Connection to a database) object To simplify the explanation, I will use an example:

Suppose that the database server is the local server (localhost) and the database (MySQL) that we want to query is called "mabase". To be able to query this database, we will use the username "user" and the password "1234". Of course, the user "user" has just enough privileges to query the database via PHP scripts.

It is now assumed that in the "mabase" database, a table named "users" has been created, the structure of which is as follows:

CREATE TABLE `utilisateurs` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   `nom` varchar(40) NOT NULL,
   `prenom` varchar(40) NOT NULL,
   `login` varchar(40) NOT NULL,
   `pass` varchar(40) NOT NULL,
   PRIMARY KEY (`id`)
);

I guess you are comfortable with SQL language. Otherwise, just run this code through the PHPMyAdmin tool on your server.

Now that all the settings for our database are there. We will see how to query it using PHP via the PDO object. Database Connection String (PDO Instance) The Connection String is the textual representation of the database connection information installed on a server. Declaring the connection string in this case is like creating a PDO instance like this:

<?php
   $pdo = new PDO("mysql:host=localhost;dbname=mabase","user","1234");
?>

Answer 3

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

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.

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.

Answer 4

PDO is an object oriented class for composing and executing MySQL queries. This may seem like an added layer of complexity, but PDO actually allows you to write queries more simply in your php, and to programmatically write queries (other code constructs the different lines of your query for you).

PDO also takes care of a lot of security issues like escaping your sql queries. You'll never do any of these things if you don't use a database abstraction layer like PDO, and even if you try to, you can easily forget, or do it incorrectly.

If you aren't concerned with security (things like SQL injection) and you are able to write the natural MySQL queries you need, then you don't need to worry about it. Learning it may make things easier in the future when you work on more structured projects that utilize frameworks.