PDO: Alternative way to access your databases

Introduction

What are PDO?

PDO (PHP Data Objects) is a PHP extension that defines an interface to access a database. 
In some way it can takes the same role as the mysql_connect, mysql_query, ... 
It is included in PHP since version 5.1. For version 5.0, you can get with the PECL extension. 

What are the advantages over the mysql_ functions?

Its main advantage is that it allows an abstraction for data access. 
That is to say that the functions to perform queries and retrieve data are the same, independent of the SQL server used (MySQL, PostgreSQL, ...). For example, if you move from MySQL to PostegreSQL, you do not need to convert all your code as with mysql_ functions (mysql_connect would be replaced by pgsql_connect, mysql_query by pgsql_query ...). 

But as each SQL Server is not running SQL queries in the same way, it is possible that you have to rewrite some queries. 

PDO is also object oriented, so you can expand and handle errors with exceptions. 

Connect to your SQL server

• You can modify the folowing:
  • "Mysql" is the name of the driver used. If you use PostegreSQL, it will pgsql.
  • Localhost is the SQL host. This is the first argument you give in mysql_connect
  • "your_database" is the name of your database. This is what you put into mysql_select_db.
  • "User": username
  • "Password": password

Run a query that doesn't return any value

A query that returns no information. For example, queries that remove, alter or insert data. Queries that return data are those that start with "SELECT ...". 

For this type of request, it is preferable to use the exec () method which returns the number of rows affected. 

Consider this example: 


In this example, we delete all accounts of type "member" (it retains the "admin" type only). As you can see, the $nb variable contains the number of rows affected (in this case, removed) via our query. 

Retrieve Data 


To retrieve data in a database, we will use the query() method of the PDO class. This method returns an object of type "PDOStatement". 

The PDOStatement proposes a method to retrieve the results row by row: fetch(). 

You can select the format in which you want to recover the data: a table (default), an object ... To avoid always indicate how we want to retrieve data calling the fetch () method, it can indicate once and for all with setFetchMode (). 

To protect our applications against SQL injections, we will use the quote () method of the PDO class. She will take care of adding quotes around strings, and avoid injections by doubling the quotes within the string. 

To summarize all this, I suggest you look at this example: 

Analyzing this script:

Line 2: We include the script 'sqlconnect.php "which takes care to connect to MySQL. 

Line 4: We store in the variable $sql a SQL query that will retrieve all fields of all entries in the table "members". 

Line 5: We execute the SQL statement and store the result (a PDOStatement) into the variable $req 

Line 6: We perform a loop for each entry. 

Line 7: This is simply an echo which is a link to the profile page of each member. 

Line 9: We close the cursor because we no longer need this variable.