How to use freetuts.org DB class

by Nenad Zivkovic

Updated on August 30, 2015


IMPORTANT: version 2 of this class has been made. It is 90% different and 100% better. Untill I have some time to change this tutorial, you can find the class and examples on Github here : https://github.com/nenad-zivkovic/DB/

----------------------------------------------------------------------------------------------------------------------------------------------------

I have prepared a mini application to demonstrate the core features of our DB class. It is just set of files including:

  1. DB class.
  2. 3 models: Author.php, News.php and Info.php - we will use them to represent our database tables, and they are all extending DB class.
  3. 2 views: index.php file that will display the results of our select queries and search method, and process.php that we are using for creating, updating and deleting rows in our tables.
  4. Loader script and our example database.

If you do not know what "model" or "view" are meaning, please read some tutorials about MVC design pattern. For simplicity, in this example application we are not fully using MVC, but you should be familiar with it to better understand what is going on here and how to use DB class properly. In short, Models represent data objects, in our case our models are associated with our database tables. So for example class Author in Author.php file will represent the table "author". Please note the naming convention we are using here. Our models should have the same name as the tables they are representing. We are using Camel Case writing style, where the first word of our model file name and class name should begin with a capital letter, while the first word of our table name should start with lower case. Views contain your presentation, your user interface. They should not contain much of the PHP inside them, only loops and other stuff necessary to output dynamic data.

One important thing that you should know is that inside your model classes you should write protected static property called $table and give it a value of the table name you are using inside your model. So for example inside the Author class you will see declared:

 

protected static $table = 'author';

 

This is how DB class will know which table it should query when you use it's public methods like getAll();

To get started, download the "example" folder that represents our mini app from our Github repository. You will find all files inside. If you want to test this app in your localhost, put the example application inside your web root folder.
Since we are using database manipulation class we need a database, right? Inside the "database_sql" folder you should see "example_db.sql" file containing sql needed to generate tables for our example_db database. You can use phpMyAdmin to import this file. Create new database called "example_db", select it, and then use the "Import" option to import the SQL from this file. After you have successfully created database, you should go to includes folder and open up the DB.php file. Don't worry, you just have to set up your connection credentials, and you can do that starting on line 28 up to 32. That is all, now you are ready to use this example application.
Now if you run the index.php file you should see the results of our select DB queries. Do not worry about this yet, I will go through each of them and explain you how to use everything.

Methods of DB class can be divided into 3 groups:

  1. First group is made from get() methods that are doing all kinds of SELECT queries plus join() and search() methods. For example with getAll() you can get all records from the specified table, or with join() you can join 2 tables using MySQL JOIN's.
  2. Second group is represented by save() method that can do both INSERT and UPDATE using internal create() and update() methods.
  3. And third group are delete() methods that are doing DELETE queries.

Here is the list of all methods provided by DB class. You can click on method names to jump directly to the part of this tutorial that is explaining how to use the chosen method: list


- get()
- showQuery()
- getAll()
- getById()
- getGrouped()
- join()
- getCount()
- count()
- search()
- save()
- delete()
- deleteAll()


get()

 

The first function I will explain you how to use is the get() method of DB class. You can use it to do SELECT query but on much easier way, without need to write all SQL yourself or doing other stuff needed for PDO query to work like preparing and executing, because DB class will do that for you. So lets imagine we have this situation: we want to get and display 5 of the most recent news from our database, and lets say we want to select only title, body and note of the news, because we do not want to get all columns from the news table. We can do this easily with get() method.

Go into the "models" folder and open up the News.php file. Inside the News class we have defined the getNews() method that will be responsible for retrieving the records we need, and we are using get() method of our DB class to retrieve the data from our database. Here is the example code:

 

/**
 * get() example
 */
public static function getNews()
{
    $columns = 'title, body, note';
    $order   = 'id DESC ';
    $limit   = 5;

    return News::get($columns, $order, $limit);
}

 

As you can see, code we wrote is pretty self explanatory. Since all methods of DB class are static, we can call them without instantiating DB class, like News::get(). Note that instead of writing method calls using DB class reference like DB::get() we use the name of our current class News::get(), this is because we are using late static bindings. Just remember that you have to call DB class methods using your model names. The next thing we did is: we assigned our query conditions to variables with meaningful names just for better readability, and then passed them to our get() method like parameters. We just "said" to our get() method what we want to be done.

 

News::get($columns, $order, $limit);

 

