Using Datatables with CodeIgniter Tutorial

Datatables is a powerful plugin for jQuery that allows you to easily display tabular data to users. It's quick to setup, easy to use and has many advanced functions that allow you to build great tables for viewing tabular data.

This tutorial is going to look at how you can implement datatables into your CodeIgniter project. We'll be grabbing some data from a database and then using Datatables' plugin to display it, allowing for ajax searching, sorting and pagination.

Datatables Series

Setup

First you'll need to grab a copy of Datatables. You can either download Datatables from here: https://datatables.net/ or you can use their CDN version:

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.js"></script> 

Since this is a jQuery plugin, you also need to have jQuery included on your page.

Once you have these files, let's setup our CodeIgniter application. We are going to create a simple application that displays book data. Here are the files we need to create:

application/controllers/Books.php
application/models/Books_model.php
application/views/books/index.php

The controller is where all our logic data is going, the model will be used for calls to the database and our index page is where we will add the Datatables Javascript code. Let's start with setting up our index.php first.

Setting Up Datatables

Create a new file called index.php in application/views/books/. Let's add the following HTML:

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Book Display</title>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.js"></script> 
    </head>
    <body>
    <h1>Book List</h1>
    </body>
</html>

This simple HTML page sets us up to use the Datatables plugin. Notice we place the jQuery library before we include the Datatables plugin.

Next, let's build our sample table. For Datatables, we still use HTML table tags. The real magic is applied using Javascript. Insert the following table HTML into the body of our index.php page:

<table id="book-table">
<thead>
<tr><td>Book Title</td><td>Book Price</td><td>Book Author</td><td>Rating</td><td>Publisher</td></tr>
</thead>
<tbody>
</tbody>
</table>

Easy enough. We are going to make a table to show some simple data about our book list.

Next let's invoke DataTables. We are going to create a Datatables instance on our table in it's most simplest form.

<script type="text/javascript">
$(document).ready(function() {
    $('#book-table').DataTable();
});
</script>

I like to place this script at the bottom of my page just before the closing body tag in order for the page to fully load before the script kicks into gear. You can place it anywhere, it's really just an old habit.

Next we want to setup our CodeIgniter application. This tutorial isn't about using CodeIgniter basics, so if you do not know how to use CodeIgniter, I recommend using their User Guide.

First let's build our controller file:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Books extends CI_Controller
{

     public function index()
     {
          $this->load->view("books/index.php", array());
     }

}
?>

This basically makes it so we can view our index.php script when we visit /books/index.php. It's a very simple script using the basics of CodeIgniter. We will be building on this as we go on.

Since you've already created your /books/index.php file, you should be able to view it by going to yourdomain.com/books/index.php. You should get something like this: 

Not very pretty is it? Well let's make it look better using Bootstrap. Luckily for us, Bootstrap styling is built into Datatables too. We just need to make a few changes to our index.php page:

Add styling for bootstrap and datatables to our index.php:

<link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.13/css/dataTables.bootstrap.min.css" />

Add the Datatables bootstrap script after the initial Datatables script:

<script type="text/javascript" src="https://cdn.datatables.net/1.10.13/js/dataTables.bootstrap.min.js"></script>

Delete the original datatable's styling from our page:

https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.css

Now that we have bootstrap theme included, we just need to apply the styles. Something like this should do the trick (replace thehtml with the following code):

<div class="container">
    <div class="row">
    <div class="col-md-12">

    <h1>Book List</h1>

    <table id="book-table" class="table table-bordered table-striped table-hover">
     <thead>
     <tr><td>Book Title</td><td>Book Price</td><td>Book Author</td><td>Rating</td><td>Publisher</td></tr>
     </thead>
     <tbody>
     </tbody>
     </table>

    </div>
    </div>
    </div>

You should now get a decent looking page like this:

Database

The next step we are going to create a simple Books table in our MYSQL database. We'll be pulling the data from this table to be used in our Datatable.

Create this simple table, or import this SQL:

