Connection to MySQL through PHP with DAO

I will try to explain to you a how DAO is a better approach to connect to MySQL through PHP, making the code readable and secure at the same time.

DAO connection model

  1. Change our connection from mysql_connect() to new mysqli():

    Imagine we have a connection.php file. Typically it looks like this:

    Instead of this we could change it into a connection class and get the credentials form environment variables.

    With this approach, every time we need to connect to MySQL we only need to instantiate the Connection class.

    And that’s it! Yes, we are making our connection simpler, but… How is it avoiding having queries everywhere? I’ll explain that on the next step.

  2. Start using DAO (Data Access Object) Model

    The idea with DAO is to ‘provide some specific data operations without exposing details of the database’ as well as avoiding queries on every script we create. So we’ll create a class per each table and relationship (n to n) we have on our database.

    For example: if we have a table called ‘user‘, we need to create a User class inherited from Connection (to manipulate its data), then we create methods as actions we need to use. In this case, we’ll create a User class, with a ‘get users’ functionality.When we extend a class, we are automatically calling the parent construct method, so we don’t need to instantiate the parent class on our child.

    Now we don’t need to instantiate our connection neither write a query to get all the users from our database. We only need to instantiate our new User class:

    If we need a specific record from the table, we can add a new method to the User class called ‘getById()’:

    And we can print directly the record fields:

    We could even create methods which are specifics for that table, like ‘getActiveUsers’ or any other kind of custom queries.

    Besides that, there are some methods that we will always need on every class: ‘getAll()’, ‘getById()’, ‘update()’, ‘deleteById()’, etc. and writing them on every class is going to be a waste of time and code, even making little changes could be very annoying.

    The solution: create an standard table class with some standard methods, and only create the custom methods with specific queries inside each specific table class. I’ll explain it on the 3rd and last step.

  3. Standardizing methods and customizing table classes

    First, we’ll need our Object class which is going to look exactly like our User class, except that we are going to instantiate our Connection because we are going to need it from our child class.

    Now we change our User class to extends our Object class, we define a construct function to define the table name through the parent constructor. We must add the same methods that we have on Object class to use them with the User class, and finally we add our custom methods:

    Now, we can call all ‘standard methods’ from every table class without defining them everywhere.

With DAO, now we can manipulate the database inside our code without typing queries everywhere, it’s more secure because we are not telling anyone about the exact architecture, and we avoid redundancy.

Posted in:

Leave a Reply

Your email address will not be published. Required fields are marked *