Connecting to MySQL Databases using PDO in PHP

To connect to MySQL databases in PHP, the age old mysql_connect API is no longer used. It has been deprecated and will not be supported in future PHP versions. So, there are recommendations everywhere for us to use either PDO (PHP Data Object) or mysqli_connect.

Using PDO (PHP Data Objects)

PDO supports multiple database types. You can connect to different databases like MySQL and SQLite based on the database driver available in your system. Switching between database types is also easy. Security is also high.

Checking for the Available Drivers

You can check for the supported databases by finding out the drivers that are installed. Just run the following code:

Based on the drivers you have, you will get an output like:

For Connecting to the Database

Querying the database

To query the database using PDO, we need to add the query line after the connection has been made. Here’s how a simple database query looks like

Databases in PDO are usually queried by using the prepare statement followed by execute. The data can be user supplied and is safe from SQL injection when queries are prepared as opposed to when it is directly executed.

Example:

Using the Output of the Query

After the query has been made, we can use the output from the statements to extract certain data. To use such data, we use the fetch statement which is usually used with a while or foreachloop.

Example:

Different Ways of Fetching

fetch(PDO::FETCH_NUM) – Fetching the results as a numeric array.
fetch(PDO::FETCH_ASSOC) – Fetching the results as an associative array.
fetch(PDO::FETCH_BOTH) – The default option, fetches both numberic as well as associative array.
fetch(PDO::FETCH_OBJ) – Fetches the results as an anonymous object.
fetch(PDO::FETCH_CLASS) – Fetches the results as a class.

There are more ways to fetch the results in the official PHP documentation.

Checking Whether the Row Results Exist

You can also add in an additional line before accessing the results of the query. This line will check or count whether there are rows that result after a query.

Example:

Closing PDO

At the end of all the queries, you should close it. Just assign NULL to the connection and you’re done.

Example:

So, a full PDO connection, query, fetching output and finally closing it looks like:

More Examples

The database name is myDatabase and the table name is table for convenience. Here’s the diagrammatic represenations of the MySQL table and its values.

A normal PDO Query

Using PDO::FETCH_NUM

Using PDO::FETCH_ASSOC

Using PDO::FETCH_OBJ

Using PDO::FETCH_CLASS

This is a special method of fetching with endless possibilities. When you fetch as a class, you have to create a class to go in the query. This allows for maximum code re-usability.