How to use datatables on Codeigniter (especially for making the search box)?

Question

Well, before i get started, im sorry for bad english yet my little knowledge in programming especially on codeigniter that i've been working on 2 weeks.

then i found the datatables. i found it interesting if i can integrate--or anything you guys called it-- the datatables with the codeigniter.

i downloaded the ignited-datatables, attached it, and yet it still not working on my project.

some help please? im new to codeigniter and datatables. i'll appreciate all of the answers, though.

P.S : just ask it if you guys need the code, i'll show you ;)

Answer 1

First You must add cdn link for data table in header of your view file In view

<div class="table-responsive">
      <table id="user_table" class="table table-striped b-t b-light">
        <thead>
           <tr>
             <th>ID</th>
             <th>Name</th>
             <th>Email</th>
             <th>Status</th>
             <th style="width:30px;">Action</th>
            </tr>
       </thead>
       <tbody>
             <td></td>
       </tbody>
     </table>
</div>

in Js

    <script>
        $(document).ready(function(){
            user_table();
        });

        function user_table(){
            if ($.fn.DataTable.isDataTable('#user_table')) {
             $('#user_table').DataTable().destroy();
            }

            $('#user_table').DataTable({
                "processing" : true,
                "serverSide" : true,
                "ajax" : {
                    "url" : base_url + 'User/get_user_data',
                    "type" : 'POST',
                },
                "columns" : [
                    {"data" : "id" },
                    {"data" : "name"},
                    {"data" : "email"},
                    {"data" : "status"},
                    {"data" : "action"},
                 ]
            });
        }
    </script>



    In controller

    public function get_user_data(){
            
            $columns = ['id','name','email','status','action'];
            $limit = $this->input->post('length');
            $start = $this->input->post('start');
            $order = $columns[$this->input->post('order')[0]['column']];
            $dir = $this->input->post('order')[0]['dir'];
            $totaldata = $this->User_model->count_all_user();
            $totalFiltered = $totaldata;

            if (empty($this->input->post('search')['value'])) {
                $users_data = $this->User_model->get_user_data($limit,$start,$order,$dir);

            }else{
                $search = $this->input->post('search')['value'];
                $users_data = $this->User_model->search_user_data($limit,$start,$order,$dir,$search);
                $totalFiltered = $this->User_model->count_search_user_data($search);
            }

            $data = [];
            if (!empty($users_data))
            {
                foreach($users_data as $user)
                {
                    $nestedData['id'] = $user->id;
                    $nestedData['name'] = $user->name;
                    $nestedData['email'] = $user->email;
                    $nestedData['status'] = $user->status;
                    $nestedData['action'] = '<button Onclick="get_user_data('.$user->id.')" class="btn btn-sm btn-primary">view</button>';
                    $data[] = $nestedData;
                }
            }

            $json_data = array(
                "draw" => intval($this->input->post('draw')),
                "recordsTotal" => intval($totaldata),
                "recordsFiltered" => intval($totalFiltered),
                "data" => $data
            );
            echo json_encode($json_data);
    }

    In model

        public function count_all_user(){
            $this->db->select('*');
            $this->db->from('users');
            return $this->db->count_all_results();
        }

         public function get_user_data($limit,$start,$order,$dir){
            $this->db->select('*');
            $this->db->from('users');
            $this->db->limit($limit, $start);
            $this->db->order_by($order, $dir);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            }else
            {
                return null;
            }
         }

        public function search_user_data($limit, $start, $order, $dir, $search) {
            $this->db->select('*');
            $this->db->from('users');
            $this->db->group_start();
            $this->db->or_like('email', $search);
            $this->db->or_like('name', $search);
            $this->db->or_like('id', $search);
            $this->db->group_end();
            $this->db->limit($limit,$start);
            $this->db->order_by($order,$dir);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            }else
            {
                return null;
            }
        }

         public function count_search_user_data($search){
             $this->db->like('id', $search);
             $query = $this->db->get('users');
             return $query->num_rows();
         }

Answer 2

I know the question has been answered but this might be helpful to others as well.

If you want to use pure javascript library (no jQuery dependency), you could use Vanilla-DataTables

To use:

Add these into your main file

<link href="https://cdn.jsdelivr.net/npm/vanilla-datatables@latest/dist/vanilla-dataTables.min.css" rel="stylesheet" type="text/css">
<script src="https://cdn.jsdelivr.net/npm/vanilla-datatables@latest/dist/vanilla-dataTables.min.js" type="text/javascript"></script>

