November Black Friday C# week
Black friday is here! Get up to 80 % extra points for free! More info
Only this week up to 80 % off on C# courses. 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.


 

Download

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

 

 

Article has been written for you by David Capka
Avatar
Do you like this article?
1 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 College The author learned IT at the Unicorn College - a prestigious college providing education on IT and economics.
Previous article
Creating the database and PHP SQL drivers
All articles in this section
Simple object-oriented CMS in PHP (MVC)
Thumbnail
Next article
Listing articles from the database in PHP (MVC)
Activities (7)

 

 

Comments
Display older comments (3)

Avatar
David Capka
ICT.social team
Avatar
David Capka:9. September 14:47

Hi yoel, it looks like either your user name or your password is wrong. Do you use the same credentials you use for your database admin tool?

Reply 9. September 14:47
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
yoel
Member
Avatar
Replies to David Capka
yoel:9. September 14:50

Yes that was my first impression too, I checked the user/pass and it's correct, I use that credentials for login in the mysql console directly (just in the linux shell, sudo mysql -u root -p)

 
Reply 9. September 14:50
Avatar
David Capka
ICT.social team
Avatar
Replies to yoel
David Capka:9. September 15:58

Maybe you can try creating another MySQL user?

Reply 9. September 15:58
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
yoel
Member
Avatar
Replies to David Capka
yoel:10. September 14:31

Sure, I created another user and I granted him privileges, and we are 1 step forward. The logic now it's working because when I try to go to the root it redirects me through the routercontroller. But actually I'm getting an 404 error when I am trying to reach any page ( in the attached image for example I am trying to show the 'portada' article )

 
Reply 10. September 14:31
Avatar
David Capka
ICT.social team
Avatar
Replies to yoel
David Capka:11. September 9:49

And are you using the project for this lesson or the final one? Only the contact form and error pages work at this point. It also has to be in the root folder of your webserver.

Reply 11. September 9:49
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
yoel
Member
Avatar
yoel:11. September 13:43

I writed the code while I was doing the course, now it's finished and I added some updates as it encourages, like the color boxes for "error" and "sucess" messages :)
This version is working fine in my XAMPP but not in the linux with the apache2 + mariadb + php stack.
So answering to your question, yes it's the final one, ¿do you imagine what may be happening?
Thanks a lot for your support

Edited 11. September 13:43
 
Reply 11. September 13:43
Avatar
David Capka
ICT.social team
Avatar
Replies to yoel
David Capka:11. September 16:06

Maybe you don't have .htaccess files enabled, I believe it's not configured by default and it has to be done manually. Please, check this link - https://phoenixnap.com/…ccess-apache

Reply 11. September 16:06
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
yoel
Member
Avatar
yoel:11. September 16:41

I changed the value of the AllowOverride parameter to All in the apache.conf file and enabled the rewrite module with "sudo a2enmod rewrite", it seems that works well except when I try to comment an article, but I think isn't part of the original lessons. Anyway I'll try to fix it and report the problem for helping other people with similar issues. Thankyou very much!

 
Reply  +1 11. September 16:41
Avatar
David Capka
ICT.social team
Avatar
Replies to yoel
David Capka:12. September 3:46

Great, I'm glad it works! :) The comments are not part of the original course, but you can always create a thread in our forums if you need any help - https://www.ict.social/php/php-forum.

Reply 12. September 3:46
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
yoel
Member
Avatar
Replies to David Capka
yoel:12. September 15:48

Finally all is working correctly, I want to share the throubleshooting for helping other people with similar issue: I adequated the format of the timestamp just declaring a variable with the date php function and created a function for extract the last article id in my commentmanager model, then I add +1 to this var in the commentcontroller for maintain the original logic, I use this value to insert the id value when I create a new comment. I observed that some web servers like xampp fill the id field calculating the value through the auto increment but others like apache2 may not interact like I expect with mariadb and gives you an error if you pass an empty ('') value. This does the trick :D
Thanks for all the support David!

 
Reply  +1 12. September 15:48
To maintain the quality of discussion, we only allow registered members to comment. Sign in. If you're new, Sign up, it's free.

10 messages from 13 displayed. Show all