With this line of code we "said" to our DB class: select from our table, get us these columns and order the results using this order and we want only 5 of them. We write our queries as we would speak about them, we just write what we need to be done. We say: "Select from news table, get us title, body and note columns, order them by id descending and limit to 5". This is how we use all our methods. The order of parameters passed to our DB methods is always the same and it follows the SQL syntax. First you specify which columns you want to select, then maybe WHERE part of you query if you have it, then ORDER BY, LIMIT, OFFSET. As you can see, order of parameters is same like in real SQL query. And all parameters except the $columns are optional. We need to say which columns we want to select, and everything else is up to us; are we going to use WHERE or not, are we going to use ORDER BY or not... To know exactly which parameters are required and which are optional for every DB method you can visit the documentation of the DB class on Github, or you can see it inside the class itself. Class is very well commented out, so you can check its methods and see inside the comments what is required and what is not. SQL executed with the previous example would be : 

 

"SELECT title, body, note FROM news ORDER BY id DESC LIMIT 5";

 

If, for example, we would have to write the WHERE clause as our condition for selection, it should be written after columns because it is natural for WHERE to appear at that spot, before other clauses. Example:

 

/**
 * get() example
 */
public static function getNews()
{
    $columns = 'title, body, note';
    $where   = ['id BETWEEN' => 4, 'AND' => 7];
    $order   = 'id DESC';
    $limit   = 5;

    return News::get($columns, $where, $order, $limit);
}

 

This would be equivalent to:

 

"SELECT title, body, note FROM news WHERE id BETWEEN :value1 AND :value2 ORDER BY id DESC LIMIT 5";

 

Please note that if you try to write you query something like this: 

 

News::get($limit, $order, $columns);

 

it will not work. You can not write LIMIT or ORDER BY clauses before you even say what you are selecting from your database, that is just invalid SQL. As you can see, it is easy to use DB methods. If you know how to write pure SQL, you will know how to write these abstraction methods. The difference is that you don't have to write all SQL yourself, only what you need to be done. Plus you do not have to write PDO related code that is doing query preparation, execution, parameter bindings and so on, DB class is doing all of that for you.
Important thing to note is how we write the WHERE part of our query. We use an associative array where keys of that array are strings ( or better said constant ) parts of your query, like 'id BETWEEN' and the values are the actual variables ( values ) for that condition that will be bounded by DB class. This way you are getting protected against SQL injection attacks, because DB class will do parameter bindings and escaping for you. Of course, values that we are using in our WHERE clauses may come from the users, like via some form, but in this example, just for simplicity, I am using hard coded values of 4 and 7.

One more great thing about DB class is that all methods that are doing SELECT queries will fetch results using PDO::FETCH_CLASS method. This means that every column returned from database will become a public property inside your model class ( you can declare them as protected or private if you want to ). In this example, our title, body and note will become public properties inside News class, and you can normally use them as if you have declared them yourself; for example $this->title. You will see example of this when I show you how we display our results in our index.php view.

The next great thing about DB class is that you do not have to bind your query conditions to php variables, you can write them directly inside your methods. That way you would have to write even less code. In this example our getNews() method have just one line of code that is dealing with database data retrieval:

 

/**
 * get() example
 */
public static function getNews()
{
    return News::get('title, body, note', 'id DESC', 5);
}

 

All you had to do was to write one single line of code to get what you wanted from your database. Of course writing your queries using meaningful variables is great for readability, and it is easier for everyone else to understand what you are doing. That is how I will do things in this tutorial.

Now lets see how we can display our results. Open up the index.php file, and starting from line 46, you can see example of how we can display data we just got from our query. We are using simple foreach loop to get through all of our results, since they are returned as an array of objects. So first we called just created method "getNews()" from News model and assigned the returned result to the "$result" variable:

 

$result = News::getNews();

 

$result variable will hold the array of returned objects, and we are just looping through them using foreach loop and display the results inside the table cells:
 

<?php foreach ($result as $news) : ?>  

<tr>
    <td><?php echo $news->title ?></td>
    <td><?php echo $news->body ?></td>
    <td><?php echo $news->note ?></td>
</tr>

<?php endforeach; ?>

 

As you can see, you didn't have to do all the boring stuff like: writing all SQL yourself, preparing PDO queries, executing queries and so on. There is no need to use while loops, or even worse, build your html results inside your model classes. That is old, boring and bad way of doing stuff.

 

showQuery()


