Using PHP PDO to manage data in MySQL

Many projects on Freelancer.com are about developing Web sites that use PHP/HTML and MySQL, so in this article I present a way to easily access and modify data stored in MySQL using PHP. This can be easily used and adapted by freelancers in their own projects. For demonstration purposes, I use the Employees sample database provided by MySQL. For accessing data, I use the PHP Data Objects (PDO) library. This is like the JDBC driver in Java -- it provides an abstract interface above all the database engine.

Creating Connections to the database

Creating database connections using PDO is simple and it supports OOP like error handling.

Using the PDO class, and passing in hostname, database name, user and password I create a connection to the database. In case there are errors a PDOException is raised and the getMessage() method returns the cause of Exception. PDO supports multiple ways to handle errors, I set the error mode to ERRMODE_EXCEPTION which will raise an exception when an error occurs during database access or query execution.

Queries and Prepared Statements

Executing queries can be done using the query() method. This can be used in multiple ways. I think the most useful way is when the method creates instances of a class (which can be mapped to a table row). Using this feature you can write a very basic ORM.

public function getAllDepartments() {
$resultSet = $this->db->query("select * from departments");
#the PDO::FETCH_CLASS parameter is used to return instances
#of the class which is specified as second parameter
$result = $resultSet->fetchAll(PDO::FETCH_CLASS,"Department");
return $result;
}

In the above method I select all the departments from the database and PDO maps the response to instances of Department class. Please use the fetchAll() method wisely, because it may return huge amount of data (depending on the data, even millions of records from the database) if the SQL query is not specific enough and this can cause performance problems and even cause deadlock situation in the database.

Prepared statements should be used when the SQL query needs to have parameters, which usually are loaded from the user interface or are results of other queries. Using prepared statements helps you avoid SQL Injection attacks. PDO escapes the input parameters and this way attackers cannot hack through your SQL queries.

Parameters inside SQLs are mapped using the :variable_name format, above the :age parameter is bound to the $age parameter of the method. The execute() method returns a boolean value, if the value is true I return the $prepStatement, this can be fetched using a while statement and the result can be displayed on the web page:

Using PDO is comfortable, since you can easily manipulate SQL and handle the response through associative arrays or PHP classes, depending on your style. It supports many database engines (MySQL, SQLite, PostgreSQL, MSSQL, ODBC and DB2) and because it provides an abstract layer above the data store, this can be easily changed.

I am a Software Engineer with over 7 years of experience in different domains(ERP, Financial Products and Alerting Systems). My main expertise is .NET, Java, Python and JavaScript.
I like technical writing and have good experience in creating tutorials and how to technical articles.
I am passionate about technology and I love what I do and I always intend to 100% fulfill the project which I am ...