PHP Class to easily use DataTables 1.10

RobinDev

Hello there,

Today, I share a class to easily use DataTables in PHP (or Github).

What can it do ?

• Generate the javascript part
• Generate the Html part
• Analyze the request and give you the SQL request to execute
• Send the json output from the queries's results that you execute

Specificity : I use the unofficial plugin jquery.dataTables.columnFilter.js that I corrected to be functionnal with the last version of DataTables. But I just see there is a best compatible plugin so I will move.

To understand the logic, there is a little example (extract from the folder examples/) :

<?php
include '../DataTable.php';
use rOpenDev\DataTablesPHP\DataTable;

$columns = array(
    array('title'=>'Rendering Engine'),
    array('title'=>'Browser'),
    array('title'=>'Platform'),
    array('title'=>'Engine Version', 'class'=>'right'),
    array('title'=>'Css Grade', 'class'=>'right')
);

$data = array(
    array('Trident', 'Internet Explorer 4.0', 'Win 95+', '5', 'c'),
    array('Trident', 'Internet Explorer 4.0', 'Win 95+', '5', 'c'),
    array('Trident', 'Internet Explorer 5.0', 'Win 99+', '6', 'd'),
    array('Trident', 'Internet Explorer 5.0', 'Win 99+', '6', 'd'),
    array('Trident', 'Internet Explorer 6.0', 'Win 98+', '4', 'e'),
    array('Trident', 'Internet Explorer 6.0', 'Win 98+', '4', 'e'),
    array('Trident', 'Internet Explorer 7.0', 'Win 97+', '3', 'f'),
    array('Trident', 'Internet Explorer 7.0', 'Win 97+', '3', 'f'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 14.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 14.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 14.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 13.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 13.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 13.10', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 12.04', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 12.04', '2', 'c'),
    array('Gecko', 'Mozilla Firefox 27.0', 'Ubuntu 12.04', '2', 'c')
);

// You can use Plug-in too : http://datatables.net/plug-ins/i18n/#How-to-use
$language = array(
    'emptyTable' => 'Aucune donnée à afficher',
    'info' => '_START_ à _END_ sur _TOTAL_ lignes',
    'infoEmpty' => '(Aucune donnée à afficher)',
    'infoFiltered' => '(filtré de _MAX_ éléments au total)',
    'infoPostFix' => '',
    'thousands' => ' ',
    'lengthMenu' => '_MENU_ lignes par page',
    'loadingRecords' => 'Chargement en cours...',
    'processing' => 'Traitement en cours...',
    'search' => 'Rechercher&nbsp;:',
    'zeroRecords' => 'Aucune donnée à afficher.',
    'paginate' => array(
        'first'   =>'Premier',
        'last'    =>'Dernier',
        'next'    =>'Suivant',
        'previous'=>'Précédent'
    ),
    'aria' => array(
        'sortAscending'  => ': activer pour trier la colonne par ordre croissant',
        'sortDescending' => ': activer pour trier la colonne par ordre décroissant'
    )
);

$dataTable = DataTable::instance('exmample');
$dataTable->setJsInitParameter('language', $language)
          ->setColumns($columns)
          ->setColumnFilterActive()
          ->setData($data);

?>
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.1.1-rc2/jquery.min.js"></script>
<link href="//cdn.datatables.net/1.10.0-rc.1/css/jquery.dataTables.css" rel="stylesheet">
<script src="//cdn.datatables.net/1.10.0-rc.1/js/jquery.dataTables.js"></script>
<script src="https://raw.githubusercontent.com/RobinDev/jquery.dataTables.columnFilter.js/master/jquery.dataTables.columnFilter.js"></script>
<script>
$(document).ready(function() {
    <?php echo $dataTable->getJavascript(); ?>
});
</script>
<?php echo $dataTable->getHtml(); ?>

Don't hesitate to send me feedback or issues here or on Github.

allan

This is excellent - thanks for sharing this with us! I look forward to seeing your script develop :-)

Regards,
Allan

tangerine

@RobinDev: I'm trying your class and I have some questions. Are you offering support?

tangerine

@RobinDev: Some debugging is needed.