Before I show you how to use other get() methods, I want to show you one nice convenient helper method: showQuery(). With this method you can see the last executed query by your abstraction methods in plain SQL. Sometimes it is nice to see what is going on behind the scene. Note that you should use this method only while developing you apps, not in production, since that can be a security problem. If you look inside the index.php at the last example, you can see I already used this method on line 61.

 

DB::showQuery();

 

Please note 2 things. First, we are using DB to invoke it, but we could use News too, it just seems more appropriate to use DB since this is just a helper method, and second you do not have to echo it, since this method is using var_dump() to display query.


getAll()


Second method I am going to show you is getAll(). As you previously saw, when I was explaining how to use get() method, we was selecting only title, body and note columns from our news table. But what if we want all columns ? We could specify the $column variable to be '*', and that would select all, but there is a  better, more semantic way of doing this, and that is to use getAll() method. This is specialized method for selecting * (all) from your tables. It is shorter and more appropriate to use in those situations. So lets say we want to select everything from our news table, and order it by id descending, because we want the newest news to be displayed on top of our list. We can do it like this:

 

/**
 * getAll() example.
 */
public static function listNews()
{
    $order  = 'id DESC';

    return News::getAll($order);   
} 

 

That is all you would have to do. You can see the working example in News.php file, and index.php file.

 

getById()


Our next stop is getById() method. It is very common that you want to select some specific row from your table. And the best way of doing that is to select it using its primary key value, usually it is an "id" integer field. You can do this using get() or getAll() method by writing the WHERE part of your query like:
 

