CodeIgniter with DataTables and Editor

My first post is about how you can use jQuery plugin ‘Editor’, with the CodeIgniter 3 framework.

The brilliant ‘Editor’ jQuery plugin can be found on the developers website https://www.datatables.net.

The Editor plugin is not free. You can download a trial version here. If you want to keep using it, you can purchase it here.

In this post I will show you how you can integrate the ‘Editor’ plugin in your CodeIgniter web application.

In a following post I will show you how you can add extra ‘DataTables’ functionality, like export buttons.

Using ‘DataTables’ without ‘Editor’

If you are looking for a way to use ‘DataTables’ only without the ‘Editor’ plugin, you can have a look at my next post (not there yet). You can use the example code and remove the ‘Editor’ parts. That should work. In the future, I will try to post a ‘DataTables only’ example.

Preconditions

This tutorial is for the ‘JQuery Editor plugin’ and not for the ‘JQuery DataTables plugin’.
You must have purchased the plugin (or use the trial to try it out first)

Intro

We want to use ‘Editor’ as a CodeIgniter library, but the problem is that it isn’t a CodeIgniter library.
So if we want to do something like:

$this->load->library('Editor');

we have to create a library ‘Editor’ ourselves, which incorporates the ‘Editor’ code.

So what are we going to do?

  • We are going to build a CodeIgniter library and we call it ‘EditorLib‘.
  • We are going to build a model ‘StaffModel‘, a view ‘Staff‘ and a controller ‘Staff‘ to test our library
  • We are going to use example ‘Server-side processing’ which can be found here:
    https://editor.datatables.net/examples/simple/server-side-processing.html
    If you go to the tab pages, you will find example code for:

    • The Javascript we are going to use
    • The HTML we are going to use
    • The CSS we are going to use
    • The server script code (PHP code) we are going to use

Okay, let’s go!

Please follow the instructions carefully. We are going to build the example and it is best to start from scratch (unless you know exactly what you are doing. But do not blame me if it does not work).

Install CodeIgniter and prepare it

Dowload and install CodeIgniter. After installation make the following modifications.

Auto load the url helper class. Do this by changing the following line in autoload.php:

$autoload['helper'] = array();

Change it to:

$autoload['helper'] = array('url');

Set the base url by changing the following line in config.php:

$config['base_url'] = '';

Change it to:

$config['base_url'] = 'http://' . $_SERVER['SERVER_NAME'] . '/EditorTest';

Here /EditorTest is the folder in the document root of the web server where you have installed CodeIgniter. If you have installed CodeIgniter in the document root, just change it to

$config['base_url'] = 'http://' . $_SERVER['SERVER_NAME'];

Set the correct URI routes by changing the following line in routes.php:

$route['default_controller'] = 'Welcome'; 

Change it to:

$route['default_controller'] = 'Staff';

Add routes for two controllers we are going to create later. Do this by adding the following lines in routes.php:

$route['Staff'] = 'Staff';
$route['Ajax/Staff'] = 'Ajax/Staff';

Fix the display of url’s

Take care url rewriting works (so you do not have index.php in your URL).
My .htaccess file looks like this:

RewriteEngine on
RewriteCond $1 !^(index\.php|images|robots\.txt|css|js|docs|system)
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L,QSA]

Do not forget to uncommented the mod_rewrite in your Apache configuration file, Do this by changing the following line in httpd.conf:

#LoadModule rewrite_module modules/mod_rewrite.so

Change it to

LoadModule rewrite_module modules/mod_rewrite.so

For these changes to take effect, restart your web server (Apache).

No we can start to create a model, view and a controller, but first we are going to download the ‘Editor’ plugin .

Download the Editor plugin and put it in the libraries folder

Goto https://editor.datatables.net/download/index, login and download “Editor For PHP”.

Extract the zip file in the application/libraries folder.
You now have a folder application/libraries/Editor-PHP-1.5.4 or similar.

Now we are going to move all the css files, images and JavaScript files in folder application/libraries/Editor-PHP-1.5.4 to our own folders. All my css files, images and JavaScript files are in a folder called ‘assets’, which is on the same level as folders ‘application’ and ‘system’
So it looks like this:

- aplication
- assets
- system

Of course you are free to put these files wherever you want as long as your are able to load them correctly in your view.

Create the example database.

In application/libraries/Editor-PHP-1.5.4/examples/sql you find an SQL script you can use to create the example database we are going to use. (For most people this will be mysql.sql.)

Create the controller (Staff.php)

<?php 

defined('BASEPATH') OR exit('No direct script access allowed'); 

class Staff extends CI_Controller 
{ 
	public function index() 
	{ 
		$this->load->view('Staff');
	}
}

Create the view (Staff.php)

<table id="example" class="display" width="100%" cellspacing="0">
   <thead>
      <tr>
         <th>First name</th>
         <th>Last name</th>
         <th>Position</th>
         <th>Office</th>
         <th>Start date</th>
         <th>Salary</th>
      </tr>
   </thead>
   <tfoot>
      <tr>
         <th>First name</th>
         <th>Last name</th>
         <th>Position</th>
         <th>Office</th>
         <th>Start date</th>
         <th>Salary</th>
      </tr>
   </tfoot>