function filter () {

$column = $this->columns[$i];

// At this point, $this->columns is an array of stdClass objects.

// So the following line needs changing:

// if (!isset($column['searchable']) || $column['searchable'] === true) {

// Should be:

if (!isset($column->searchable) || $column->searchable === true) {

RobinDev

Hello Tangerine,

It was the first draw, today I push a more stable version, updated (a little) the documentation and add new features (complexe headers, Join...)... I am more reactive on Github, don't hesitate to open an issue.

/!\ I ever use it now in prod but it's still an alpha (some functions could be renamed in the future).

Are you offering support?

Yes, you can post or email-me.

Thank you for your corrections :

• For the column, I (re)Switch to a php table wich is converted only for the Js

• For the debugging part, can you be more explicit ? How are you seeing the debugging of this var ?

kqueekquee

i have this error
Parse error: syntax error, unexpected '[' in C:\www-php\agp\version\agp-141\commun\plugins\DataTablesPHP\DataTable.php on line 88
$objectize = is_string(array_keys($data[0])[0]) ? true : false;
après correction ok
$objectize = is_string(array_keys($data[0])) ? true : false;

Christian

tangerine

@RobinDev:
I just edited my "debugging" post slightly - "function filter() {" should have a line to itself.

The error was:

ErrorException [ 1 ]: Cannot use object of type stdClass as array ~ APPPATH\classes\datatable.php [ 319 ]

(it's from Kohana framework).

So this line will fail:

if (!isset($column['searchable']) || $column['searchable'] === true) {

Thanks for your help.

tangerine

Another thing :-)

From function setData:

$objectize = is_string(array_keys($data[0])[0]) ? true : false;

PHP 5.3.8 does not recognise that syntax:

array_keys($data[0])[0]

I can work around it like this:

$k = array_keys($data[0]) ;

$objectize = is_string($k[0]) ? true : false;

RobinDev

@kqueekquee See the last @tangerine's anwer

PHP 5.3.8 does not recognise that syntax

So replace the line by :

$k = array_keys($data[0]) ;
$objectize = is_string($k[0]) ? true : false;

I update the class... It's compatible with PHP 5.3 now.

---

@tangerine : Grab the new file (or composer update), I change the columns's type. Now, $this->columns is an array of array.

kqueekquee

i have this error
Parse error: syntax error, unexpected '[' in ........\plugins\DataTablesPHP\examples\ServerSide.php on line 47
when i load serverside.php

tangerine

@kqueekquee:
That needs the same solution as RobinDev's last post.

@RobinDev: Your examples need updating for consistency with your class itself. I will have to leave this for now as I don't have the time. I will try again when you have thoroughly tested your code. Thanks for your help.

tangerine

Hi RobinDev.

Is this still alive? If so, how would I insert (for example) a row's id - from the main db table - into a "formatter" function?

That is, in my $columns array:

    array('data' => 'id','title'=>'Id')

    array('title'=> 'Admin', 'formatter'=>function($data){return '<a href="">[id_in_link]</a>';} )

Edit: Sorry about the careless example! The id would be in the href, not the link title.

tangerine

Never mind, I got it.

However - no responses in here and no action on GitHub for three weeks - is this thing dead already?

RobinDev

@tangerine Yes it's still alive. I just worked on other stuff.

If you are still interested... to insert row's id in the formatter function just do :

array(
    'title'=> 'Admin',
    'formatter'=>function($colValue, $rowValues){
            return '<a href="">'.$rowValues['id'].'</a>';
        } 
);

I just did a big comit with a few modifications (bug corrections, improvements, new features). Just see the readme.md

tangerine

Thanks - good to hear. I'll be checking out the upgrade. Thanks again.

RobinDev

@tangerine Yes it's still alive. I just worked on other stuff.

If you are still interested... to insert row's id in the formatter function just do :

array(
    'title'=> 'Admin',
    'formatter'=>function($colValue, $rowValues){
            return '<a href="">'.$rowValues['id'].'</a>';
        } 
);

I just did a big comit with a few modifications (bug corrections, improvements, new features). Just see the readme.md

RobinDev

@tangerine Yes it's still alive. I just worked on other stuff.

If you are still interested... to insert row's id in the formatter function just do :

array(
    'title'=> 'Admin',
    'formatter'=>function($colValue, $rowValues){
            return '<a href="">'.$rowValues['id'].'</a>';
        } 
);

I just did a big comit with a few modifications (bug corrections, improvements, new features). Just see the readme.md

RobinDev

@tangerine Yes it's still alive. I just worked on other stuff.

If you are still interested... to insert row's id in the formatter function just do :

array(
    'title'=> 'Admin',
    'formatter'=>function($colValue, $rowValues){
            return '<a href="">'.$rowValues['id'].'</a>';
        } 
);

I just did a big comit with a few modifications (bug corrections, improvements, new features). Just see the readme.md