Get up to 80 % extra points for free! More info:

Lesson 7 - Database wrapper

In the previous lesson, Creating the database and PHP SQL drivers, we set up a MySQL database and discussed the different approaches that can be used when working with databases in PHP. In today's tutorial, we'll program a simple wrapper that we'll use over PDO.

Wrapper above PDO

We'll make a CRUD wrapper to communicate with the database and integrate said communication into our object-oriented code. A wrapper generally wraps something more complex. We define our interface over said wrapper, to make it simpler to work with. We'll build the wrapper over the PDO driver, which is provided directly by PHP and is the best driver available. It also allows us to switch to other database types, turn map entities into objects, and so on. PDO itself is actually a wrapper, so we're essentially wrapping a wrapper which is not wrong in any way. In fact, we simplify our application's code quite a lot this way.

Model

The wrapper is logic, so we'll have to make it a model. Let's create a Db class in the "models" folder. The name is short and simple because we're going to use it a lot.

class Db
{

}

Sharing the connection instance

We want the database connection to be stored as soon as it's established, so we'll need the class to be available from everywhere in our application. There are a lot of design patterns to achieve that. Ideally, the Dependency Injection, simpler Service Locator or Singleton (which is more of an anti-pattern). Since these are advanced techniques, we'll simply use static members instead. We'll make all of the methods and properties in the class static, which actually makes sense since it's a utility class.

We'll start by adding a static $connection property:

private static $connection;

Connecting

Next, we'll create a PDO instance in order to establish the database connection. The instance takes the connection settings as a parameter. The connection settings are an associative array where we use PDO class constants as keys. Let's declare said array in our class and set error handling and an initialization command. We'll make errors throw exceptions. The initialization command will be "SET NAMES utf8", which all of you should know well by now. It sets the encoding so that international characters are displayed properly (even if your country doesn't use them, what if a guy with a foreign name registers on your page?). Unless you're using a really old database, add the PDO::ATTR_EMU­LATE_PREPARES flag, which leaves passing query parameters to the database (which is safer and more reliable). The static $settings class property will look like this:

private static $settings = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
    PDO::ATTR_EMULATE_PREPARES => false,
);

Now we have everything ready to add a method that connects to the database:

public static function connect($host, $user, $password, $database)
{
    if (!isset(self::$connection))
    {
        self::$connection = @new PDO(
            "mysql:host=$host;dbname=$database",
            $user,
            $password,
            self::$settings
        );
    }
}

The method simply creates a PDO instance using the usual parameters for establishing database connections (host, username, password and database name) and stored said instance in the static $connection property. We could also return the instance if we wanted to. Notice how we make sure it checks whether the connection has already been established so the method doesn't try to re-connect.

Executing a query

Now we need a method to execute a database query. PDO perfectly supports so-called prepared statements, which is a way of adding (or excluding) variables in a query. With older drivers we had to insert variables directly into a query string, which I'm you have already seen before:

// This code is dangerous
mysql_query('SELECT * FROM `user` WHERE `name` = "' . $name . '"');

That kind of query is potentially dangerous since variable contents may come from a user and could have malicious code in it. The old way of dealing that sort of thing was the following:

mysql_query('SELECT * FROM `user` WHERE `name` = "' . mysql_real_escape_string($name) . '"');

The function with the awkward name - mysql_real_es­cape_string() secured the variable by "escaping" potentially dangerous characters. However, this works only for strings and you had to secure numbers in another way.

Prepared statements solve this problem in a different way, it does it by adding a placeholder character "?" instead. Afterward, it's executed by the database along with an array of parameters that are inserted beside the question mark placeholders. The query is completely separated from the user variables, so it's safe and considered "prepared".

Let's add a method for retrieving a row from the database:

public static function queryOne($query, $params = array())
{
    $result = self::$connection->prepare($query);
    $result->execute($params);
    return $result->fetch();
}

We call the prepare() method on the instance to which we pass a query string including the placeholders. Then, we'll call the execute() method, which passes the parameter array to the query and executes it on the database. Last of all, we get and return the first row of the result using the fetch() method.

Querying multiple rows

The queryOne() method returns a single row, and we'll most likely need to query multiple rows at some point. Therefore, we'll add a queryAll() method, which will return an array of rows that match a given query. Normally, we would use this sort of method when displaying article comments.

public static function queryAll($query, $params = array())
{
    $result = self::$connection->prepare($query);
    $result->execute($params);
    return $result->fetchAll();
}

Querying a single value

We'll also need to return a single column, e.g. in queries like SELECT COUNT(*)... For this reason, we'll add a querySingle() method which will always return the first value of the first row found:

public static function querySingle($query, $params = array())
{
    $result = self::queryOne($query, $params);
    if (!$result)
        return false;
    return $result[0];
}

The method is very similar to the queryOne() method, but it's shorter which comes in handy when used frequently. Besides, this is the main purpose of using wrappers, to make our work easier.

Querying the number of affected rows

We'll also need a method that returns the number of affected rows, especially, when we need to add, edit or delete rows. It'll look like the following:

// Executes a query and returns the number of affected rows
public static function query($query, $params = array())
{
    $result = self::$connection->prepare($query);
    $result->execute($params);
    return $result->rowCount();
}

Our wrapper is done. In the next lesson, Listing articles from the database in PHP (MVC), we'll add an ArticleController, and we'll teach our application to display articles from the database, and list said articles. The project in its current state is available for download below, as always.


 

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.

Download

By downloading the following file, you agree to the license terms

Downloaded 221x (13.44 kB)
Application includes source codes in language PHP

 

Previous article
Creating the database and PHP SQL drivers
All articles in this section
Simple Object-Oriented CMS in PHP (MVC)
Skip article
(not recommended)
Listing articles from the database in PHP (MVC)
Article has been written for you by David Capka Hartinger
Avatar
User rating:
4 votes
The author is a programmer, who likes web technologies and being the lead/chief article writer at ICT.social. He shares his knowledge with the community and is always looking to improve. He believes that anyone can do what they set their mind to.
Unicorn university David learned IT at the Unicorn University - a prestigious college providing education on IT and economics.
Activities