Blog

In this tutorial I am going to encapsulate the functionality of the PDO class in a php class of my own. Why am I doing this? Well, to make my life easier when I program, simplify my code and gain speed when doing web development.

Content

• What is encapsulation?
• What is the use of encapsulating the PDO class?
• How to encapsulate PDO in its own class
  • 1.Attributes and constructor
  • 2. Methods of the php class
  • 3 Final, the PDOAdmin class

What is encapsulation?

This concept is enough for a whole post, but as a reminder or introduction I am going to make a brief explanation: in theory, encapsulation is the hiding of complex internal processes to give access to specific and simplified functionalities. The user (in this case us as programmers) of this development will only see basic and essential functionalities available (chosen by us) to do what is proposed.

And in practice? Better let’s see a real example: Imagine an airplane pilot, when he is placed at the controls of the ship he does not know if the plane inside is made with one piece or another, or if the state of the engine is optimal. On the other hand, the pilot has within his reach many buttons and levers on the control panel that, by simply pressing or pulling them, makes the plane take off, accelerate or even activate the air conditioning!

The encapsulation is the concealment of internal processes to give access in a certain use to specific functionalities, the user (in this case we as programmers) of this development will only see basic and essential functionality available to carry out what is proposed.

What is the use of encapsulating the PDO class?

The connection and use of the PDO class (in case I haven’t already said it, I love it!), can be tedious, especially if in our developments we want to keep our database out of the hands of careless and malicious people. Maintaining this sought-after security, in complex queries, often consecutive, or web developments with a considerable number of queries, can be tedious and costly as far as time is concerned, not to mention the possibility of making a mistake when typing one command or another.

By encapsulating the use of the PDO class, we achieve that with a method and a number of parameters appropriate to our needs, we can launch and launch queries on our database using half or less lines of code.

How to encapsulate PDO in its own class

1.Attributes and constructor

Alright, let’s create a php class called PDOAdmin. As class attributes I am going to define a PDO class object to store the PDO class object, and another PDOStatement class object to store the result of the queries that I am going to launch.

Now that I have the class and attributes, I’m going to add its constructor to it. In the constructor I am going to create the PDO object with the connection data entered as input parameters, also, anticipating a future use of the class for connection to different databases, and to make my life easier, I am going to declare the values by default of the connection in the input parameters. In this example it will be a database test, in localhost with user root and pass proofI will leave the array of options from the empty PDO constructor since by default I don’t need any extra configuration:

As you can see, I create the PDO object and store it in the attribute $_pdo of the class. I prevent a possible error of a failed connection with the try and catch and in case of error I print it with the print function and end the php execution with die().

The class code PDOadmin with its constructor it will look like this

2. Methods of the php class

As methods of the class I will need, a priori, three:

1. Method to launch queries
2. Method to recover errors from queries
3. Method to retrieve the id of the last insertion on the database.
4. Method to close the connection to the database.

2.1 The method for launching queries

To launch queries I am going to create a method called the same as the one in the PDOStatement class, execute(). This method execute() You will need a series of input parameters to be able to perform the queries, whether they are SQL INSERT, SQL UPDATE either DELETE SQL and also perform them with the security provided by the bindParam method.

As input parameters I will need at least two: the query ($query) and another configuration parameter ($return_rows) that tells me if it has to return the result rows or not, that is, if I have to return the rows already extracted with fetchAll () or fetch(). These two parameters are the basic and essential ones that I could use in queries without any kind of security, but since I want to be able to use my queries with the security options of PDO, I will need two more parameters, an array with the values ​​of the queries contained in the query ($array_valores), and another array with the types corresponding to each value entered ($array_tipos).

This method execute() of our PDOAdmin class will encapsulate three actions inside:

1. Prepare the consultation received in $query with the method prepare() of the PDO object that we have in the attribute $_pdo and save in attribute $_pdoStat the result object of the PDOStatment class.
2. perform the bindParam (if necessary) of the parameters entered in $array_values ​​with its type $array_types and their position, which will be the same as they occupy inside the array.
3. Return the result if $return_rows is 0 (in the case of deleting or inserting), return the rows resulting from the query if $return_rows is equal to 1 or return a single row if $return_rows worth 2.

2.1.1 Prepare the received query with prepare()

With the following line of code we save, for later use, the PDOStatement object returned by the method prepare() from the PDO object:

$this->_pdoStat = $this->_pdo->prepare($query);

2.1.2 Perform bindParam() on the parameters

In this case the loop foreach() will cycle through the values ​​entered in the $array_values and then assign to a variable ($var_type) the corresponding value type constant of the $array_types. You can see that I have used the string ‘STR’ to mark values ​​of type VARCHAR and although it is not displayed, tell you what I will use ‘INT’ for the integers. This implementation is basic, although it includes the general use of variables in a simple web. For type values DATE or decimals, etc. we could extend the functionality of the method with a comparison of other types.

Finally I do the bindParam() passing the position of the value +1 since the arrays start at 0 and the question marks of the query in 1. I also pass it the value fetched by reference (hence the & of the foreach next to $value) and the constant of the value type in $var_type.

2.1.3 Return rows or result result of PDOStatement

Not much to say about this code, where I collect the result of executing the query in MySQL with execute() in $result and return the rows with fetchAll() or fetch() as long as the input parameter $return_rows is greater than 0 and otherwise $resulteither a resource or false on error.

2.2 Method to recover PDO errors

The method show_error() is a method that I will use to debug and see possible errors in queries, therefore the error is retrieved with the method errorInfo() and I display the information directly with the var_dump() function. Ugly but useful, ahem.

2.3 Retrieve last inserted id method

We often come across queries that insert rows from which we later want to retrieve their identifier, for these cases we can use the method lastInsertId() of the PDO class. The following method encapsulates that functionality using the same name:

2.4 Method disconnect() to close the PDO connection

We must always close the connection to the database, otherwise we will accumulate more and more connections without closing, think that each access to a page will be a new connection. This will be especially problematic in high-traffic web applications.

3 Final, the PDOAdmin class

After all this stretch of text that I hope you have read (at least mostly) and above all that it has been useful to you, I leave you the result class:

<?php
class PDOAdmin{
     private $_pdo;
     private $_pdoStat;
     function __construct($host = 'localhost', $dbname = 'test', $user = 'root', $pass = 'prueba', $options = array() ){
          try {
              $this->_pdo = new PDO('mysql:host='.$host.';dbname='.$dbname, $user, $pass, $options);
          }catch (PDOException $e) {
              print "¡Error!: " . $e->getMessage();
              die();
          }
     }
     function execute($query = '', $return_rows = 0, $array_valores = array(), $array_tipos= array()){
           $this->_pdoStat = $this->_pdo->prepare($query);
           foreach($array_valores as $posicion => &$valor){
                   $tipo_var = 'STR' == $array_tipos[$posicion] ? PDO::PARAM_STR : PDO::PARAM_INT;
                   $this->_pdoStat->bindParam($posicion+1, $valor, $tipo_var);
           }
           $result = $this->_pdoStat->execute();
           if( 0 < $return_rows && $result){
                 return $return_rows == 2 ? $this->_pdoStat->fetch() : $this->_pdoStat->fetchAll();
           }
           return $result;
     }
     function mostrar_error(){
         $array = $this->_pdoStat->errorInfo();
         var_dump($array);
     }
     function lastInsertId(){
         return $this->_pdo->lastInsertId();
     }
        function disconnect () {
             $this->_pdoStat->closeCursor();
             $this->_pdoStat = null;
             $this->_pdo = null;
     }
}
?>