Gaurav Panchal's Blog: DataTables to display MySQL table using CodeIgnitor : Part 1

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. DataTables.net 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 jTable.org 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.

Configure connection to database in application/config/database.php.

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => 'password',
 'database' => 'databasename',

Add database library to application/config/autoload.php
```
$autoload['libraries'] = array('database');
```

Below is a sample table. Use your own table that you want.

create table persons(
id int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(500),
email varchar(500),
initials varchar(10)
);

Create a Model: application/models/View_model.php

<?php
class View_model extends CI_Model{

 function view_persons()
 {  
 $query = $this->db->get('persons');
 return $query->result();
 }
}
?>

Create a controller application/controllers/View.php

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

class View extends CI_Controller {
 
 function index(){
  $this->load->model('View_model');
  
  $data['$title'] = 'Table View';
  $data['rows'] = $this->View_model->view_persons();
  $this->load->view('dataview');
 }
}
?>

Finally, create a view file /application/views/dataview.php

<!DOCTYPE html>
<html>
 <head>
  <title><?= $title ?></title>
 </head>
 <body>  
  <table id="personstable">
   <thead>
    <td>ID</td><td>Name</td><td>Email</td><td>Initials</td>
   </thead>
   <tbody>    
    <?php 
    foreach ($rows as $row)
    {
      ?>
    <tr>
     <td>
      <?= $row->id ?>
     </td>      
     <td>
      <?= $row->name ?>
     </td>
     <td>
      <?= $row->email ?>
     </td>
     <td>
      <?= $row->initials ?>
     </td>
    </tr>
    <?php
    }
    ?>
   </tbody>
  </table>
  
  <script type="text/javascript" charset="utf8" src="//code.jquery.com/jquery-1.10.2.min.js"></script>
  <script type="text/javascript" src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" />
  <script>   
   $(document).ready(function(){
    $('#personstable').DataTable();
   });
  </script>
 </body>
</html>

Result

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

Gaurav Panchal's Blog

DataTables to display MySQL table using CodeIgnitor : Part 1

Friday, October 30, 2015

No comments: