DataTables to display MySQL table using CodeIgnitor : Part 1

Friday, October 30, 2015
This blog is 2 part tutorial to help you to display MySQL data  using Codeignitor. Previously, before MVC, I use to write loops and pagination of tables from scratch. I use to do that on java in 11th class in my school day. I learnt loop from my teacher but I learnt pagination on my own. Now a days javascripts have arrived that can perform the server side processing. is the way to go if you have table with small amount of rows. Thus server side pagination by CodeIgnitor can be ignored. However, when it comes to editing and displaying huge sets of data, it is better to have because it is design with efficient server side processing and also editing in DataTables is only trial based.

Difference between jTable and DataTables is in the way they deal with HTML data. jTable just requires a DIV to be assigned and jquery and PHP does the rest whereas in DataTables you simply generate a standard HTML table which is later converted into pagination.

This is the Part 1 that show how to use Datatables to display tables. The tutorial begins with basic step considering you have already installed WAMP or LAMP and unzipped codeignitor.
  1. Configure connection to database in application/config/database.php.
    $db['default'] = array(
     'dsn' => '',
     'hostname' => 'localhost',
     'username' => 'root',
     'password' => 'password',
     'database' => 'databasename',
  2. Add database library to application/config/autoload.php
    $autoload['libraries'] = array('database');
  3. Below is a sample table. Use your own table that you want.
    create table persons(
    name varchar(500),
    email varchar(500),
    initials varchar(10)
  4. Create a Model: application/models/View_model.php
    class View_model extends CI_Model{
     function view_persons()
     $query = $this->db->get('persons');
     return $query->result();
  5. Create a controller application/controllers/View.php
    <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
    class View extends CI_Controller {
     function index(){
      $data['$title'] = 'Table View';
      $data['rows'] = $this->View_model->view_persons();
  6. Finally, create a view file /application/views/dataview.php
    <!DOCTYPE html>
      <title><?= $title ?></title>
      <table id="personstable">
        foreach ($rows as $row)
          <?= $row->id ?>
          <?= $row->name ?>
          <?= $row->email ?>
          <?= $row->initials ?>
      <script type="text/javascript" charset="utf8" src="//"></script>
      <script type="text/javascript" src="//"></script>
      <link rel="stylesheet" type="text/css" href="//" />

Thats it! If you find error please comment below. The next part will be dealing with editing and handling large data using jTable.

No comments: