CodeIgniter, MySQL

(Codeigniter) Simple Server-side Datatables bootstrap style example

Before I’ve shared example code for simple Server-side Datatables with Codeigniter. Now I’ll give an example like berfore but with bootstrap styling.
If you want you can use Ignited Datatables library. But with custom own code you can do more flexible.

 

server-side datatables bootstrap

server-side datatables bootstrap

Here several framework and library:

  1. PHP+ MySQL or you may use XAMPP –> Download
  2. Codeigniter 3.1.0 –> Download
  3. jQuery 2.2.3 –> Download
  4. DataTables 1.10.12 –> Download

step 1. create database and table for example. I have database named example and customers as table:

run this query:

create database example;
use example;
CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL DEFAULT '',
  `phone` varchar(50) NOT NULL DEFAULT '',
  `address` varchar(50) NOT NULL DEFAULT '',
  `city` varchar(50) NOT NULL DEFAULT '',
  `country` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- example data customers
INSERT INTO `customers` (`id`, `FirstName`, `LastName`, `phone`, `address`, `city`, `country`)
VALUES
    (1,'Carine ','Schmitt','40.32.2555','54, rue Royale','Nantes','France'),
    (2,'Jean','King','7025551838','8489 Strong St.','Las Vegas','USA'),
    (3,'Peter','Ferguson','03 9520 4555','636 St Kilda Road','Melbourne','Australia'),
    (4,'Janine ','Labrune','40.67.8555','67, rue des Cinquante Otages','Nantes','France'),
    (5,'Jonas ','Bergulfsen','07-98 9555','Erling Skakkes gate 78','Stavern','Norway'),
    (6,'Susan','Nelson','4155551450','5677 Strong St.','San Rafael','USA'),
    (7,'Zbyszek ','Piestrzeniewicz','(26) 642-7555','ul. Filtrowa 68','Warszawa','Poland'),
    (8,'Roland','Keitel','+49 69 66 90 2555','Lyonerstr. 34','Frankfurt','Germany'),
    (9,'Julie','Murphy','6505555787','5557 North Pendale Street','San Francisco','USA'),
    (10,'Kwai','Lee','2125557818','897 Long Airport Avenue','NYC','USA'),
    (11,'Diego ','Freyre','(91) 555 94 44','C/ Moralzarzal, 86','Madrid','Spain'),
    (12,'Christina ','Berglund','0921-12 3555','Berguvsvägen  8','Luleå','Sweden'),
    (13,'Jytte ','Petersen','31 12 3555','Vinbæltet 34','Kobenhavn','Denmark'),
    (14,'Mary ','Saveley','78.32.5555','2, rue du Commerce','Lyon','France'),
    (15,'Eric','Natividad','+65 221 7555','Bronz Sok.','Singapore','Singapore'),
    (16,'Jeff','Young','2125557413','4092 Furth Circle','NYC','USA'),
    (17,'Kelvin','Leong','2155551555','7586 Pompton St.','Allentown','USA'),
    (18,'Juri','Hashimoto','6505556809','9408 Furth Circle','Burlingame','USA'),
    (19,'Wendy','Victorino','+65 224 1555','106 Linden Road Sandown','Singapore','Singapore'),
    (20,'Veysel','Oeztan','+47 2267 3215','Brehmen St. 121','Bergen','Norway  '),
    (21,'Keith','Franco','2035557845','149 Spinnaker Dr.','New Haven','USA'),
    (22,'Isabel ','de Castro','(1) 356-5555','Estrada da saúde n. 58','Lisboa','Portugal'),
    (23,'Martine ','Rancé','20.16.1555','184, chaussée de Tournai','Lille','France'),
    (24,'Marie','Bertrand','(1) 42.34.2555','265, boulevard Charonne','Paris','France'),
    (25,'Jerry','Tseng','6175555555','4658 Baden Av.','Cambridge','USA'),
    (26,'Julie','King','2035552570','25593 South Bay Ln.','Bridgewater','USA'),
    (27,'Mory','Kentary','+81 06 6342 5555','1-6-20 Dojima','Kita-ku','Japan'),
    (28,'Michael','Frick','2125551500','2678 Kingston Rd.','NYC','USA'),
    (29,'Matti','Karttunen','90-224 8555','Keskuskatu 45','Helsinki','Finland'),
    (30,'Rachel','Ashworth','(171) 555-1555','Fauntleroy Circus','Manchester','UK'),
    (31,'Dean','Cassidy','+353 1862 1555','25 Maiden Lane','Dublin','Ireland'),
    (32,'Leslie','Taylor','6175558428','16780 Pompton St.','Brickhaven','USA'),
    (33,'Elizabeth','Devon','(171) 555-2282','12, Berkeley Gardens Blvd','Liverpool','UK'),
    (34,'Yoshi ','Tamuri','(604) 555-3392','1900 Oak St.','Vancouver','Canada'),
    (35,'Miguel','Barajas','6175557555','7635 Spinnaker Dr.','Brickhaven','USA'),
    (36,'Julie','Young','6265557265','78934 Hillside Dr.','Pasadena','USA'),
    (37,'Brydey','Walker','+612 9411 1555','Suntec Tower Three','Singapore','Singapore'),
    (38,'Frédérique ','Citeaux','88.60.1555','24, place Kléber','Strasbourg','France'),
    (39,'Mike','Gao','+852 2251 1555','Bank of China Tower','Central Hong Kong','Hong Kong'),
    (40,'Eduardo ','Saavedra','(93) 203 4555','Rambla de Cataluña, 23','Barcelona','Spain'),
    (41,'Mary','Young','3105552373','4097 Douglas Av.','Glendale','USA'),
    (42,'Horst ','Kloss','0372-555188','Taucherstraße 10','Cunewalde','Germany'),
    (43,'Palle','Ibsen','86 21 3555','Smagsloget 45','Århus','Denmark'),
    (44,'Jean ','Fresnière','(514) 555-8054','43 rue St. Laurent','Montréal','Canada'),
    (45,'Alejandra ','Camino','(91) 745 6555','Gran Vía, 1','Madrid','Spain'),
    (46,'Valarie','Thompson','7605558146','361 Furth Circle','San Diego','USA'),
    (47,'Helen ','Bennett','(198) 555-8888','Garden House','Cowes','UK'),
    (48,'Annette ','Roulet','61.77.6555','1 rue Alsace-Lorraine','Toulouse','France'),
    (49,'Renate ','Messner','069-0555984','Magazinweg 7','Frankfurt','Germany'),
    (50,'Paolo ','Accorti','011-4988555','Via Monte Bianco 34','Torino','Italy'),
    (51,'Daniel','Da Silva','+33 1 46 62 7555','27 rue du Colonel Pierre Avia','Paris','France'),
    (52,'Daniel ','Tonini','30.59.8555','67, avenue de l\'Europe','Versailles','France'),
    (53,'Henriette ','Pfalzheim','0221-5554327','Mehrheimerstr. 369','Köln','Germany'),
    (54,'Elizabeth ','Lincoln','(604) 555-4555','23 Tsawassen Blvd.','Tsawassen','Canada'),
    (55,'Peter ','Franken','089-0877555','Berliner Platz 43','München','Germany'),
    (56,'Anna','O\'Hara','02 9936 8555','201 Miller Street','North Sydney','Australia'),
    (57,'Giovanni ','Rovelli','035-640555','Via Ludovico il Moro 22','Bergamo','Italy'),
    (58,'Adrian','Huxley','+61 2 9495 8555','Monitor Money Building','Chatswood','Australia'),
    (59,'Marta','Hernandez','6175558555','39323 Spinnaker Dr.','Cambridge','USA'),
    (60,'Ed','Harrison','+41 26 425 50 01','Rte des Arsenaux 41 ','Fribourg','Switzerland'),
    (61,'Mihael','Holz','0897-034555','Grenzacherweg 237','Genève','Switzerland'),
    (62,'Jan','Klaeboe','+47 2212 1555','Drammensveien 126A','Oslo','Norway  '),
    (63,'Bradley','Schuyler','+31 20 491 9555','Kingsfordweg 151','Amsterdam','Netherlands'),
    (64,'Mel','Andersen','030-0074555','Obere Str. 57','Berlin','Germany'),
    (65,'Pirkko','Koskitalo','981-443655','Torikatu 38','Oulu','Finland'),
    (66,'Catherine ','Dewey','(02) 5554 67','Rue Joseph-Bens 532','Bruxelles','Belgium'),
    (67,'Steve','Frick','9145554562','3758 North Pendale Street','White Plains','USA'),
    (68,'Wing','Huang','5085559555','4575 Hillside Dr.','New Bedford','USA'),
    (69,'Julie','Brown','6505551386','7734 Strong St.','San Francisco','USA'),
    (70,'Mike','Graham','+64 9 312 5555','162-164 Grafton Road','Auckland  ','New Zealand'),
    (71,'Ann ','Brown','(171) 555-0297','35 King George','London','UK'),
    (72,'William','Brown','2015559350','7476 Moss Rd.','Newark','USA'),
    (73,'Ben','Calaghan','61-7-3844-6555','31 Duncan St. West End','South Brisbane','Australia'),
    (74,'Kalle','Suominen','+358 9 8045 555','Software Engineering Center','Espoo','Finland'),
    (75,'Philip ','Cramer','0555-09555','Maubelstr. 90','Brandenburg','Germany'),
    (76,'Francisca','Cervantes','2155554695','782 First Street','Philadelphia','USA'),
    (77,'Jesus','Fernandez','+34 913 728 555','Merchants House','Madrid','Spain'),
    (78,'Brian','Chandler','2155554369','6047 Douglas Av.','Los Angeles','USA'),
    (79,'Patricia ','McKenna','2967 555','8 Johnstown Road','Cork','Ireland'),
    (80,'Laurence ','Lebihan','91.24.4555','12, rue des Bouchers','Marseille','France'),
    (81,'Paul ','Henriot','26.47.1555','59 rue de l\'Abbaye','Reims','France'),
    (82,'Armand','Kuger','+27 21 550 3555','1250 Pretorius Street','Hatfield','South Africa'),
    (83,'Wales','MacKinlay','64-9-3763555','199 Great North Road','Auckland','New Zealand'),
    (84,'Karin','Josephs','0251-555259','Luisenstr. 48','Münster','Germany'),
    (85,'Juri','Yoshido','6175559555','8616 Spinnaker Dr.','Boston','USA'),
    (86,'Dorothy','Young','6035558647','2304 Long Airport Avenue','Nashua','USA'),
    (87,'Lino ','Rodriguez','(1) 354-2555','Jardim das rosas n. 32','Lisboa','Portugal'),
    (88,'Braun','Urs','0452-076555','Hauptstr. 29','Bern','Switzerland'),
    (89,'Allen','Nelson','6175558555','7825 Douglas Av.','Brickhaven','USA'),
    (90,'Pascale ','Cartrain','(071) 23 67 2555','Boulevard Tirou, 255','Charleroi','Belgium'),
    (91,'Georg ','Pipps','6562-9555','Geislweg 14','Salzburg','Austria'),
    (92,'Arnold','Cruz','+63 2 555 3587','15 McCallum Street','Makati City','Philippines'),
    (93,'Maurizio ','Moroni','0522-556555','Strada Provinciale 124','Reggio Emilia','Italy'),
    (94,'Akiko','Shimamura','+81 3 3584 0555','2-2-8 Roppongi','Minato-ku','Japan'),
    (95,'Dominique','Perrier','(1) 47.55.6555','25, rue Lauriston','Paris','France'),
    (96,'Rita ','Müller','0711-555361','Adenauerallee 900','Stuttgart','Germany'),
    (97,'Sarah','McRoy','04 499 9555','101 Lambton Quay','Wellington','New Zealand'),
    (98,'Michael','Donnermeyer',' +49 89 61 08 9555','Hansastr. 15','Munich','Germany'),
    (99,'Maria','Hernandez','2125558493','5905 Pompton St.','NYC','USA'),
    (100,'Alexander ','Feuer','0342-555176','Heerstr. 22','Leipzig','Germany'),
    (101,'Dan','Lewis','2035554407','2440 Pompton St.','Glendale','USA'),
    (102,'Martha','Larsson','0695-34 6555','Åkergatan 24','Bräcke','Sweden'),
    (103,'Sue','Frick','4085553659','3086 Ingle Ln.','San Jose','USA'),
    (104,'Roland ','Mendel','7675-3555','Kirchgasse 6','Graz','Austria'),
    (105,'Leslie','Murphy','2035559545','567 North Pendale Street','New Haven','USA'),
    (106,'Yu','Choi','2125551957','5290 North Pendale Street','NYC','USA'),
    (107,'Martín ','Sommer','(91) 555 22 82','C/ Araquil, 67','Madrid','Spain'),
    (108,'Sven ','Ottlieb','0241-039123','Walserweg 21','Aachen','Germany'),
    (109,'Violeta','Benitez','5085552555','1785 First Street','New Bedford','USA'),
    (110,'Carmen','Anton','+34 913 728555','c/ Gobelas, 19-1 Urb. La Florida','Madrid','Spain'),
    (111,'Sean','Clenahan','61-9-3844-6555','7 Allen Street','Glen Waverly','Australia'),
    (112,'Franco','Ricotti','+39 022515555','20093 Cologno Monzese','Milan','Italy'),
    (113,'Steve','Thompson','3105553722','3675 Furth Circle','Burbank','USA'),
    (114,'Hanna ','Moos','0621-08555','Forsterstr. 57','Mannheim','Germany'),
    (115,'Alexander ','Semenov','+7 812 293 0521','2 Pobedy Square','Saint Petersburg','Russia'),
    (116,'Raanan','Altagar,G M','+ 972 9 959 8555','3 Hagalim Blv.','Herzlia','Israel'),
    (117,'José Pedro ','Roel','(95) 555 82 82','C/ Romero, 33','Sevilla','Spain'),
    (118,'Rosa','Salazar','2155559857','11328 Douglas Av.','Philadelphia','USA'),
    (119,'Sue','Taylor','4155554312','2793 Furth Circle','Brisbane','USA'),
    (120,'Thomas ','Smith','(171) 555-7555','120 Hanover Sq.','London','UK'),
    (121,'Valarie','Franco','6175552555','6251 Ingle Ln.','Boston','USA'),
    (122,'Tony','Snowden','+64 9 5555500','Arenales 1938 3\'A\'','Auckland  ','New Zealand');