CREATE TABLE `books` (
  `ID` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `author` varchar(255) NOT NULL,
  `rating` int(11) NOT NULL,
  `publisher` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `books`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `books`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

Some dummy data:

INSERT INTO `books` (`ID`, `name`, `price`, `author`, `rating`, `publisher`) VALUES
(1, 'Harry Potter And The Order Of The Phoenix', '10.99', 'J.K. Rowling', 9, 'Bloomsbury'),
(2, 'Harry Potter And The Goblet Of Fire', '6.99', 'J.K Rowling', 8, 'Bloomsbury'),
(3, 'Lord Of The Rings: The Fellowship Of The Ring', '8.99', 'J. R. R. Tolkien', 8, 'George Allen & Unwin'),
(4, 'Lord Of The Rings: The Two Towers', '4.55', 'J. R. R. Tolkien', 8, 'George Allen & Unwin'),
(5, 'Lord Of The Rings: The Return Of The King', '7.99', 'J. R. R. Tolkien', 9, 'George Allen & Unwin'),
(6, 'End of Watch: A Novel', '5.00', 'Stephen King', 7, 'Scribner'),
(7, 'Truly Madly Guilty', '4.55', 'Liane Moriarty', 6, 'Flatiron Books'),
(8, 'All There Was', '3.99', 'John Davidson', 3, 'Newton'),
(9, 'Mystery In The Eye', '8.44', 'E.L. Joseph', 8, 'Red Books'),
(10, 'Neo Lights', '12.99', 'George Nord', 8, 'Heltower'),
(11, 'Universe: History', '13.99', 'Albert Shoon', 4, 'Easy Books'),
(12, 'Green Earth', '7.99', 'Ashleigh Turner', 4, 'Yellowhouse'),
(13, 'Music Of The Ages', '3.83', 'James King', 3, 'Universe Co'),
(14, 'Ancient Tea', '3.99', 'Jess Red', 8, 'Yellowhouse');

Once you have created your data, we need to create our model that grabs it from the database. Create the following Books_model.php:

<?php

class Books_Model extends CI_Model
{

     public function get_books()
     {
          return $this->db->get("books");
     }

}

?>

Now we can use our model in our script by loading it. Modify Books.php controller to load the model:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Books extends CI_Controller
{

    public function __construct() {
        Parent::__construct();
        $this->load->model("books_model");
    }

     public function index()
     {
          $this->load->view("books/index.php", array());
     }

}
?>

Now that we have access to our book data, we need to update our Datatables code in index.php. Datatables needs to know what page to query to grab our data. We can do this simply by modifying our DataTable() call on our table to:

<script type="text/javascript">
$(document).ready(function() {
    $('#book-table').DataTable({
        "ajax": {
            url : "<?php echo site_url("books/books_page") ?>",
            type : 'GET'
        },
    });
});
</script>

This basically tells DataTables to look up the function in our Books controller books_page and look for the datatable's data. Next we build our books_page function in our Books.php controller file:

public function books_page()
     {

          // Datatables Variables
          $draw = intval($this->input->get("draw"));
          $start = intval($this->input->get("start"));
          $length = intval($this->input->get("length"));


          $books = $this->books_model->get_books();

          $data = array();

          foreach($books->result() as $r) {

               $data[] = array(
                    $r->name,
                    $r->price,
                    $r->author,
                    $r->rating . "/10 Stars",
                    $r->publisher
               );
          }

          $output = array(
               "draw" => $draw,
                 "recordsTotal" => $books->num_rows(),
                 "recordsFiltered" => $books->num_rows(),
                 "data" => $data
            );
          echo json_encode($output);
          exit();
     }

This is the basis of grabbing data from our database and then putting it out in a format that the Datatables plugin can read. Let's explain what each line of code does:

// Datatables Variables
          $draw = intval($this->input->get("draw"));
          $start = intval($this->input->get("start"));
          $length = intval($this->input->get("length"));

These 3 variables are sent to our page as $_GET requests automatically by Datatables. $draw is a unique number sent to our page so that Datatables can handle the request correctly. We output this variable at the end so the plugin knows it has the right request.

$start and $length are used for pagination. We'll be using these two variables in our database model later on, but for now, think of them as being used in terms of the starting point from which we grab records (i.e. we want to start from record 5) and length is how many records to return.

 $books = $this->books_model->get_books();
 $data = array();

Next we make a call to our books model to grab all the books from our database. The $data array will be used to store our table data.

foreach($books->result() as $r) {

       $data[] = array(
            $r->name,
            $r->price,
            $r->author,
            $r->rating . "/10 Stars",
            $r->publisher
       );
}

Next we loop through our database results and store the data in an array. The fields must correspond to the position in which you created them in your HTML table. Each field is a string of data which you can manipulate on the fly (notice the ratings field where we append an extra string).

$output = array(
               "draw" => $draw,
                 "recordsTotal" => $books->num_rows(),
                 "recordsFiltered" => $books->num_rows(),
                 "data" => $data
            );
          echo json_encode($output);
          exit();

Datatables likes to read our data in json format. It also expects at least four specific data points back. In the code above, you notice we make a new array called $output which contains the $draw variable from earlier on, the total number of rows and our $data array which contains the table output.

Finally we need to use PHP's json_encode function on our new $output array and exit the program.

You should now see your book data being displayed in your table: 

Notice how datatables automatically implements pagination, sorting and searching for you. You don't need to do anything extra for it. We can specify the amount of rows we want to display in our Datatables Javascript code:

$('#book-table').DataTable({
        "pageLength" : 5,
        "ajax": {
            url : "<?php echo site_url("books/books_page") ?>",
            type : 'GET'
        },
    });

The pageLength option is sent to our controller script in the variable $length. You can change it to whatever value you want.

The above solution is okay for very small datasets, but when we have thousands of rows to sort we end up putting massive strain on our database. In our next tutorial we'll cover large datasets and handling queries serverside.

Resources



Enjoyed that? Check These Posts Out

Learn Linux: Setup DNS Server with Bind On Debian

Using Datatables with CodeIgniter Tutorial

Setup the new Envato API to check Product Codes with PHP

Fullcalendar with PHP and CodeIgniter - Adding Events - Part 3

...

Article Comments

Let us know your thoughts below by adding a quick comment!

14/06/2017

ola

Nice Example ,
A have ERROR message in search input Illegal mix of collations for operation 'like'
how to fix that
thanx

Reply

14/06/2017

patchesoft

Hi Ola,

Looks like your error message is related to mysql: http://stackoverflow.com/questions/18629094/illegal-mix-of-collations-for-operation-like-while-searching-with-ignited-data

What does your query look like?

Reply

14/06/2017

ola

My query works Fine on local server and i did not get and same Error

$this-&gt;db-&gt;select('*');
$this-&gt;db-&gt;from($this-&gt;table);

$this-&gt;db-&gt;join('unversity', 'unversity.UnVerId = training.university');
$this-&gt;db-&gt;join('major', 'major.DeptID = training.major');
$this-&gt;db-&gt;join('cualification', 'cualification.CerID = training.cualification');

and the function

foreach ($this-&gt;column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{

if($i===0) // first loop
{
$no = $_POST['start'];
$no++;
$row[] = $no;
// open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this-&gt;db-&gt;like($item, $_POST['search']['value']);
}
else
{
$this-&gt;db-&gt;or_like($item, $_POST['search']['value']);
}

if(count($this-&gt;column_search) - 1 == $i) //last loop
//$this-&gt;db-&gt;group_end(); //close bracket
$no = $_POST['start'];
$no--;
$row[] = $no;
}
$i++;
}

Reply

14/06/2017

patchesoft

Once you execute the query, can you run this:

echo $this->db->last_query();

and see what it outputs?

Reply

14/06/2017

ola

SELECT * FROM (`training`) JOIN `unversity` ON `unversity`.`UnVerId` = `training`.`university` JOIN `major` ON `major`.`DeptID` = `training`.`major` JOIN `cualification` ON `cualification`.`CerID` = `training2`.`cualification`

Reply

14/06/2017

patchesoft

I'm really not sure to be honest. I can't see anything obviously wrong with your query. Such a strange error!

Reply

14/06/2017

ola

:(

Reply

14/06/2017

ola

Oky I will try to Solve It , Any Idea to change collation on Query ?

Reply

14/06/2017

avik

invalid json response

Reply

14/06/2017

patchesoft

What happens when you try to go to yoursite.com/books/books_page ? Is there an error/output?

Reply

14/06/2017

Robert

Same issue as avik. Can you kindly hint about the routes.php configuration? Thank you

Reply

14/06/2017

Robert

Hello my previous question is solved. The issue was in the .htaccess and in the routes.php. I have mistyped in the routes.php (that was a secondary errors) but mainly my issue was the missing .htaccess properly configured for a local xampp test environment

Now one technical question. My table has some thousands rows, how to load only the ones that fit the first pages of the pagination? The idea would be to dynamically load the following ones when browsing the pagination.

If the above hypothesis could work... how will it be handled the search field? Will it run realtime queries? Or that will search only through the already loaded rows?

Thank you

Reply

14/06/2017

patchesoft

Hi,

Did you take a look at the Part 2 & 3 sections? This shows you how it loads only the paginated data on request instead of all 1000 rows. When doing searching, it will perform a search on all rows in the database (not just the ones loaded) via AJAX.

Reply

15/06/2017

Wole

I heard datatables limits to 2000 rows, If I have more than 2000 rows will it work?

Reply

15/06/2017

Patchesoft

@wole there isn't a limit on how many rows datatables can use :)

Reply

Leave A Comment