DataTables logo DataTables

Ignited Datatables // php library
  • Hello guys,

    Some of you guys might heard about Ignited Datatables. A wrapper class/library for Codeigniter.
    ( codeigniter version :https://github.com/IgnitedDatatables/Ignited-Datatables)

    Here is the standalone php version of the library that has no dependency on Codeigniter. Usage is very similar.
    (You can choose db driver: mysql and mysqli is supported at the moment. im planning on adding more)
    https://github.com/n1crack/IgnitedDatatables-native-php-version

    FEATURES:
    1. Easy to use. Generates json using only a few lines of code.
    2. Support for table joins (left, right, outer, inner, left outer, right outer).
    3. Able to define custom columns, and filters.
    4. Editable custom variables with callback function support.
    5. Supports generation of tables using non utf-8 charsets.
    6. mDataprop support!
    7. Auto detects HTTP method (POST or GET) //only native php version

    Available function list :
    https://github.com/IgnitedDatatables/Ignited-Datatables/wiki/Function-Reference

    This library makes server-side scripting easier and more flexible!

    You can download the db (sakila) for examples from: http://dev.mysql.com/doc/index-other.html

    Regards,
    Yusuf
  • Updated.
  • Nice one numberone! I've added your two links to the DataTables news feed.

    Regards,
    Allan
  • Thanks Allan,

    I've updated github just now, added mDataprop support.

    now It detects mDataprop and returns an array of objects instead of array of arrays in json output. there is no need to change anything else.
  • I'm done with coding the library, Let me know if there is a bug.

    Regards,
    Yusuf
  • Hi all,
    Added more examples to github with a few slight fixes.
    https://github.com/n1crack/IgnitedDatatables-native-php-version

    I would love to hear your feedbacks and suggestions..

    Regards,
    Yusuf
  • Hi numberone !

    I have been playing around with your plugin (which works pretty well so far !) but I'm stuck with a small problem.

    Basically, I'm retrieving dates from one of my table and I would like to convert them to a UK format (DD/MM/YY). I already have a function I wrote in php. What I've tried to do is to edit a column as you did in one of your examples (callback_function) :

    ->edit_column('date', '$1', 'USAToUKDate(date)')

    Is it possible to use own made PHP functions in order to manipulate data ?

    Regards
  • Yes you can use your own functions as well. add 'callback_' just before your function.
    ->edit_column('date', '$1', 'callback_USAToUKDate(date)');
    

    And be sure your function is accessible in your ajax file.

    for example :
    <?php 
    require_once('../../Datatables.php');
    $datatables = new Datatables();  // for mysqli  =>  $datatables = new Datatables('mysqli'); 
    
    // MYSQL configuration
    $config = array(
    'username' => 'root',
    'password' => '',
    'database' => 'sakila',
    'hostname' => 'localhost');
    
    $datatables->connect($config);
    
    $datatables
    ->select('film_id, title, release_year, length, rating')
    ->from('film')
    ->edit_column('release_year', '$1', 'callback_test(film_id, release_year)');
    
    echo $datatables->generate();
    
    function test($id, $year) { 
      return $year . '_' . $id;	// whatever you want..
    }
    
    ?>
  • Ah god how stupid am I ... Thanks a lot it works perfectly now !
  • Excellent add-on!!

    How do I add an "if-then-else" statement based on a database value?

    Like: "if recordset field MemberActive = 1 show image1 else show image2"?
  • Master-Detail tables would be great! Maybe two tables on one page, master on the top, details are refresh with master recordset selected.
  • Hi Bender,

    i would prefer doing those if-else statements in javascript. But of course you can do it with a callback function like :
    $datatables
    ->select('id, name, memberactive')
    ->from('members')
    ->edit_column('name', '<img src="$2"> $1', 'name, callback_get_imglink(memberactive)');
    

    and your callback function :
    function get_imglink($active) { 
      return ($active == 1)? 'image link 1' : 'image link 2';
    }
    

    About Master-Detail tables.. There is a range-filtering example at github. https://github.com/n1crack/IgnitedDatatables-native-php-version/tree/master/examples/range_filtering
    You can get the idea.

    Regards,
    Yusuf
  • Thx Yusuf
    I'll give a try :-)
  • *PHP5 is required for the library (php5 constructors are used in the lib.)

    Regards,
    Yusuf
  • - added "Individual Column Filtering" support for http://www.datatables.net/examples/api/multi_filter.html

    - added filter() method. (its usage like where() method but it filters the data and adds "(filtered from xxx total entries)" to datatables. )

    some online examples :
    http://numberone.kodingen.com/datatables/examples/basic_init/
    http://numberone.kodingen.com/datatables/examples/range_filtering/ //range filtering with where
    http://numberone.kodingen.com/datatables/examples/range_filtering2/ //range filtering with filter
    http://numberone.kodingen.com/datatables/examples/range_filtering_date/
    http://numberone.kodingen.com/datatables/examples/multi_filtering/

    BIRT db for examples on kodingen :
    http://www.eclipse.org/birt/phoenix/db/#mysql

    --edit
    0.5.4 Changes :
    - removed 'callback_' prefix from callback functions
    - revised callback functions parameters
    --edit
    0.6 Changes :
    - includes some bug fixes and support for non utf-8 charsets

    - improved functionality for individual column filtering in v0.6
    # usage:
    # oTable.fnFilter( ">=10, <=36", column_index_number ); makes range filtering
    # oTable.fnFilter( ">10", column_index_number );
    # oTable.fnFilter( "some, text", column_index_number );

    # example:
    # http://numberone.kodingen.com/datatables/examples/range_filtering3
  • The only suggestion I would have after looking at this library is maybe a way to incorporate MemcacheD so that you could have some large datasets cached so that you are not going to the DB with every request.
  • I've setup a server-side processing data table using ignited... but I'd like to change the search to work like the non server-side searches work... allowing multiple terms in the table search. I found a page that'll do this for Allen's server processing script (see the link below), but can't find a solution using ignited. Is there one?

    http://datatables.net/forums/discussion/3343/server-side-processing-and-regex-search-filter/p1
  • wow this is great, was about to give up on DataTables due to my JOINS not working, now they do. thanks alot. I`m a little stuck on getting my date field converted to UK format, in the old script I had this:
    		"aoColumns" : [
    		{"bVisible": false, "bSortable": false, "bSearchable": false},		
    	    {"aaSorting": [[2,'desc']], "fnRender": format_ddmmyyyy},
    		null, 
    		null,
    		null,
            null,
    		{"bSortable": false, "bSearchable": false},
    		{"bSortable": false, "bSearchable": false},
    		{"bSortable": false, "bSearchable": false}]
    	  });
    });
    
    
    
    // Take date from mysql, formatted yyyy-mm-dd, and return as dd/mm/yyyy
    function format_ddmmyyyy(oObj) {
      var sValue = oObj.aData[oObj.iDataColumn]; 
      var aDate = sValue.split('-');
      return aDate[2] + "/" + aDate[1] + "/" + aDate[0];
    }
    
    

    I guess I need to create a function but, have no idea where to start as I`m still learning php / mysql.
  • Have tried the following but, not working am I close?

    ->edit_column('date_time', '$1', 'callback_test(leads.lead_id, date_time)')

    function test($id, $date) {
    $date = date('d/m/Y', strtotime($yourtimestring));
    return $date; // whatever you want..
    }
  • no problem, have sorted it now, great script!
  • midjam, what was the fix? can you post?
  • $(document).ready(function()
      {
        var oTable = $('#example').dataTable
        ({
    		  'bProcessing': true,
    		  'aaSorting': [[1,'asc']], // sorts date by default.
    		  'iDisplayLength': 10,
    		  'bJQueryUI': true,
    		  'bServerSide': true,
    		  'sAjaxSource': 'ajax/pc-ajax-table.php',
    		  'fnServerData': function(sSource, aoData, fnCallback)
          {
    
              aoData.push( { "name": "from_date", "value": $( "#from" ).val() },
                           { "name": "to_date", "value": $( "#to" ).val() } );
            $.ajax
                ({
                  'dataType': 'json',
                  'type'    : 'POST',
                  'url'     : sSource,
                  'data'    : aoData,
                  'success' : fnCallback
                });
          },
    	  	   'aoColumns':[
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},		
    	      {"fnRender": format_ddmmyyyy}, // renders the date as dd/mm/yyyy
    		  null, 
    		  null,
    		  null,
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},
              {"bVisible": false, "bSortable": false, "bSearchable": true},
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},
    		  null,
    		  null,
    		  null,
    		  null,
    		  null]
        });
    });
    // Take date from mysql, formatted yyyy-mm-dd, and return as dd/mm/yyyy
    function format_ddmmyyyy(oObj) {
      var sValue = oObj.aData[oObj.iDataColumn]; 
      var aDate = sValue.split('-');
      return aDate[2] + "/" + aDate[1] + "/" + aDate[0];
    }
    
    good luck buddy :)

    I`m now stuck on creating either a show/hide row details or, adding a fancybox for more details. I have looked around this forum but, have not found any examples which work in my case. Does anyone know of any good examples? Something similar to the notes link here: http://www.datatables.net/download/ would be great.
  • Have tried adding the code here: http://www.datatables.net/blog/Drill-down_rows but, nothing happens when I click the details_open button.

    $(document).ready(function()
      {
    	var anOpen = [];
        var oTable = $('#example').dataTable
        ({
    		  'bProcessing': true,
    		  'aaSorting': [[1,'asc']], // sorts date by default.
    		  'iDisplayLength': 10,
    		  'bJQueryUI': true,
    		  'bServerSide': true,
    		  'sAjaxSource': 'ajax/pc-ajax-table.php',
    		  'fnServerData': function(sSource, aoData, fnCallback)
          {
    
              aoData.push( { "name": "from_date", "value": $( "#from" ).val() },
                           { "name": "to_date", "value": $( "#to" ).val() } );
            $.ajax
                ({
                  'dataType': 'json',
                  'type'    : 'POST',
                  'url'     : sSource,
                  'data'    : aoData,
                  'success' : fnCallback
                });
          },
    	  	   'aoColumns':[
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},	
    	      {"fnRender": format_ddmmyyyy}, // renders the date as dd/mm/yyyy
    		  null, 
    		  null,
    		  null,
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},
              {"bVisible": false, "bSortable": false, "bSearchable": true},
    		  {"bVisible": false, "bSortable": false, "bSearchable": true},
    		  null,
    		  null,
    		  null,
    		  null,
    		  null]
        });
    	// for adding a details box
    $('#example td.control').live('click', function () {
       var nTr = this.parentNode;
       var i = $.inArray( nTr, anOpen );
        
       if ( i === -1 ) {
          $('img', this).attr( 'src', "../images/details_close.png" );
          oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' );
          anOpen.push( nTr );
        }
        else {
          $('img', this).attr( 'src', "../images/details_open.png" );
          oTable.fnClose( nTr );
          anOpen.splice( i, 1 );
        }
    } );
     
    function fnFormatDetails( oTable, nTr )
    {
      var oData = oTable.fnGetData( nTr );
      var sOut =
        '<div class="innerDetails">'+
          '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
            '<tr><td>Rendering engine:</td><td>'+oData.engine+'</td></tr>'+
            '<tr><td>Browser:</td><td>'+oData.browser+'</td></tr>'+
            '<tr><td>Platform:</td><td>'+oData.platform+'</td></tr>'+
            '<tr><td>Version:</td><td>'+oData.version+'</td></tr>'+
            '<tr><td>Grade:</td><td>'+oData.grade+'</td></tr>'+
          '</table>'+
        '</div>';
      return sOut;
    }
    
    

    Code for generating the image:

    ->add_column('moreinfo', '<span style="cursor:pointer"><img src="../images/details_open.png" alt="img" class="img" name="img"/></span>', 'leads.lead_id')

    Code
  • have solved this also :)
  • Thanks midjam for posting..very nice implementation and explanation!

    I solved it using server side (with helper file):
    http://codeigniter.com/forums/viewthread/160896/P280/

    I'm sure I'll come back to this thread to add_column for extra image column and function.
  • Thank you! I am really loving the native PHP version.

    A couple questions:

    1) Does it support MySQL SUM(), COUNT() w/ GROUP BY functions? Any examples?

    2) What about string functions such as LEFT, RIGHT and TRIM?
  • Looks like people aren't really posting here much, but I'm gonna give this a try since I'm kinda stuck. I would like to use this library to create the equivalent of the following SQL:
    SELECT lists.id,lists.name,lists.capacity,
      (SELECT COUNT(*) FROM subscriptions WHERE subscriptions.listid=lists.id) AS subcount)
    FROM lists
    WHERE lists.ownerid=1
    

    The "sql_subqueries" demos folder got my hopes up, but my SQL is rusty enough that it doesn't seem to be doing what I'm trying to do, and didn't yield the SQL I'm aiming for. Is this query possible with this library?
  • Hello I am having problems with accents.
    How to solve?
    error this á, ã, à...
    Sorry if you have any errors in English
  • heber;

    I also had the same problem with french accent. In order to solve this, I had to set the character code for the db connection to the proper one use by the database, in my case 'UTF-8' using mysql_set_charset().

        protected function db_connect()
        {
          if ($this->port != '')
            $this->hostname .= ':'.$this->port;
    
          $this->db = @mysql_connect($this->hostname, $this->username, $this->password, TRUE);
          mysql_set_charset('utf8', $this->db); // Change connection character set to UTF-8
        }
    

    The change was made in the mysql.php file within the Active Records folder at the db_connect() function. Not sure if this is exactly the right place but it is working for me.
  • Hi Yusuf,

    Thank you for providing this. Is there any plans to add regex support?
  • maybe, i dont know yet..
  • Hi numberone,

    Is there a workaround for the aLengthMenu option for displaying all compounds? I originally had it as
    "aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
    But when the user selects "Show All" it only shows the default selection

    Thanks
    --Nick
  • Hello, I have a select sum() in datatables with ajax, but when I search any work, show me the mysql erro : Invalid use of group function

    Have support for SUM with datatables in ajax ?

    Thanks in advanced.
  • That's an SQL question for an SQL forum. You'll get better answers to SQL questions in an SQL forum, rather than this which is specifically for the client-side DataTables library.

    Allan
  • Can you please post the example for the custom filters. In the zip file there is the sql joint instead. Thx.
  • Hi guys, I wonder if the sColumns showing in the JSON cannot be considered as a security issue once we are giving this information "for free" which is exactly the MySQL field names.

    IMHO if we make things as difficult as we can to avoid data hijacking or other kinds or threat, the better. Is that something we have to concern about?
  • ->edit_column('post_date','$1','callback_substr(post_content)')
    ->edit_column('post_date','$1','callback_formatDate(post_date)')

    not runing with datatable library. What is the fault?
  • Hi @yusuef, Please remove "callback_" prefix according to http://ellislab.com/forums/viewthread/160896/P405.
  • Wow, awesome job :)

    Have you planed to add the support for postgreSql ?

    Otherwise, do you have a workaround to give, so I could start the work since I'm using pgsql with codeigniter, I will need this !

    Thanks a lot
  • I have created a small tutorial for how to use ignited datatables, hope it helps you
    http://www.ahmed-samy.com/codeigniter-full-featrued-jquery-datatables/
  • Hi
    Could someone explain to me the workings of the following piece of code:
    ->add('edit', '<a href="#$1" title="Edit:$2 $3">Edit</a>', 'customer_id, first_name, last_name')
    what do the $1 $2 $3 in the string stand for.
    I'm trying to use this line for a link to go off and edit a record on another page which would be filtered by lets say the customer_id, but I am not having much joy.
    I've tried changing the href to <a href="edit_customer.php?$1"
    I can see the customer id being sent in the address bar, but a dump of the variables on the landing page gives an array like array(0) { } .

    Any ideas

    David
  • i can explain
    ->add('edit', '<a href="#$1" title="Edit:$2 $3">Edit</a>', 'customer_id, first_name, last_name')

    the format is ;
    ->add($columns, $newstring, $replaces)
    $1 refers to customer_id, $2 first_name and $3 is last_name.


    ->add('edit', '<a href="edit_customer.php?$1">Edit</a>', 'customer_id')

    this should work. customer_id must be selected with $datatables->select(). otherwise it can't replace it.
  • Thank's for that, I will give it a go and report back.
    David
  • Thank's for that, it worked well.
    David
  • Got it to work with the following method
    ->add('edit', '<a href="pay_invoice.php?inv=$1" title="Edit:$2 $3">pay</a>', 'purchase_invoice_id');
    shows http://192.168.1.81/cloudone/pay_invoice.php?inv=3 in the address bar.

    so alls good, thank you for your help, are you able to point me in the direction of an example on how to format the following two items, returned date like 2013-04-14 to display like 14-04-2013 04 or 14/04/2013 in keeping with the UK and 23.00 to £23.00.
    Thank you

    David
  • Hi
    I tried your advice, however, I could not get a named pair on the receiving page, I set it up in the following manner for the finnish.
    ->add('edit', '<a href="pay_invoice.php?inv=$1" title="Edit:$2 $3">pay</a>', 'purchase_invoice_id');

    this gives the following in the receiving address bar
    http://192.168.1.81/cloudone/pay_invoice.php?inv=2.[/code]

    Whilst I am here, can you point me in the right direction to format the columns for Uk currency and Date.

    Any assistance or pointers appreciated.

    David
  • Ok, I am trying out this plugin. I have data in json format coming back from the database. Table renders correctly in the webpage, but I can not seem to get the data to render in the table. I am using the latest CI, jQuery, the latest datatables.jsa and the CI datatables library. Using a localhost of XAMPP.
    $(document).ready(function () {
    	var oTable = $('#big_table').dataTable({
    		"bProcessing": true,
    		"bServerSide": true,
    		"sAjaxSource": 'index.php/home/datatable',
    		"bJQueryUI": true,
    		"sPaginationType": 'full_numbers',
    		"iDisplayStart": 20,
    		"oLanguage": {
    			"sProcessing": "<img src='assets/img/ajax-loader_dark.gif' />"
    			},
    		"fnInitComplete": function () {
    			//oTable.fnAdjustColumnSizing();
    			},
    		"fnServerData": function (sSource, aoData, fnCallback) {
    			$.ajax({
    				'dataType': 'json',
    				'type': 'get',
    				'url': sSource,
    				'data': aoData,
    				'success': fnCallback
    				});
    			console.log(fnCallback);
    			}
    		});
    });
    

    here is the controller
    class Home extends CI_Controller {
    
    	function  __construct() {
    		parent::__construct();
    		$this->load->library('Datatables');
            $this->load->library('table');
            //$this->load->database();
    		}
    
    	public function index() {
    		//$this->load->library('table');
    		$tmpl = array ( 'table_open'  => '<table id="big_table" border="1" cellpadding="2" cellspacing="1" class="mytable">' );
    		$this->table->set_template($tmpl);
    		$this->table->set_heading('First Name','Last Name','Email');
    		$this->load->view('default'); //subscriber_view
    		}
    	//function to handle callbacks
    	function datatable() {
    		$this->datatables->select('first,last,email')
    			->unset_column('id')
    			->from('subscriber');
    		echo $this->datatables->generate();
    		}
    
    }
    

    I found a simple tutorial/example online here: http://www.ahmed-samy.com/php-codeigniter-full-featrued-jquery-datatables-part-1/

    I have a feeling that my problem lies with the success method inside the ajax call. i know it has to be some newbie mistake that i am making. just cannot see it.

    tony
  • @upshire

    ->edit('someprice', '£$1', 'someprice') /// simple replace

    ->edit('some_column' , '$1', 'time()') // replace current timestamp.
    // ->edit('some_column' , '$1', "strtotime('some_column')") // or convert some_column value to timestamp
    ->edit('some_column' , '$1', "date('l jS \of F Y h:i:s A', some_column)") // format and replace.


    @energetic_pixels

    $this->datatables->select('first,last,email')
    ->unset_column('id')
    ->from('subscriber');
    echo $this->datatables->generate();

    here is the problem.. "->unset_column('id')" you should first select the 'id' column, then u are able to unset it.


    $this->datatables->select('first,last,email')
    ->from('subscriber');

    and another problem is ;

    $.ajax({
    'dataType': 'json',
    'type': 'get',
    'url': sSource,
    'data': aoData,
    'success': fnCallback
    });

    type should be 'post'


    Regards,
    Yusuf
  • Yusuf,
    Thanks for the reply. I understand now about that unset_column method. i commented it out.
    As for the $.ajax type?? Every time I change it to a POST, I get a Error Code 500 from the XAMPP. It points back to my home/datatable ci function and it says that the action I have requested is not allowed. Why would this happen with POST and not GET??

    Tony
  • Thank's Yusuf

    I will give it a try and report back,your willingness to help the less experienced is much appreciated.
    Dave
  • Where is the native PHP version (non-CodeIgniter)?
    https://github.com/n1crack/IgnitedDatatables-native-php-version throws a 404.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Support

Get useful and friendly help straight from the source.