$where = ['id =' => $_GET['id'];

 

But there is a better way of writing these kinds of queries, and that is by using getById() method. Lets say we want to select some news with the id of 5 ( this value may come from $_GET or whatever...), and we want to select everything not just some specific columns. Doing this is very simple:

 

/**
 * getById() example
 */
public static function getOneNews()
{
    $id = trim($_GET['id']); // let's pretend we got 5 as an id :)

    return News::getById($id);
}  

 

You can see usage example of this method in News.php file as well as output example in index.php file.
 

getGrouped()


Now, what if you want to group your results using GROUP BY clause ? Well, you can use the specialized function for that: getGrouped(). This method can accept a lot of parameters. Required ones are $columns and $group. Which means that minimum code (SQL) you need in order for this method to work is:

 

$columns = "you select some columns and use aggregate functions";
$group   = "you group them by something";

Model::getGrouped($columns, $group);

 

Here I used some pseudo code. But lets look at some more real world example:

Lets say our news are getting rated by users or news editor, and each news can get rated with numbers from 1 to 5.
What we would like to see is which news author has the highest total rating. This means that we will have to sum the ratings first, but also group them by authors who made them.
But also ( for some reason ) we want to sum only ratings that are at least 2 or higher. We do not want to take in count news that are rated with 1. Also ( for some reason ) we want to see only total ratings higher than 6. Plus we want to make sure that highest total rating is displayed at the top of our list.

We can do this using getGrouped() method:

 

/**
 * getGrouped() example
 */
public static function getGroupedRatings()
{
    $columns = 'SUM(rating) as totalRating, authorId';
    $where   = ['rating >=' => 2];
    $group   = 'authorId';
    $having  = ['totalRating >=' => 7];
    $order   = 'totalRating DESC';
    $limit   = 5;

    return News::getGrouped($columns, $where, $group, $having, $order, $limit);
}

 

When we would display the result of this query, we would see author id and total rating for that author. So this is example on how to use getGrouped() metod, but the output we get from this example is not very much useful, it would be nicer if we can also see the usernames of these authors not just their id's. We can do this by doing one more query, this time in author model. Since authorId is the foreign key in the news table representing the id of the authors in the author table, we can use this to get the author username. We can write a method in Author model to retrieve the usernames based on the id's we provide for that method ( in this case authorId's from news table). This is the method we need to write in our Author model:

 

/**
 * getGrouped() example.
 */
public static function getAuthor($id)
{
    $result = Author::getById('username', $id);

    foreach ($result as $author) 
    {
        return $author->username;
    }
}

 

And this is how we would display the results:

 

<table>

    <tr>
        <th class="title">Author ID</th> 
        <th class="text">Username</th> 
        <th class="note">Total Rating</th>
    </tr>

    <?php foreach ($result as $news) : ?>  

    <tr>
        <td><?php echo $news->authorId ?></td>
        <td><?php echo Author::getAuthor($news->authorId); ?></td>
        <td><?php echo $news->totalRating ?></td>
    </tr>

    <?php endforeach; ?>

</table>

 

You can find this example code in News.php, Author.php and index.php.


join()


Writing this code that will display us usernames based on foreign keys is good introduction for the next method that we can use, and that is join(). More often, when you want to use data from two tables that are connected you will use SQL JOIN's. Now I will explain you how we can use our join() method.

Lets say we want to display our news along with their authors. News title and text are stored in the news table, and usernames of the authors are stored in the author table, but since news table contains foreign key "authorId", referencing the primary key "id" in author table, we can use JOIN's to get what we need. Here is the method inside our Author model that can achieve what we want:
 

/**
 * join() example
 */
public static function joinTables()
{
    $table   = 'news, LEFT';
    $columns = 'author.username, news.title, news.body';
    $on      = 'author.id = news.authorId';

    return Author::join($table, $columns, $on);   
}

 

As you can see, we first specify the table we are going to join with our current one (author), and we specify the type of join to be LEFT because we want to see even users that haven't made any news yet. If you want to use INNER JOIN you do not need to write it as the second part of the $table string, since that is the default way of joining tables. Next we just say which columns to select and what is the ON keyword ( fields that connects our tables ). We would output results as any other we did so far.

So far, so good. But you may be wandering how we can alter data that comes from our database before we display it to users. Maybe we want to process it on some way before outputting. We can do that easily. Lets say we want to alter titles coming from the database. To do that, we can do it directly in the __construct method of our model, but that can create a mess if we have a lot of things to do, so better way is to write separate function to do all the processing and then just invoke it in __construct method. You can see the full working example of all of this if you see Author.php file ( example for altering title is merged with join() example ) , and output is presented in index.php.


getCount()


The next two methods I will show you are methods made for counting. One is just helper method that will help you get the number of all rows affected by your select statements when you use LIMIT clause, and second is wrapper of COUNT() sql function. Lets start with the second one.

Both functions are very simple to use. Lets say you want to count the number of authors from the news table. You don't care if someone made more that 1 news, and in fact, you do not want to count that author twice, you want to count only DISTINCT authors. You can count distinct authorId fields using getCount() method.

 

/**
 * getCount() example
 */
public static function getCountedAuthors()
{
    $columns = 'DISTINCT(authorId)';

    return News::getCount($columns);
}

 

This was pretty darn simple. You can see this example in action in News.php model, and output in index.php starting from line 202.


count()


Next function is a life-saver when you need to use pagination. When paginating, you need to know the total number of selected rows by your select methods, even if you use LIMIT. Simple rowCount() PDO function do not work in this case, because it will return only the number of selected rows after LIMIT is applied. So for example if your LIMIT is 5, but you selected 20 rows and you just need them in bulks of 5, rowCount() will tell you that you have selected 5 rows, not 20, This is not good... But luckily with count() method you can get what you need. It will return you the real number of selected rows.

Usage is very simple. When you need the number of affected rows by your get() or join() or search() methods when you are using LIMIT clause, you just invoke count() function like this:

 

DB::count();

 

You can see this function in action in index.php file on lines 103-104, and few more.


search()


One more imbazor function that DB class is providing to us is search(). We can use it to easily do full text ( LIKE ) searches of our database tables. The usage is very simple. You just tell it what and where you want to search. Let's see an example. Let's say that we want to see if any of our news have "15 16" inside their title or body. Here is the code:

 

/**
 * search() example
 */
public static function searchNews()
{
    $columns = '*';
    $where   = ['title' => '15 16', 'OR body' => '15 16'];
    $order   = 'id';
    $limit   = 10;

    return News::search($columns, $where, $order, $limit);
}

 

For columns we specify * (all), $where is our condition - what we want to find and where. Output process is same like for every other example. You can see this code in action in News.php and index.php files.


save()


We are done with methods that are selecting data from our database. Let's see how we can create new database records, update them, and delete them.

Function that is capable of both creating and updating records is save() method. The first thing that you may be wandering is: "how this method know when to create and when to update ?" The catch is, when you are updating some record, you usually need to specify the condition for updating. Like: I want to update the news where id is equal to 3. When you are creating you do not have any conditions, you just create new stuff. Of course, there are cases where you need to update everything in your table with some new data, so you do not need to write conditions, but they are very rare, and they can be solved with writing a condition like: update where id is greater than 0. That means that everything will get updated. Enough talk, let's get to the examples.

For working with save() and delete() methods, we are going to use process.php script instead of index.php, and Info.php model. This model is representing info table where we are going to store and update new records, and delete some of them ( or maybe all ).

Let's say we want to create a new record in our info table using some default text assigned to our class properties ( inside Info model ). To do this, we may use the code like this:

 

/**
 * Save() example ( INSERT )
 */
public function actionCreate()
{
    $values = ['title' => $this->title, 
               'body'  => $this->body, 
               'extra' => $this->extra];

    return Info::save($values);
}

 

What we just did here is that we have used an associative array to specify the value of our insert statement. Keys of our array are the columns we are inserting into, and values are actual values that we will inserted, and we just passed that to our save() method. Now we only have to invoke our actionCreate() method of our Info class, and data will be inserted into database. To do that, open up the process.php file, and from lines 29 to 40 uncomment the code we have there. You should see this code:

 

$info = new Info();

if ($info->actionCreate())
{
    echo "Insertion was successful";
}
else
{
    echo "Failure";
}

DB::showQuery();

 

So we have just instantiated our Info class, and invoked it's actionCreate() method using $info object. We will also display some messages after method invocation, just to have some feedback. Now just start the process.php file by visiting it in the URL like: http://localhost/example/process.php . You should see the message "Insertion was successful". If you visit your database, you can see that new record with the id of 6 has been created. Now, comment out the creation example code in process.php, since we will try to update this record using save() method again.

Now lets update the title of the info record with the id of 6. To do that we can write the next few lines of code:

 

/**
 * Save() example ( UPDATE )
 */
public function actionUpdate()
{
    $values    = ['title' => 'Updated title'];
    $condition = ['id =' => 6];

    return Info::save($values, $condition);
}

 

First we said which column we want to update ( title ) and we specified the new value for that column ( "Updated title" ). Next, we just said for what row in our table we want to do this update, and that is the row with an id of 6. Now, inside the process.php file, uncomment the code from the line 47 until 58, and visit the http://localhost/example/process.php URL again. You should see  message that update was successful. If you look in your database again, you will see that the title field has been updated. Please comment out the code from line 47 'till 58 inside your process.php file, because we are going to do some deletion next.


delete()


Now, let's say we want to delete this record with the id of 6. We can do that like this:

 

/**
 * Delete() example
 */
public function actionDelete($id = null)
{
    return Info::delete($id);
}

 

So we are just passing the id of the row we want to delete to our delete() method. Our actionDelete method of the Info class is expecting to receive the $id we will use inside our delete() method. I could just hard code the value of id, but I wanted to do this example a bit differently than create and update ones. Now uncomment the code from the line 65 until 76 and refresh the process.php page. You should see the " Deletion was successful " message. If you refresh this page again, you should see the " Failure " message, because the record with the id of 6 do not exist anymore. But what if you do not want to delete by id, you maybe want to use some other value. Well, just write your condition as an associative array like this:

 

Info::delete(['title = '=>'test']);

 

This will delete the row from info table that has the title equal to "test". Be careful when you are doing this kind of deletion, make sure you know what you are doing first. For example, if you had one more row with the title equal to "test" and if it is appearing in your table before the one that you want to delete, it will be deleted instead. They will not be deleted both, since delete() method is,  as an precaution,  limiting the number of rows that can be deleted to 1.


deleteAll()


In very rare cases, you may want to delete all the rows from your table. For that, you can use deleteAll() method. Usage is simple, you just invoke the method like:

 

Info::deleteAll();

 

We are done with this tutorial. Thank you for reading, and I hope it was useful to you. Happy coding cool


If you want to post a comment, you need to signup and create a profile.

3 comment(s) :


#56 User Pic
Nenad Zivkovic August 30, 2015, 7:50 pm

Hi everyone. I have created version 2 of this class. It is much better. When I get some free time I will rewrite this tutorial. I hope that you will like the changes. You can find the code and examples on my Github profile here:

https://github.com/nenad-zivkovic/DB/

#54 User Pic
Nenad Zivkovic August 20, 2015, 6:02 pm

@ablero

It is not, because table should be defined as a static property of a model class. I don't know if you are familiar with MVC design, but your database bussines logic should be placed in models. So you should make model class and put code that comunicate with database there. That is why it is default to declare table that you are quering from in model class. I guess that if you want to structure your code in a different way you may want to use DB methods without having to have Model, but in that case you may want to change this class to suit your needs.

#53 User Pic
ablero August 18, 2015, 5:02 am
Hello, thanks for this nice class. However, is it possible to set the variable $table directly from the public static function ?