Ignited Datatables // php library

Ignited Datatables // php library

numberonenumberone Posts: 86Questions: 0Answers: 0
edited September 2012 in General
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

  • numberonenumberone Posts: 86Questions: 0Answers: 0
    Updated.
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Nice one numberone! I've added your two links to the DataTables news feed.

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

    Regards,
    Yusuf
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    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
  • wraxouwraxou Posts: 3Questions: 0Answers: 0
    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) :

    [code]->edit_column('date', '$1', 'USAToUKDate(date)')[/code]

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

    Regards
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    Yes you can use your own functions as well. add 'callback_' just before your function.
    [code]
    ->edit_column('date', '$1', 'callback_USAToUKDate(date)');
    [/code]

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

    for example :
    [code]
    <?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..
    }

    ?>[/code]
  • wraxouwraxou Posts: 3Questions: 0Answers: 0
    Ah god how stupid am I ... Thanks a lot it works perfectly now !
  • BenderBender Posts: 3Questions: 0Answers: 0
    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"?
  • BenderBender Posts: 3Questions: 0Answers: 0
    Master-Detail tables would be great! Maybe two tables on one page, master on the top, details are refresh with master recordset selected.
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    Hi Bender,

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

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

    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
  • BenderBender Posts: 3Questions: 0Answers: 0
    Thx Yusuf
    I'll give a try :-)
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    *PHP5 is required for the library (php5 constructors are used in the lib.)

    Regards,
    Yusuf
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited August 2011
    - 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
  • jcrawfordjcrawford Posts: 172Questions: 0Answers: 0
    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.
  • jeffreyjeffrey Posts: 16Questions: 0Answers: 0
    edited October 2011
    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
  • midjammidjam Posts: 13Questions: 0Answers: 0
    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:
    [code]
    "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];
    }

    [/code]

    I guess I need to create a function but, have no idea where to start as I`m still learning php / mysql.
  • midjammidjam Posts: 13Questions: 0Answers: 0
    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..
    }
  • midjammidjam Posts: 13Questions: 0Answers: 0
    no problem, have sorted it now, great script!
  • zadrozadro Posts: 12Questions: 0Answers: 0
    midjam, what was the fix? can you post?
  • midjammidjam Posts: 13Questions: 0Answers: 0
    [code]
    $(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];
    }
    [/code]
    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.
  • midjammidjam Posts: 13Questions: 0Answers: 0
    edited November 2011
    Have tried adding the code here: http://www.datatables.net/blog/Drill-down_rows but, nothing happens when I click the details_open button.

    [code]
    $(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 =
    ''+
    ''+
    'Rendering engine:'+oData.engine+''+
    'Browser:'+oData.browser+''+
    'Platform:'+oData.platform+''+
    'Version:'+oData.version+''+
    'Grade:'+oData.grade+''+
    ''+
    '';
    return sOut;
    }

    [/code]

    Code for generating the image:

    [code]->add_column('moreinfo', '', 'leads.lead_id')[/code]

    Code
  • midjammidjam Posts: 13Questions: 0Answers: 0
    have solved this also :)
  • zadrozadro Posts: 12Questions: 0Answers: 0
    edited November 2011
    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.
  • mike3evenmike3even Posts: 1Questions: 0Answers: 0
    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?
  • dts_sldts_sl Posts: 5Questions: 2Answers: 0
    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:
    [code]
    SELECT lists.id,lists.name,lists.capacity,
    (SELECT COUNT(*) FROM subscriptions WHERE subscriptions.listid=lists.id) AS subcount)
    FROM lists
    WHERE lists.ownerid=1
    [/code]

    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?
  • heberheber Posts: 1Questions: 0Answers: 0
    Hello I am having problems with accents.
    How to solve?
    error this á, ã, à...
    Sorry if you have any errors in English
  • ibelaribelar Posts: 1Questions: 0Answers: 0
    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().

    [code]
    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
    }
    [/code]

    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.
  • bikedorkseattlebikedorkseattle Posts: 20Questions: 1Answers: 0
    Hi Yusuf,

    Thank you for providing this. Is there any plans to add regex support?
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    maybe, i dont know yet..
This discussion has been closed.