Using PHP PDO to manage data in MySQL

Ilitumwa mnamo - Iliwekwa upya mnamo

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.

class EmployeesRepository {
	private $db;
	public function __construct($host, $database, $user, $password) {
		try {
			$this->db = new PDO("mysql:host=".$host.";dbname=".$database,$user,$password);
			$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		} 
		catch(PDOException $pdoEx) {
			echo $pdoEx->getMessage();
			die();
		}
		catch(Exception $ex) {
			echo $ex->getMessage();
			die();
		}
	}
}

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.

public function getEmployeesOlderThan($age) {
	$prepStatement = $this->db->prepare(
 	"select * from employees e where ((DATEDIFF(NOW(), e.birth_date))/365) > :age"
	);
	$prepStatement->bindParam(":age", $age);

	$result = $prepStatement->execute();

	if($result) {
		return $prepStatement;
	}

	//in case the query was unsuccessful, return empty result
	return array();
}

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:

    $age = 63; //this can come from input or session
    
    try{
      $result = $repo->getEmployeesOlderThan($age);
    }
    catch(PDOException $pdoEx){
      echo $repo->formatErrorMessage($pdoEx->getMessage());
      die();
    }
    echo "<h2>Employees older than ".$age.":</h2>" ;

    while ($item = $result->fetch()) {
      echo "<p>[".$item['emp_no']."] ".$item['first_name']." ".$item['last_name']."</p>";
    }

Invoking Stored Procedures

Stored procedures can be invoked using prepared statements too. I created this stored procedure, which returns the employees who have their salary in a given range.

DROP PROCEDURE IF EXISTS SP_GET_EMPLOYEES_WITH_SALARIES_BETWEEN;

DELIMITER //

CREATE PROCEDURE SP_GET_EMPLOYEES_WITH_SALARIES_BETWEEN 
(IN p_start INT, 
 IN  p_end INT)
BEGIN
	SELECT e.first_name, e.last_name, max(s.salary) as salary
    FROM `employees` e, `salaries`s
	WHERE s.salary > p_start AND s.salary < p_end and s.emp_no=e.emp_no
	GROUP BY e.first_name, e.last_name;

END //
DELIMITER ;

Parameters can be passed to the stored procedure using “?”:

 public function getEmployeesWithSalariesBetween($min_salary, $max_salary) {
	$prepStatement = $this->db->prepare(
		"CALL SP_GET_EMPLOYEES_WITH_SALARIES_BETWEEN(?,?)"
	);
	$prepStatement->bindParam(1, $min_salary);
	$prepStatement->bindParam(2, $max_salary);

	$result = $prepStatement->execute();

	if($result) {
		return $prepStatement;
	}

	//in case the query was unsuccessful, return empty result
	return array();
}

In this case, binding of parameters starts from index one. The processing of the result is done the same way as of a normal query.

    $min = 149000; //this can come from input or session
    $max = 156000;

    try{
      $result = $repo->getEmployeesWithSalariesBetween($min, $max);
    }
    catch(PDOException $pdoEx){
      echo $repo->formatErrorMessage($pdoEx->getMessage());
      die();
    }
    echo "<h2>Employees with salary between:$".$min." and $".$max.":</h2>" ;

    while ($item = $result->fetch()) {
      echo "<p>[$".$item['salary']."] ".$item['first_name']." ".$item['last_name']."</p>";
    }

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.

The code samples, as usual, can be found on GitHub.

Ametuma 3 Februari, 2015

Greg Bogdan

Software Engineer, Blogger, Tech Enthusiast

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

Nakala Mpya

Get more jobs, with ZERO project fees