or you can download sample data here : example.sql

step 2. copy codeigniter you have download to your web server htdocs directory. and create assets folder to codeigniter directory include all javascript and css library like this.

server side datatables bootstrap assets

server side datatables bootstrap assets

 

step 3. config route.php . to configure route for default controller.

config/route.php

$route['default_controller'] = 'customers';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;

for codeigniter version 3.0.3 or later base_url must be configure manually. you can config this here:
config/config.php

server side datatables bootstrap base-url

server side datatables bootstrap base-url

or if you want make base_url dinamically you can read this post : Dynamic base_url() and site_url() Codeigniter 3.0.3 +

 

step 4. create Customers_model.php in model directory.

<?php defined('BASEPATH') OR exit('No direct script access allowed'); class Customers_model extends CI_Model { var $table = 'customers'; var $column_order = array(null, 'FirstName','LastName','phone','address','city','country'); //set column field database for datatable orderable var $column_search = array('FirstName','LastName','phone','address','city','country'); //set column field database for datatable searchable var $order = array('id' => 'asc'); // default order 

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    private function _get_datatables_query()
    {
        
        $this->db->from($this->table);

        $i = 0;
    
        foreach ($this->column_search as $item) // loop column 
        {
            if($_POST['search']['value']) // if datatable send POST for search
            {
                
                if($i===0) // first loop
                {
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else
                {
                    $this->db->or_like($item, $_POST['search']['value']);
                }

                if(count($this->column_search) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }
        
        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order))
        {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    function get_datatables()
    {
        $this->_get_datatables_query();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered()
    {
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all()
    {
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }

}

step 5. create controller file Customers.php

<?php defined('BASEPATH') OR exit('No direct script access allowed'); class Customers extends CI_Controller { public function __construct() { parent::__construct(); $this->load->model('customers_model','customers');
    }

    public function index()
    {
        $this->load->helper('url');
        $this->load->view('customers_view');
    }

    public function ajax_list()
    {
        $list = $this->customers->get_datatables();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $customers) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = $customers->FirstName;
            $row[] = $customers->LastName;
            $row[] = $customers->phone;
            $row[] = $customers->address;
            $row[] = $customers->city;
            $row[] = $customers->country;

            $data[] = $row;
        }

        $output = array(
                        "draw" => $_POST['draw'],
                        "recordsTotal" => $this->customers->count_all(),
                        "recordsFiltered" => $this->customers->count_filtered(),
                        "data" => $data,
                );
        //output to json format
        echo json_encode($output);
    }


}

step 6. and the last create for view. customers_view.php at views directory

<!DOCTYPE html>
<html>
    <head> 
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Simple Serverside jQuery Datatable</title>
    <link href="<?php echo base_url('assets/bootstrap/css/bootstrap.min.css')?>" rel="stylesheet">
    <link href="<?php echo base_url('assets/datatables/css/dataTables.bootstrap.min.css')?>" rel="stylesheet">
    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
    </head> 
<body>
    <div class="container">
        <h1 style="font-size:20pt">Simple Serverside Datatable Codeigniter Bootstrap</h1>

        <h3>Customers Data</h3>
        <br>
        <table id="table" class="table table-striped table-bordered" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>No</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Phone</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Country</th>
                </tr>
            </thead>
            <tbody>
            </tbody>

            <tfoot>
                <tr>
                    <th>No</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Phone</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Country</th>
                </tr>
            </tfoot>
        </table>
    </div>

<script src="<?php echo base_url('assets/jquery/jquery-2.2.3.min.js')?>"></script>
<script src="<?php echo base_url('assets/bootstrap/js/bootstrap.min.js')?>"></script>
<script src="<?php echo base_url('assets/datatables/js/jquery.dataTables.min.js')?>"></script>
<script src="<?php echo base_url('assets/datatables/js/dataTables.bootstrap.min.js')?>"></script>


<script type="text/javascript">

var table;

$(document).ready(function() {

    //datatables
    table = $('#table').DataTable({ 

        "processing": true, //Feature control the processing indicator.
        "serverSide": true, //Feature control DataTables' server-side processing mode.
        "order": [], //Initial no order.

        // Load data for the table's content from an Ajax source
        "ajax": {
            "url": "<?php echo site_url('customers/ajax_list')?>",
            "type": "POST"
        },

        //Set column definition initialisation properties.
        "columnDefs": [
        { 
            "targets": [ 0 ], //first column / numbering column
            "orderable": false, //set not orderable
        },
        ],

    });

});

</script>

</body>
</html>

for example demo you can try this code here:

Preview Demo

you can also download source here:
Download








You Might Also Like

  • Syarif Hidayat

    Mbah kalo mau url ajax nya dikirim dari controller gimana ya?
    Misal dicontroller yang manggil view:


    $data['ajax_get'] = '/url/to/ajax/function';

    lalu di javascriptnya:

    "url": "",

    Saya error terus ‘undefined variable’