</table>
<script src="//code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/buttons/1.1.0/js/dataTables.buttons.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/select/1.1.0/js/dataTables.select.min.js" type="text/javascript"></script>
<script src="<?php echo base_url(); ?>/assets/js/dataTables.editor.min.js" type="text/javascript"></script>
<script src="<?php echo base_url(); ?>/assets/js/custom.js" type="text/javascript"></script>

The controller and the view do nothing special. The controller loads the view and the view only shows a basic HTML table. The magic is in the JavaScript files. All the JavaScript files were copied from the Editor zip file, except for custom.js.
We are going to create a file called custom.js, which contains the JavaScript code which is used to populate the HTML table. (The code is copied from tab page ‘Javascript’ on https://editor.datatables.net/examples/simple/server-side-processing.html. )

Create custom.js

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        "ajax": "Ajax/Staff",
        "table": "#example",
        "fields": [ {
                "label": "First name:",
                "name": "first_name"
            }, {
                "label": "Last name:",
                "name": "last_name"
            }, {
                "label": "Position:",
                "name": "position"
            }, {
                "label": "Office:",
                "name": "office"
            }, {
                "label": "Extension:",
                "name": "extn"
            }, {
                "label": "Start date:",
                "name": "start_date",
                "type": "datetime"
            }, {
                "label": "Salary:",
                "name": "salary"
            }
        ]
    } );

    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "Ajax/Staff",
            type: "POST"
        },
        serverSide: true,
        columns: [
            { data: "first_name" },
            { data: "last_name" },
            { data: "position" },
            { data: "office" },
            { data: "start_date" },
            { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

We only changed the URLs of the AJAX calls. Because we use CodeIgniter we are going to use the CodeIgniter framework to handle the page request. So we need at least a controller for this.

We create a controller called ‘AJAX‘ which will be responsible for any AJAX request. It needs to have an action (a method) which gives us our data back. We use method name ‘staff‘ to get the data for our HTML table.

Create the controller (Ajax.php)

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

class Ajax extends CI_Controller {
    
	public function staff()
	{	
	    //Load our library EditorLib 
		$this->load->library('EditorLib');
		
		//`Call the process method to process the posted data
		$this->editorlib->process($_POST);
	}
}

We see that the controller uses library ‘EditorLib‘ which is the CodeIgniter library which we have to write. Method process is called to handle the posted data.

It is not really necessary to pass $_POST as an argument to the library. We can access $_POST from everywhere. It is just my personal preference not to access $_POST in my library.

Now we have to create the CodeIgniter library. The library is going to call file DataTables.php which contains all the server side code of the ‘Editor jquery plugin’.
If you look in the code of DataTables.php, you see that 4 things are being done:

  1. The PHP version is checked.
  2. The database settings are loaded. This is done in the included file ‘config.php’.
  3. An auto-loader is created for the ‘Editor’ files. This is done in the included file ‘Bootstrap.php’.
  4. A database connection is being made. This is done at the end of included file ‘Bootstrap.php’.

More on this later. First create the library.

Create the library (‘EditorLib.php’)

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

class EditorLib {
    
    private $CI = null;
    
    function __construct()
    {
        $this->CI = &get_instance();
    }   

	public function process($post)
	{	
	    // DataTables PHP library
		require dirname(__FILE__).'/Editor-PHP-1.5.4/php/DataTables.php';
		
		//Load the model which will give us our data
		$this->CI->load->model('StaffModel');
		
		//Pass the database object to the model
		$this->CI->StaffModel->init($db);
		
		//Let the model produce the data
		$this->CI->StaffModel->getStaff($post);
	}
}

And that is all we have to change! Well not quite true. We have to take care the correct database settings are being used and for that we have to modify file ‘config.php’.

Change file config.php (file ‘Editor-PHP-1.5.4/php/config.php’)

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */
$sql_details = array(
	"type" => "Mysql", // Database type: "Mysql", "Postgres", "Sqlite" or "Sqlserver"
	"user" => "root", // Database user name
	"pass" => "", // Database password
	"host" => "localhost", // Database host
	"port" => "", // Database connection port (can be left empty for default)
	"db"   => "datatables", // Database name
	"dsn"  => "" // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
);

Fill here the connection details in of the database which you created.

Note: If you want, you can load the database settings from the CodeIgniter database configuration file (file ‘application/config/database.php’). It is not really necessary, so for this moment we leave it as it is.

Now that we have correctly loaded all the server side code of the ‘Editor jquery plugin’, we need some code to return the actual data. In CodeIgniter data we normally would call a model to retrieve data, and that is exactly what we are going to do now.
There is however one big difference. Our model is going to use the database object which is created in Bootstrap.php (file ‘application/libraries/editor-php-1.5.4/Bootstrap.php’) and not the Codeigniter database class!

Normally you would use CodIgniter database functionality to retrieve data from your database. This is not possible. The ‘Editor jQuery plugin’ uses it’s own database class and it is seems almost impossible to write a version which uses the CodeIgniter database class.

Is it a problem? I don’t think so. You can use the CodeIgniter database class next to the ‘Editor jquery plugin’ database class without any problem, and if you want, in the same model!

In our libary ‘EditorLib’, the retrieving of the data from our model ‘StaffModel‘ is done with these lines:

		//Load the model which will give us our data
		$this->CI->load->model('StaffModel');
		
		//Pass the database object to the model
		$this->CI->StaffModel->init($db);
		
		//Let the model produce the data
		$this->CI->StaffModel->getStaff($post);

First we load the model, than we pass the database object to the model with method ‘init‘. With method ‘getStaff’ we retrieve the data from the model.

The code for the method ‘getStaff’ is copied from tab page ‘Server script’ on https://editor.datatables.net/examples/simple/server-side-processing.html ). Let’s now create the model.

Create the model (‘StaffModel.php’)

<?php

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
    
class StaffModel extends CI_Model 
{
    private $editorDb = null;
    
    public function init($editorDb)
    {
        $this->editorDb = $editorDb;
    }
    
    public function getStaff($post)
    {
		// Build our Editor instance and process the data coming from _POST
		// Use the Editor database class
		Editor::inst( $this->editorDb, 'datatables_demo' )
		->fields(
		    Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
		    Field::inst( 'last_name' )->validator( 'Validate::notEmpty' ),
		    Field::inst( 'position' ),
		    Field::inst( 'email' ),
		    Field::inst( 'office' ),
		    Field::inst( 'extn' ),
		    Field::inst( 'age' )
		    ->validator( 'Validate::numeric' )
		    ->setFormatter( 'Format::ifEmpty', null ),
		    Field::inst( 'salary' )
		    ->validator( 'Validate::numeric' )
		    ->setFormatter( 'Format::ifEmpty', null ),
		    Field::inst( 'start_date' )
		    ->validator( 'Validate::dateFormat', array(
		        "format"  => Format::DATE_ISO_8601,
		        "message" => "Please enter a date in the format yyyy-mm-dd"
		    ) )
		    ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
		    ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )
		)
		->process( $post )
		->json();    
    }
}

– That should be it. Try to call http://localhost/EditorTest or http://localhost depending on where you have put your CodeIgniter site.

– Now remove the folders you do not need. Remove folders ‘css’, ‘examples’, ‘images’ and ‘js’ from the library folder (folder ‘application/libraries/Editor-PHP-1.5.4’)

I have chosen to move all the files in folders ‘css’, ‘examples’, ‘images’ and ‘js’ which are part of the jQuery plugin ‘Editor’ to another location. It is better NOT to move them and just leave them in the folders they came in. In my second post I will correct this.

EXTRA
(only required if you want to see that you still can use CodeIgniters own database class)

To check if we can still use the CodeIgniter database class without any problem, we create an extra controller ‘DatatableTest’.
In the method ‘getStaffMember’ it calls on StaffModel, we will use the CodeIgniter database class, to proof both database classes can exist next to each other.

First Auto load the database library. Do this by changing the following line in autoload.php:

$autoload['libraries'] = array();

Change it to:

$autoload['libraries'] = array('database');

Create the controller ‘DatatableTest’

<!--?php defined('BASEPATH') OR exit('No direct script access allowed');
 
class DatabaseTest extends CI_Controller 
{ 
  public function index() 
  { 
    $this->load->model('StaffModel');
    $data = $this->StaffModel->getStaffMember(5);
    var_dump($data); //Just dump the data. No need to make a view just to proof it works.
  }
}

Add a constructor to model ‘StaffModel’ and create method ‘getStaffMember’

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

public function getStaffMember($id)
{
  if($id != false)
  {
    //Use the CodeIgniter database class
    $query = $this->db->get_where('users', array('id' => $id));
    return $query->row_array();
  }
  return false;
}

When you now call http://localhost/yourFolderName/DatabaseTest or http://localhost/DatabaseTest depending on where you have put your CodeIgniter site, you should see:

array (size=15)
  'id' => string '5' (length=1)
  'title' => string 'Miss' (length=4)
  'first_name' => string 'Kamal' (length=5)
  'last_name' => string 'Roberson' (length=8)
  'phone' => string '1-134-408-5227' (length=14)
  'city' => string 'Rehoboth Beach' (length=14)
  'zip' => string 'V7I 6T5' (length=7)
  'updated_date' => string '2015-12-12 13:52:05' (length=19)
  'registered_date' => string '2012-12-23 00:17:03' (length=19)
  'active' => string '1' (length=1)
  'manager' => string '1' (length=1)
  'site' => string '5' (length=1)
  'image' => null
  'shift_start' => string '09:00:00' (length=8)
  'shift_end' => string '17:00:00' (length=8)

It seems to work!

Download

The files created in this tutorial can be downloaded, but you are encouraged to create all files in this tutorial manually. If it doesn’t work or something is not clear, you can have a look at the downloaded files.

You can download the files here. here: EditorTest.zip.
Just extract it in the document root of the web server or in a subfolder of your choice.

Note: It does contain the CodeIgniter 3.03 framework, but NOT the jQuery plugin ‘Editor’, NOR the jQuery plugin ‘DataTables’.

Leave a Reply