Ignited Datatables // php library

Ignited Datatables // php library

edited September 2012 in General Posts: 71
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
«13

Replies

  • Posts: 71
    Updated.
  • Posts: 21,952
    Nice one numberone! I've added your two links to the DataTables news feed.

    Regards,
    Allan
  • Posts: 71
    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.
  • Posts: 71
    I'm done with coding the library, Let me know if there is a bug.

    Regards,
    Yusuf
  • edited July 2011 Posts: 71
    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
  • Posts: 3
    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
  • edited July 2011 Posts: 71
    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.. } ?>
  • Posts: 3
    Ah god how stupid am I ... Thanks a lot it works perfectly now !
  • Posts: 3
    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"?
  • Posts: 3
    Master-Detail tables would be great! Maybe two tables on one page, master on the top, details are refresh with master recordset selected.
  • Posts: 71
    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
  • Posts: 3
    Thx Yusuf
    I'll give a try :-)
  • edited July 2011 Posts: 71
    *PHP5 is required for the library (php5 constructors are used in the lib.)

    Regards,
    Yusuf
  • edited August 2011 Posts: 71
    - 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.
  • edited October 2011 Posts: 16
    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
  • Posts: 13
    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.
  • Posts: 13
    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..
    }
  • Posts: 13
    no problem, have sorted it now, great script!
  • Posts: 12
    midjam, what was the fix? can you post?
  • Posts: 13
    $(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.
  • edited November 2011 Posts: 13
    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
  • Posts: 13
    have solved this also :)
  • edited November 2011 Posts: 12
    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?
  • Posts: 1
    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?
  • Posts: 1
    Hello I am having problems with accents.
    How to solve?
    error this á, ã, à...
    Sorry if you have any errors in English
  • Posts: 1
    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..
Sign In or Register to comment.