Ignited Datatables // php library

numberone

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

numberone

Updated.

allan

Nice one numberone! I've added your two links to the DataTables news feed.

Regards,
Allan

numberone

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.

numberone

I'm done with coding the library, Let me know if there is a bug.

Regards,
Yusuf

numberone

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

wraxou

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

numberone

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]

wraxou

Ah god how stupid am I ... Thanks a lot it works perfectly now !

Bender

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"?

Bender

Master-Detail tables would be great! Maybe two tables on one page, master on the top, details are refresh with master recordset selected.

numberone

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

Bender

Thx Yusuf
I'll give a try :-)

numberone

*PHP5 is required for the library (php5 constructors are used in the lib.)

Regards,
Yusuf

numberone

- 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

jcrawford

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.

jeffrey

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

midjam

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.

midjam

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..
}

midjam

no problem, have sorted it now, great script!

zadro

midjam, what was the fix? can you post?

midjam

[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.

midjam

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

midjam

have solved this also :)

zadro

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.

mike3even

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_sl

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?

heber

Hello I am having problems with accents.
How to solve?
error this á, ã, à...
Sorry if you have any errors in English

ibelar

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.

bikedorkseattle

Hi Yusuf,

Thank you for providing this. Is there any plans to add regex support?

numberone

maybe, i dont know yet..