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
- Using Datatables with CodeIgniter - Part 1
- Datatables with CodeIgniter - Server Side - Part 2
- Datatables with CodeIgniter – Server Side Sorting – Part 3
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
ola
Nice Example ,
A have ERROR message in search input Illegal mix of collations for operation 'like'
how to fix that
thanx
Reply