jTable to edit and process MySQL table using CodeIgnitor : Part 2

Tuesday, November 3, 2015
Previously in Part 1, we generated table using Datatables. However, part 1 tutorial was only to display data from database. To create, update and delete rows, it's better to use jTable as its freely available. Just follow the simple steps below:
  1. Download jTable PHP Sample: http://jtable.org/downloads/jTable-PHP-Samples.zip
  2. Unzip the zip and place the content of Codes folder in root web folder. Don't worry if you already have codeigniter files.
  3. Personally. I feel dealing with jTable code is very tricky. Therefore, I would initially test jTable functionality in Core PHP by updating MySQL with sql file in unzipped Database folder. Start by converting all uppercase to lowercase in JavaScript and links. Then connect to database by updating PersonActions.php and PersonActionsPagedSorted.php
    <link href="scripts/jtable/themes/lightcolor/blue/jtable.css" rel="stylesheet" type="text/css" />

<script src="scripts/jtable/jquery.jtable.js" type="text/javascript"></script>
    Open the browser and check if you can view the data in table. If you are successful then proceed to next step.
  4. Configure connection to database in application/config/database.php. 
    $db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => 'password',
 'database' => 'databasename',
  5. Add database library to application/config/autoload.php 
    $autoload['libraries'] = array('database');
  6. Create a model application/models/Jtable_model.php to define jTable configuration and update, create, delete and list options. The model includes jtableconf function that generates the json script as well as select MySQL table for operations. This function would be further explained later. 
    <?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Jtable_model extends CI_Model
{
 function jtableconf()
 {
  $jtablefields = array(
   'PersonId' => array (
    'key' => true,
    'create' => false,
    'edit' => false,
    'list' => false
   ),
   'Name' => array (
    'title' => 'Author Name',
    'width' => '40%'
   ),
   'Age' => array (
    'title' => 'Age',
    'width' => '20%'
   ),
   'RecordDate' => array (
    'title' => 'Record date',
    'width' => '30%',
    'type' => 'date',
    'create' => false,
    'edit' => false
   )
  );

  $jtable = array (
  'title' => 'Table of people',
  'tablename' => 'people', // Important
  'idname' => 'PersonId', // Important
  'paging' => true,
  'pageSize' => 5,
  'sorting' => true,
  'defaultSorting' => 'Name ASC',
  'actions' => array (
   'listAction' => 'jtable/listrecord',
   'createAction' => 'jtable/create',
   'updateAction' => 'jtable/update',
   'deleteAction' => 'jtable/deleterecord'
  ),
  'fields' => $jtablefields
  );
  
  return $jtable;
 }

 function listtable($tablename, $sorting = 'id', $start = '0', $size = '10')
 {
  
  $query = $this->db->get($tablename);
  $recordCount = $query->num_rows();
  
  $query = $this->db->query("SELECT * FROM ". $tablename ." ORDER BY " . $sorting . " LIMIT " . $start . "," . $size );
  
  $rows = array();
  foreach ($query->result() as $row)
  {
      $rows[] = $row;
  }

  $jTableResult = array();
  $jTableResult['Result'] = "OK";
  $jTableResult['TotalRecordCount'] = $recordCount;
  $jTableResult['Records'] = $rows;
  return json_encode($jTableResult);
 }
 
 function createrow($tablename, $dataset, $idname)
 {  
  $this->db->insert($tablename, $dataset, $idname);
    
  $query = $this->db->query("SELECT * FROM " . $tablename . " WHERE ". $idname ." = LAST_INSERT_ID();");
  $row = array();
  foreach($query->result_array() as $ro)
  {
   $row = $ro;
  }

  $jTableResult = array();
  $jTableResult['Result'] = "OK";
  $jTableResult['Record'] = $row;
  return json_encode($jTableResult);
 }
 
 function updaterow($tablename, $dataset, $idname, $id)
 {  
  $this->db->update($tablename, $dataset, $idname . " = " . $id);
  
  $jTableResult = array();
  $jTableResult['Result'] = "OK";
  return json_encode($jTableResult);
 }
 
 function deleterow($tablename, $idname, $id)
 {
  $this->db->delete($tablename, array($idname => $id));

  $jTableResult = array();
  $jTableResult['Result'] = "OK";
  return json_encode($jTableResult);
 } 
}
?>
  7. Create a controller named Jtable (keep J capital): application/controllers/Jtable.php. These controller job is to request data and return in json format.
    <?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Jtable extends CI_Controller 
{ 
 function listrecord()
 {
  $this->load->model('Jtable_model');
  $data = $this->Jtable_model->jtableconf();
  $sorting = $this->input->get('jtSorting');
  $start = $this->input->get('jtStartIndex');
  $size = $this->input->get('jtPageSize');  
  $data = $this->Jtable_model->listtable($data['tablename'], $sorting, $start, $size);
  echo $data;  
 }
 
 function create()
 {
  $this->load->model('Jtable_model');
  $data = $this->Jtable_model->jtableconf();
  $dataset = $this->getset_fields($data, 'create');     
  $data = $this->Jtable_model->createrow($data['tablename'], $dataset, $data['idname']);
  echo $data;  
 }
 
 function update()
 {  
  $this->load->model('Jtable_model');
  $data = $this->Jtable_model->jtableconf();
  $id = $this->input->post($data['idname']);
  $dataset = $this->getset_fields($data, 'edit');   
  $data = $this->Jtable_model->updaterow($data['tablename'], $dataset, $data['idname'], $id);
  echo $data;
 }
 
 function deleterecord()
 {
  $this->load->model('Jtable_model');
  $data = $this->Jtable_model->jtableconf();
  $id = $this->input->post($data['idname']);  
  $data = $this->Jtable_model->deleterow($data['tablename'], $data['idname'], $id);
  echo $data;
 }
 
 function getset_fields($data, $type)
 {
  $dataset = array();
  foreach($data['fields'] as $key => $val) 
  {
   if(!isset($val[$type]))
   {
    $dataset[$key] = $this->input->post($key);
   }
   else
   {
    if( $val[$type] != false)
    {
     $dataset[$key] = $this->input->post($key);
    }
   }
  }
  return $dataset;  
 } 
}
?>
  8. Create a controller application/controllers/View.php 
    <?php