Then on your js file just add:

var dataTable = new DataTable("#table_list"); //where #table_list is the id of your table

There are also a lot of Options available applicable to your choice

Or if you want newer library, you could also check on Simple-DataTables

Hope this helps.

Answer 3

Do you know www.datatables.net? You can include javascript & css (local installation):

<!-- DataTables CSS -->
<link href="<?php echo base_url('assets/css/plugins/dataTables/jquery.dataTables.css') ?>" rel="stylesheet" />

<!-- jQuery -->
<script src="<?php echo base_url('assets/js/jquery-1.10.2.min.js') ?>"></script>

<!-- DataTables JS -->
<script src="<?php echo base_url('assets/js/plugins/dataTables/jquery.dataTables.min.js') ?>"></script>

Then, inside your table:

<table id="dataTables-example"> ...</table>

Finally, the script:

$(document).ready( function () {
$('#dataTables-example').DataTable();
} );

Answer 4

I suppose you already have a running database. For the example, let's suppose that we have a table named "cars" containing a list of cars and their specifities.

cars :
  id    |  brand  | model  | year
   1    |  Ford   | Escort | 1989
   2    |  Audi   | A4     | 2005
  ...   |  ...    | ...    | ...

How it works on codeigniter :

1/ Configuration file

---

Set your database connection values in application/config/database.php.

Here we are setting a connection to our local server for the database "mydatabase". Login/password are myusername/mypassword.

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'myusername',
    'password' => 'mypassword',
    'database' => 'mydatabase',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => TRUE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'autoinit' => TRUE,
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

More details here : http://www.codeigniter.com/user_guide/database/configuration.html

2/ Models

---

Codeigniter is a MVC framework. It means that you must separate database (Models) access from what you display (Views) or any other treatment (Controllers). In application/models/ we create a new model named Cars.php :

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

class Cars extends CI_Model
{   
}

Inside our new class, we are going to write a function that gets all the cars in the table. CodeIgniter uses an ORM called Active Record. To summarize, it's a tool that makes query writing easier and safier.

public function getAllCars()
{
    $query = $this->db->get('cars');
    //This how you write *SELECT * FROM cars* with Active Record

    return $query->result(); //The result is an array of objects, each row = an object
}

More details : http://www.codeigniter.com/user_guide/general/models.html http://www.codeigniter.com/user_guide/database/active_record.html

3/ Controller

---

Now, we need to create a new controller. So in application/controllers make a new file named Cars.php :

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

class Cars extends CI_Controller 
{
    public function __construct()
    {
        parent::__construct();
    }

    public function index()
    {

    }

}

Inside our new controller we are going to call our model.

public function index()
{
    //First we need to load the model
    $this->load->model('cars');

    //Now we need to get our car list using the function we write on our model
    $car_list = $this->cars->getAllCars();

   //Finally, we send are list to the view so we can display it.
   $data["cars_lst"] = $car_list;
   $this->load->view("mycars", $data); //We are building this view in the next step.
}

More details : http://www.codeigniter.com/user_guide/general/controllers.html

4/ View

---

Final step, the output. In application/views/ create a new file mycars.php. Keep in mind that in our controller, we called that view and sent to it an array with our car_list at the index "car_lst". We now can use that array index as a var in our view which will contains our list of cars (remember the model result ?).

<!DOCTYPE html>
<html>

    <head>
         <!-- Meta, title, CSS, ... -->
    </head>

    <body>
        <table class="table table-hover">
             <thead>
                 <tr>
                     <th>Id</th>
                     <th>Brand</th>
                     <th>Model</th>
                     <th>Year</th>
                 </tr>
             </thead>

             <?php foreach ($car_lst as $c): ?>

             <tr>
                 <td><?php echo $c->id; ?></td>
                 <td><?php echo $c->brand; ?></td>
                 <td><?php echo $c->model; ?></td>
                 <td><?php echo $c->model; ?></td>              
             </tr>

             <?php endforeach; ?>

        </table>
    </body>
</html>

5/ Voilà !

---

Now if we access our application with http://localhost/mysite/index.php/cars/ we should see our cars list.

This is how to deal with database in codeigniter. Of course it's a light preview but you should be able to understand how it works and find your own solutions.