undefined index: columns, one silly and irritating error while using Datatables library

This Error occurred suddenly in one of my project listing page and it takes my lots of hour to find out the solution. That’s why think to share the experience.

What I found after searching is its happened may be for two reason

  • The encoded array from ssp.class.php was not in UTF-8 what is needed for json_encode.
  • And another one reason if the column array is big then the big URL size makes it problem if you are using GET method while server side processing.

So What’s the solution 🙂

First reason’s solution is at SSP Class you have to add following line after PDO connect after line no #272 to define character set.

$db->query("SET NAMES 'utf8'");

Or you can see my cutomised SSP class .

And second reason’s solution is you have to do use POST instead of GET while server side AJAX call.

While assign the URL you have to use HTTP type POST.

"ajax": {
"url": "scripts/post.php",
"type": "POST"
},

And little reminder is at server side script use POST instead of GET variable while calling SSP simple function. Like

$Ssp::simple( $_POST, $sql_details, $table, $primaryKey, $columns)

You can see the POST examples here. 

Hope it may help. 🙂

Thanks

Advertisements

JOIN and extra condition support at Datatables library SSP class

Whenever I need to view some listing in TABLE, I LOVE to use Datatables. Datatables library is really Awesome. It works like charm!!!

If you are new with Datatables then please first check their documentation and examples then come back 🙂

For HUGE data handle their Server side processing works also fine. They provide Server Side Processing class to process the request for datatables @version 1.10.0. But it’s only works with single table :(.

So handle Complex query like join or extra condition not supported at their provided SSP class. After a lots of searching in google and forums not found any reliable solution. After that I changed the class as my own.

I faced problem like

  1. Get Data from Multiple table not supported via Joining.
  2. Extra Where, except filtering was not possible.
  3. During work on Multiple Table, to avoid Duplicate key was not possible.

So due to my purpose done I have changed the SSP class like my own. The changes i made are :

  1. I have added option to ADD JOIN Query and make necessary changes.
  2. I have changed Column ARRAY format to handle get data from multiple table. And add TWO new index(‘field’, ‘as’) for complex query handle.
  3. Add Extra Where condition through SSP Class.
  4. You can Group by the result via sending Query through simple function of SSP Class.

You can see the DEMO and DOWNLOAD server-side processing customised SSP class from the following link.

demodownload

I want to share the changes I made at SSP class and try to explain what & why I made the changes. And I will show the usage too.

Change in SSP::simple Function :


/*

*....

* @param array $columns Column information array
* @param array $joinQuery Join query String
* @param string $extraWhere Where query String
*
* @return array Server-side processing response array
*
*/
static function simple ( $request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '')
{......
 $where = Libs_SSP::filter( $request, $columns, $bindings, $joinQuery );

// IF Extra where set then prepare query
 if($extraWhere)
 $extraWhere = ($where) ? ' AND '.$extraWhere : ' WHERE '.$extraWhere;

 // Main query to actually get the data
 if($joinQuery){
 $col = Libs_SSP::pluckForJoin($columns, 'db');
 $query = "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $col)."
 $joinQuery
 $where
 $extraWhere
 $groupBy
 $order
 $limit";
 }else{
.........

Here is the screen shot

SSP::simple

SSP simple class

Change in Column Array Filtering :

I have change the column array to fulfill my purpose of joining table. You know column name may duplicatie, So sometimes its needed to rename, I give option to this column array.

    1. Index ‘db’ refer which tables which field will select.
    2. Index ‘dt’ refer data table index as before
    3. Index ‘field’ refer at output in which name the output will print from SQL result after rename.
    4. Index ‘as’ refer If this index set then the column name will renamed via the assigned value.  
$columns = array(
array( 'db' => '`c`.`id`', 'dt' => 0, 'field' => 'id' ),
array( 'db' => '`c`.`login`', 'dt' => 1 'field' => 'login' ),
array( 'db' => '`c`.`password`', 'dt' => 2, 'field' => 'password' ),
array( 'db' => '`c`.`name`', 'dt' => 3, 'field' => 'client_name', 'as' => 'client_name' ),
array( 'db' => '`cn`.`name`', 'dt' => 4, 'field' => 'currency_name', 'as' => 'currency_name' )

array( 'db' => '`c`.`id_client`', 'dt' => 5,
'formatter' => function( $d, $row ) {
return '<a href="EDIT_URL"><span class="label label-inverse"><i class="fa fa-edit"></i> Edit</span></a>
<span class="label label-danger pointer"><i class="fa fa-trash-o"></i> Delete</span>
';
}, 'field' => 'id_client' )
);

$joinQuery = "FROM `{$table}` AS `c` LEFT JOIN `currency_names` AS `cn` ON (`cn`.`id` = `c`.`id_currency`)";

$Ssp = new Libs_SSP();
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery )
);

Here is the Screenshot

Customized Column array

SSP Customized column array

Extra Condition/Where Add :

I have added one option to SSP::simple class as a parameter and i prepare query as it should be. If you need to send some extra data while datatables initialisation, then you can do that like this.

$Where = "`c`.`id_client`=".$CLIENT_ID; // OR For a Normal query condition could be $Where = "`id_client`=".$CLIENT_ID;
$Ssp = new Libs_SSP();
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where);
);
where option

Extra where option added.

And how i prepare query for extra where that have shown at first screen shot.

Group By Option :

You can GROUP BY your result via sending the COLUMN name in SSP::simple function.

$groupBy = '`c`.`id_client`'; // for multiple COLUMN '`c`.`id_client`,`c`.`other_param`'
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where, $groupBy);
);

In previous version of SSP class, the parameter took full format like below.

$groupBy = ' GROUP BY `c`.`id_client` ';
  • HAVING Option

You can use HAVING query within SSP class right now. And it’s pretty easy to use.

 $having = '`c`.`id_client` >= 100'; // same as the extra where
echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where, $groupBy,$having); );

 

  • Output Format changes

For JOIN query the output field is mapping from ‘field’ index of COLUMN array rather than ‘db’ index.

output mapping

Output Mapping from Column field index for JOIN

Other Changes in SSP class :

  • filter function
    • Added $isJoin optional parameter.
    • IF $isJoin set then the Quotation have different due to change in db index of COLUMN ARRAY for JOIN.
    • Otherwise it works like before.
  • pluck function
    • Same as filter function added $isJoin optional parameter.
    • For rename column name, preparing query is here, If corresponding as index is set at COLUMN Array .

 Updated: Due to few re-factoring and renamed may be some code shown here will mismatch with present version of ssp.customized.class.php. 

Like the Class name is changed SSP from Libs_SSP, So It’s better to see/use Example code at github. You will get just the Idea of changes here, So don’t Use these code blindly.

That’s it…..

So Listing huge data with complex query is FUN with Datatables NOW.

So, Happy Coding!!! 🙂

Any kind of Suggestion or feedback is truly appreciated.