defined('BASEPATH') OR exit('No direct script access allowed');

class View extends CI_Controller 
{ 
 function index()
 {
  $this->load->model('Jtable_model');  
  $data['jtablescript'] = $this->Jtable_model->jtableconf();
  $this->load->view('dataview', $data);
 }
}
?>
  9. Create a view file in application/views/dataview.php. I am currently showing PersonActionsPagedSorted.php method and below is the sample of dataview.php 
    <!DOCTYPE html>
<html>
  <head>
    <link href="/themes/redmond/jquery-ui-1.8.16.custom.css" rel="stylesheet" type="text/css" />
 <link href="/scripts/jtable/themes/lightcolor/blue/jtable.css" rel="stylesheet" type="text/css" />
 
 <script src="/scripts/jquery-1.6.4.min.js" type="text/javascript"></script>
    <script src="/scripts/jquery-ui-1.8.16.custom.min.js" type="text/javascript"></script>
    <script src="/scripts/jtable/jquery.jtable.js" type="text/javascript"></script> 
  </head>
  <body>
 <div id="PeopleTableContainer" style="width: 600px;"></div>
 <pre style="background-color: #f5f5f5; border: 1px solid #ccc;"> 
//This is to verify that the data is correct. Remove it once configured
<?= print_r($jtablescript) ?> 
 </pre>
 <script type="text/javascript">
  $(document).ready(function () {
      //Prepare jTable
   $('#PeopleTableContainer').jtable(<?php echo json_encode($jtablescript); ?>);

   //Load person list from server
   $('#PeopleTableContainer').jtable('load');
  });
 </script>
  </body>
</html&gt

Result:


How to change or modify default table?

The above script have centralized all MySQL and jTable variables in a single array called by function jtableconf(). To modify or change the table, simply alter jtableconf() function in application/models/Jtable_model.php. For example, if you have multiple tables with same columns and want to dislay each on request, you can modify a function like this
jtableconf($tablename)
//...
'tablename' => $tablename
//...
After modify that function, you can call this method from controller View to change table as per requirement.
Thats it! If you are facing error comment down below.
Posted by at
Labels:

1 comment:

Unknown said...

I am working with codeigniter and jtable. Fortunately, I have found your post. But I got a problem with the search bar. I change the model with $this->db->like(); but I could not understand how to reload jtable after post to the search bar. Can you help me please.

September 28, 2016 at 3:44 AM

Post a Comment

Subscribe to: Post Comments (Atom)