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.

Advertisements

176 thoughts on “JOIN and extra condition support at Datatables library SSP class

    • I got problem on apply @joinQuery that the join table valuable (s.surame) cannot be shown and return “null”

      $columns = array(
      array( ‘db’ => ‘`s`.`surname`’, ‘dt’ => 0, ‘field’ => ‘sid’ ), <– this shown null
      :
      :
      $joinQuery = "FROM `fchan_mc_ee_hw` AS `h` LEFT JOIN `fchan_mc_stud` AS `s` ON (`s`.`id` = `h`.`stud_id`)";

      for details, please refer to my post here :

      https://fchanblog.wordpress.com/2015/12/20/joinquery-apply-left-join-but-cannot-show-join-tables-valuable/

      • Emran Ul hadi says:

        I think the column array was wrong. As you want to rename your column name, so you have to define the ‘as’ index with new name. Like
        $columns = array(
        array( ‘db’ => ‘`s`.`surname`’, ‘dt’ => 0, ‘field’ => ‘sid’, as => ‘sid’ )

        I think that will solve your problem.
        Thanks

      • I’ve encountered the same problem.
        ..
        array( ‘db’ => ‘`p`.`id`’, ‘dt’ => ‘sid’, ‘field’ => ‘sid’, ‘as’ => ‘sid’ ),
        ..
        ..
        $joinQuery = “FROM `places` AS `p`”;

        This returns sid null
        If i change
        ‘db’ => ‘`p`.`id`’
        to
        ‘db’ => ‘`id`’
        It fixes the problem. Why?

      • Emran Ul hadi says:

        If you don’t use need Other table to JOIN then use normally Datatables.
        And otherwise the ‘dt’ index is usually integer as a column index.
        Otherwise those syntax seems ok for me.

    • Emran Ul hadi says:

      Sorry for Late reply Leonel. 😦

      Whatever you can ADD Multiple JOIN within this JOIN Query param.
      LIKE:

      $joinQuery = "FROM `user` AS `u` JOIN `user_details` AS `ud` ON (`ud`.`user_id` = `u`.`id`) LEFT JOIN `user_address` AS `ua` ON (`ua`.`user_id` = `u`.`id`)";

      And So on…

      And for specific order column you have to define that column in Javascript end, not in Server side script.

      $('#example').dataTable( {
      "processing": true,
      "serverSide": true,
      "ajax": "scripts/server_processing.php",
      "order": [[ 2, "desc" ]]
      } );

      It will sort by 2 no index column. For more details: http://datatables.net/examples/basic_init/table_sorting.html

      Thanks 🙂

  1. Hi Emran.
    Your work on this is very useful to me, so Thank You!
    However, I’m struggling with one particular situation where I need two joins to the same table.

    My “person” table has a birthplace_id and a deathplace_id. These both join to a “towns” table which has a simple “id, name” structure.

    In my $columns array I have these elements (among others):
    array( ‘db’ => ‘`t1`.`name`’, ‘dt’ => 7, ‘field’ => ‘id’, ‘as’ => ‘btown_id’ ),
    array( ‘db’ => ‘`t2`.`name`’, ‘dt’ => 8, ‘field’ => ‘id’, ‘as’ => ‘dtown_id’ ),
    and my join clause looks like this:
    LEFT JOIN `towns` AS `t1` ON (`t1`.`id` = `w`.`btown_id`)
    LEFT JOIN `towns` AS `t2` ON (`t2`.`id` = `w`.`dtown_id`)

    (I have tried various other combinations, but basically I’m always missing something.)
    Some help would be great. I’m happy to pay for support and provide more code if required.
    TIA
    Martin Hall

    • Emran Ul hadi says:

      Hello Martin, Sorry for Late reply 😦

      How much I understand your scenario, I think everything is ok except the COLUMN Array.

      As I said my Post that Index ‘field’ refer at output in which name the output will print from SQL.

      And Index ‘as’, If this index set then the column name will renamed via the assigned value.

      And when one COLUMN is renamed then obviously it’s `field` index value would be same as `as` index value.

      So your column Array would be like

      array( ‘db’ => ‘`t1`.`name`’, ‘dt’ => 7, ‘field’ => ‘btown_id’, ‘as’ => ‘btown_id’ ),
      array( ‘db’ => ‘`t2`.`name`’, ‘dt’ => 8, ‘field’ => ‘dtown_id’, ‘as’ => ‘dtown_id’ ),

      Hope I may clear to you and hope it’s Help. 🙂

  2. this is really awesome, I have looking for anywhere, and I found here. thank you very much.
    but, I wanna asking you.
    how about if using subquery mysql.
    example query like this.
    SELECT
    a.id_p,
    a.id_prov,
    a.id_bdg,
    a.kode_pelatihan,
    a.nama_pelatihan,
    a.status_pel,
    a.ket_pel,
    a.date_input,
    b.provinsi,
    c.nama_bidang,
    (
    SELECT
    count(id_matpel)
    FROM
    tb_matpel
    WHERE
    id_p = a.id_p
    ) AS total_materi,
    (
    SELECT
    count(id_in)
    FROM
    tb_inspel
    WHERE
    id_p = a.id_p
    ) AS total_inst,
    (
    SELECT
    count(id_pespel)
    FROM
    tb_pespel
    WHERE
    id_pelatihan = a.id_p
    ) AS total_p
    FROM
    tb_p a
    INNER JOIN tb_prov b ON a.id_prov = b.id_prov
    INNER JOIN tb_bdg c ON a.id_bdg = c.id_bdg
    ORDER BY
    a.date_input DESC;

    // is possible to use with your class…? if can, how to implement it
    thanks in advance and sorry for my bad english

    • Ok sorry, I was looking for it since 1 hour ^^. I just forgot double quotes…

      Perhaps this syntax will help someone …

      $extraWhere = ‘`chambre`.`ambiance` = ‘.$id_ambiance.’ AND `salon`.`terrasse` != “‘.$ambiance->nom_ambiance().'”‘;

      Thank you man for this excellent script ! 🙂

    • Emran Ul hadi says:

      Sorry for Late reply Adam 😦

      Whatever you can ADD Multiple JOIN within this JOIN Query param.
      LIKE:
      $joinQuery = “FROM `user` AS `u` JOIN `user_details` AS `ud` ON (`ud`.`user_id` = `u`.`id`) LEFT JOIN `user_address` AS `ua` ON (`ua`.`user_id` = `u`.`id`)”;

      And So on… You can add multiple JOIN query within this query string as you want.

      Hope I may clear to you 🙂

  3. hi,

    Thanks for your article to perform join and additional where filter options.

    Now, i need one more help,

    i need to place checkbox on first column, do you have any idea to pass it through this ssp class.

    I think you can help me, thanks in advance.

    • Emran Ul hadi says:

      To add custom view in COLUMN data you have to use formatter in column Array.
      Like if you need Checkbox then you can formatter define like this:

      $columns = array(
      array( 'db' => "id_tariffs_key", 'dt' => 0, 'formatter' => function($d, $row){
      return '<input type="checkbox" class="tariff-key-id-input" name="tariff_key_ids[]" value="'.$d.'"/>';
      }),
      ..........................


      Within this return you can insert HTML INPUT Tag or anything else. In formatter you can do anything and whatever you return that will show within this COLUMN.

      Hope I make it clear to you 🙂

    • Emran Ul hadi says:

      I have renamed the class as SSP from Libs_SSP at present version of ssp.customized.class.php. And I did some re-factoring too. 😦

      So can you please see the example code at github repo. Or you can use SSP instead of Libs_SSP wherever you have used in your Script to get the SSP Class.

      Whatever Thanks for the comment 🙂

  4. This looks very interesting but I’m hoping that you can provide a little guidance. I am new to DataTables and am using the Editor plugin. I currently just have a PHP file that creates the Editor instance and processes some items I send by POST to add an additional WHERE clause. I want to use your code so I can add the extra where clause because I have a UDF in MySQL I want to use.

    Could you please help me and let me know the best way to so this. Would I just edit my current PHP by adding:

    require(‘ssp.customized.class.php’ );

    and then at the end of the Editor Instance add something like this:

    ->extrawhere = “myUDF(`Lname`) <3";

    • Emran Ul hadi says:

      Thanks George for the appreciation. 🙂

      I haven’t used UDF yet but UDF and MySQL default functions should work in a same way. So I think Your extra where statement should work. Can you please try and let me know what you got.

      Thanks

  5. Hi there, thanks so much for your custom class, its really nice, i have been working with your custom on my local server and it works perfectly, but for any reason not work online, could you help me to find the problem! thanks!!!

    • Emran Ul hadi says:

      You are Most welcome. 🙂

      Whatever I haven’t find any difficulties or any extra change for online/server.
      So can you please check your error then debug that. Or I can Help if you share your code snippet and error.

      Thanks 🙂

  6. Hi Emran.
    Many thanks again for your help with this.

    Is it possible to do a MySQL CONCAT with your class? For example, I almost got this element working in my $columns array:

    array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`first_name`, `a`.`last_name` )’, ‘dt’ => 2,
    ‘field’ => ‘name’ ),

    but I don’t know how to name the ‘field’. My db table has “first_name” and “last_name” but not “name” so your class returns “Error – Undefined index: name”.

    Thanks in advance.
    Martin

    • Emran Ul hadi says:

      Hello Martin,

      It seems everything is fine. But you have to rename the custom field using define ‘as’ index.

      In column array ‘field’ index refer, from which index the output data will return. You define ‘field’ is ‘name’. But its true that there is no column named ‘name’. So just you have to rename it via define ‘as’ index like following.

      array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`first_name`, `a`.`last_name` )’, ‘dt’ => 2, ‘field’ => ‘name’, ‘as’ => ‘name’ ),

      Then it should work.

      Thanks

      • Yes, thanks Emran – that’s working now.
        BUT my mistake was using ‘AS’ instead of ‘as’. (I normally use uppercase for MySQL terms.) Maybe your class needs to use strtolower() ? Or add to the documentation?

        Anyway – thanks again. I really appreciate your work!

  7. Hi, I come back here because I have a problem recently.

    All my pages using Datatable work normally. In recent days, I try to parse the result of two new tables (with the same column names as those in which I already use this class and it works) and I get the error tn/1 saying Datatable can not encode JSON return of the script.

    What is very strange is that if I do a var_dump ($results), I get a good result makes sense. Why json_encode does not?

    I also noticed that sometimes the date formatter is the source of the problems and sometimes not.

    What can produce this problem, knowing that it works fine on all other pages?

    • Emran Ul hadi says:

      Hello,

      You have told “with the same column names as those in which I already use this class and it works”, the matter is obviously the column name is not responsible, may be few character is included in the output what makes JSON broken. Check the OUTPUT deeply.

      Few days ago I faced a silly problem and I solve it via defining character set utf8.
      Don’t know this will help or not just sharing. you can look: http://bit.ly/1GQCvgg
      And you can see this too: http://datatables.net/manual/tech-notes/1

      It would be better to understand if you share the source code.
      Hope you can sort it out.

  8. Hi there, thanks so much for your custom class, its really nice.

    I want to concat two fields (fname, lname as Name) in single column and also change the id field into checkbox for performing action on the selected row?

    Also one more thing? is it working with PosgreSQL too?

    • Emran Ul hadi says:

      To CONCAT two field you can define COLUMN array as below.

      array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`fname`, `a`.`lname` )’, ‘dt’ => 2, ‘field’ => ‘Name’, ‘as’ => ‘Name’ ),

      To add custom view in COLUMN data you have to use formatter in column Array.
      Like if you need Checkbox then you can formatter define like this:

      $columns = array(
      array( 'db' => "id", 'dt' => 0, 'formatter' => function($d, $row){
      return '<input type="checkbox" class="id-input" name="ids[]" value="'.$d.'"/>';
      }),
      ...

      This custom Class will not work for PosgreSQL.
      But you can use this Script to work with PosgreSQL for datatables 1.9 .
      https://datatables.net/development/server-side/php_postgres

  9. hi emran it very owsome custom, how about if i have colom in datatsble and it value is multi colum in database.
    array( ‘db’ => ‘`u`.`first_name`’,’`u`.`first_lastname`’, ‘dt’ => 0, ‘field’ => ‘first_name’ ),

    thanks

  10. Hi Emran, again great job!!

    I have some particular join to do.

    I have a table named lease_agreement with renter_id and lessee_id.
    I have another table customer with id, customerName, CustomerLastName

    I want to show this fields in datatables.

    lease_agreements.id / renter_id (shown as customerName and customerLastName) / lessee_id (shown as customerName and customerLastName)

    Don’t know how to write the SQL Join statement.
    I hope you can help me.
    Thanks!

  11. array( ‘db’ => ‘`c`.`id`’, ‘dt’ => 0, ‘field’ => ‘id’ ),
    array( ‘db’ => ‘`p`.`address`’, ‘dt’ => 1, ‘field’ => ‘address’ ),
    array( ‘db’ => ‘`a`.`customerName`’, ‘dt’ => 1, ‘field’ => ‘renter_id’, ‘as’ => ‘renter_id’ ),
    array( ‘db’ => ‘`a`.`customerLastName`’, ‘dt’ => 2, ‘field’ => ‘renter_id’, ‘as’ => ‘renter_id’ ),
    array( ‘db’ => ‘`aa`.`customerLastName`’, ‘dt’ => 3, ‘field’ => ‘lessee_id’, ‘as’ => ‘lessee_id’),
    array( ‘db’ => ‘`aa`.`customerName`’, ‘dt’ => 4, ‘field’ => ‘lessee_id’, ‘as’ => ‘lessee_id’)

    $joinQuery = “FROM `{$table}` AS `c` INNER JOIN `property` as `p` ON (`c`.`property_id` = `p`.`id`)”
    . “INNER JOIN `customers` AS `a` ON (`c`.`renter_id` = `a`.`id`)”
    . “INNER JOIN `customers` AS `aa` ON (`c`.`lessee_id` = `aa`.`id`)”;

    It shows id from lease_agreement (AS ‘c’). And customerLastName, but instead of customerName’s again shows customerLastName.

    Thanks.

    • Emran Ul hadi says:

      Hello Lisandro,

      Thanks for the appreciation.

      First of all, I am not sure what you mean by that in previous comment.

      “renter_id (shown as customerName and customerLastName) / lessee_id (shown as customerName and customerLastName)”

      What do you really want to show?
      If you need something customised output then you can achieve that via defining “formatter” index in column array.
      Even you can combine two field via using CONCAT. like

      array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`customerName `, `a`.`customerLastName ` )’, ‘dt’ => 2, ‘field’ => ‘name’, ‘as’ => ‘name’ ),

      And you define “db” index as ‘`a`.`customerName`’. and you select output ‘renter_id’ via defining ‘field’. “db” index for select, and “field” index for give output. I am little bit confused. 😦

      Can you please clarify, then may I can help.

      Sorry for not understanding 😦

      • Dear Emran, i have the next Join:

        $joinQuery = “FROM `{$table}` AS `c` ”
        . “LEFT JOIN `lease_agreement` as `la` ON (`c`.`id` = `la`.`lessee_id`)”
        . “LEFT JOIN `lease_agreement` as `laa` ON (`c`.`id` = `laa`.`renter_id`)”;

        And it’s returning duplicated rows, i want to add GROUP BY `c`.`id` so returns not duplicated records.
        This is the query that i run in phpmyadmin:

        SELECT `customers`.`id` , `a`.`uuid`
        FROM `customers`
        LEFT JOIN `lease_agreement` a ON `customers`.`id` = `a`.`renter_id`
        LEFT JOIN `lease_agreement` b ON `customers`.`id` = `b`.`lessee_id`
        GROUP BY `customers`.`id`

        And it works!

        Thanks.

      • Emran Ul hadi says:

        Hello Lisandro,

        I had added GROUP BY option too at my customized SSP class.
        To do that, I had just added another param after Extra where parameter in the SSP::simple function.

        So you just have to pass the COLUMN by which you want to GROUP BY your result at SSP::simple function.

        $groupBy =  '`c`.`id`';
        echo json_encode(  SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy );
        )
        

        One more thing, as you have no Extra WHERE condition then pass NULL or ” on that position.

        Now it should work as you expect. 🙂

        Thanks

      • This is my code.

        require(‘ssp.customized.class.php’ );

        $joinQuery = “FROM `{$table}` AS `c` ”
        . “LEFT JOIN `lease_agreement` as `la` ON (`c`.`id` = `la`.`lessee_id`)”
        . “LEFT JOIN `lease_agreement` as `laa` ON (`c`.`id` = `laa`.`renter_id`)”;

        $extraWhere = “”;
        $groupBy = ‘`c`.`id`’;
        echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy ));

        But i’m getting the same error. So i’ve changed ssp.customized.class.php in order to print $sql statement and returned this.

        SELECT SQL_CALC_FOUND_ROWS `c`.`id`, `c`.`customerName`,
        `c`.`customerLastName`, `c`.`phone`, `la`.`lessee_id`, `laa`.`renter_id`, `c`.`uuid`
        FROM `customers` AS `c` LEFT JOIN `lease_agreement` as `la` ON (`c`.`id` = `la`.`lessee_id`)LEFT JOIN `lease_agreement` as `laa` ON
        (`c`.`id` = `laa`.`renter_id`) `c`.`id` ORDER BY `c`.`id` LIMIT 0, 10″

        If you see, before ORDER BY you can see an extra `c`.`id`. If i take this sentences and put in SQL in phpmyadmin doesn’t work. But if i remove `c`.`id`, it works!

        So i think is order function that is adding this.

        Can you help me?

        Thank again!

      • Emran Ul hadi says:

        Sorry Lisandro, I gave you wrong instruction. I forgot that in GROUP BY param you have to send full format rather than simply send COLUMN name.
        It could be managed in a smarter way. I will change that in future.

        So right now you have to do is

        $groupBy = ‘ GROUP BY `c`.`id` ';
        

        Now it should work.

        I am really sorry 😦

  12. adam kenyon says:

    Hello Emran,

    Great job with this code. Many thanks.

    Just wondering whether it is possible to include a calculated column in the SQL (along with GROUP BY), and then pass that into the columns array?

    I am trying to do the following SQL:

    $columns = array(
    array( ‘db’ => ‘`ic`.`InvId`’, ‘dt’ => 0, ‘field’ => ‘InvId’ ),
    array( ‘db’ => ‘`ic`.`FirstName`’, ‘dt’ => 1, ‘field’ => ‘FirstName’ ),
    array( ‘db’ => ‘`ic`.`Surname`’, ‘dt’ => 2, ‘field’ => ‘Surname’ ),
    array( ‘db’ => ‘`ic`.`CompanyName`’, ‘dt’ => 3, ‘field’ => ‘CompanyName’ ),
    array( ‘db’ => ‘`ic`.`Country`’, ‘dt’ => 4, ‘field’ => ‘Country’ ),
    array( ‘db’ => ‘`cb`.`Type`’,’dt’ => 5, ‘field’ => ‘Type’ ),
    array( ‘db’ => ‘`cb`.`Reason`’, ‘dt’ => 6, ‘field’ => ‘Reason’ ),
    array( ‘db’ => ‘`ic`.`numbounces`’, ‘dt’ => 7, ‘field’ => ‘numbounces’ )
    );

    $join_clause[] = “INNER JOIN contact_bounces AS cb ON ic.InvId = cb.InvId”;
    $where_clause[] = “ic.InvId >= 1″;

    $joinQuery =”,COUNT(Surname) AS ‘Num_of_bounces’ FROM investor_contacts AS ic ” . implode(‘ ‘, $join_clause);

    $extraWhere = implode(‘ and ‘, $where_clause) . ” GROUP BY ic.Email,cb.Type,cb.Reason”;

    echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere )
    );

    If I remove the count part in the $joinQuery and the 8th element mapping in the array, the query works. The error I get says that the field ‘ic.numbounces’ doesn’t exist (technically true as it is a calculated field).

    If you could help at all I would be really grateful.

    Thanks

    Adam

    • Emran Ul hadi says:

      Hello Adam,

      Thanks for the appreciation. And very sorry for late reply.

      I think few things are wrong here. First one is you add selected COLUMN at JOIN Query. That’s will not work. You have to add that in COLUMN array. Cause SSP class prepare the OUTPUT from COLUMN Array.

      So JOIN Query should be like this.

      $joinQuery =” FROM investor_contacts AS ic ” . implode(‘ ‘, $join_clause);
      

      And COLUMN Array for that customized COLUMN should be like this.

      .....
      array( ‘db’ => ‘COUNT(`ic`.`Surname`)’, ‘dt’ => 7, ‘field’ => ‘numbounces’, 'as' => 'numbounces' )
      

      You have to rename the output field by defining ‘as’ index on that array.

      Hope it may help.

      Thanks

  13. Thanks Emran,

    It’s not working in server-side mode.

    This is my initialization code..

    table = $(‘#example’).dataTable( {
    “dom”: ‘Tlfrtip’,
    “tableTools”: {
    “sSwfPath”: “js/plugins/dataTables/swf/copy_csv_xls_pdf.swf”
    },
    “processing”: true,
    “serverSide”: true,
    “ajax”: {
    “url”: “scripts/client.php”
    },
    “search”: {
    “caseInsensitive”: false
    },
    “columnDefs”: [
    { className: “align-center”, “targets”: [ 4 ] },
    { “visible”: false, “targets”: 4 },
    { “visible”: false, “targets”: 5 }
    ]
    } );

  14. Hello Emran,

    Great this code. Many thanks for it.
    I have a little problem.
    i have in database 12334 entries but showing only 11746.
    Why?
    :
    Showing 1 to 10 of 11,746 entries (filtered from 12,334 total entries)

    Many Thanks!

    • Emran Ul hadi says:

      Hello Casba,

      The matter is, we filtered externally using WHERE/JOIN. That’s why the message shows filtered from. Because of _MAX_ and _TOTAL_ are different the filter message have appear.

      In Datatables everything is customizable, So you can change this too.
      May be, you can remove this message would be one solution via setting “infoFiltered” param.

      $('#example').dataTable( {
             "oLanguage": {
               "sInfoFiltered": "" // remove " ( filtering from _MAX_ records)"
             }
      } );
      

      Hope it may help.

      Thanks 🙂

    • Emran Ul hadi says:

      Thanks Vel 🙂

      You can use any MySQL function within COLUMN array like bellow.

      array( ‘db’ => ‘SUM( `a`.`quantity`)’, ‘dt’ => 2, ‘field’ => ‘sum_of_quantity’, ‘as’ => ‘sum_of_quantity’ )
      ....
      
  15. I’m sorry my English is by google translator.
    Find the optimum his script, I am always using.
    But I have a problem in my data base the “dates” are as 01/01/2015.
    Then I use like this:

    array (‘db’ => “DATE_FORMAT (conta_receber_data, ‘% d /% m /% Y’)” ‘dt’ => 2, ‘field’ => “DATE_FORMAT (conta_receber_data, ‘% d /% m /% Y ‘) “)

    But it gives me an unrecognized field error in the table.

    I need to make the transformation of the field data by the type:

       array (‘db’ => ‘conta_receber_data’, ‘t’ => 2, ‘field’ => ‘Date’, ‘formatter’ => function ($ d, $ row) {
                                                                         return date (“d / m / Y ‘, strtotime ($ d));
                                                                     }),

    The field is perfectly changed, but the search does not recognize the type 01/01/2015, for example.

    So if you have an idea of what should I do thank you very much.

    • Emran Ul hadi says:

      I think you got the solution as i am answering too late, but besides that just saying using ‘formatter’ index you can add customised view in column. Within this formatter index whatever you will pass, it will appear that column.

      You can send HTML block or any customised output. You can see ‘formatter’ index example code at my given example.

      Thanks 🙂

  16. Hello. I have a problem. My column array looks like that:

    $columns = array(
    array( ‘db’ => ‘a.LONG_ID’, ‘dt’ => 0, ‘field’ => ‘LONG_ID’ ),
    array( ‘db’ => ‘a.NAME’, ‘dt’ => 1, ‘field’ => ‘NAME’, ‘as’ => ‘NAME’),
    array( ‘db’ => ‘a.BREED’, ‘dt’ => 2, ‘field’ => ‘BREED’ ),
    array( ‘db’ => ‘a.BIRTH_DATE’, ‘dt’ => 3, ‘field’ => ‘BIRTH_DATE’),
    array( ‘db’ => ‘a.GENDER’, ‘dt’ => 4, ‘field’ => ‘GENDER’ ),
    array( ‘db’ => ‘a.FATHER’, ‘dt’ => 5, ‘field’ => ‘FATHER’ ),
    array( ‘db’ => ‘b.NAME’, ‘dt’ => 6, ‘field’ => ‘NAME’, ‘as’ => ‘FATHER_NAME’)
    );

    And my join query looks like that:

    $joinQuery = “FROM animal AS a LEFT JOIN animal AS b ON (b.ID = a.FATHER)”;
    $extraWhere = “”;
    echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere)
    );

    FATHER value in the table is ID value in the same table of different row. But what it does now in place of a.NAME it shows same value than in b.NAME. But the values of a.NAME and b.NAME are different. What could be my problem?

    • Emran Ul hadi says:

      Hello Julius

      The problem is in the last column ‘field’ index.

      ‘field’ Index refer at, in which index the output will take from SQL result after rename.

      So you had renamed the field as ‘FATHER_NAME’. so your ‘field’ index value should be same like as ‘as’ index value, that is ‘FATHER_NAME’.

      It takes a.name, cause the ‘field’ refers to the ‘NAME’ same as 2nd column.

      so your last column should be like this.
      array( ‘db’ => ‘b.NAME’, ‘dt’ => 6, ‘field’ => ‘FATHER_NAME’, ‘as’ => ‘FATHER_NAME’)

      Hope it clear to you and solve your problem.

      Thanks

  17. Hi, wonderful addition to datatables. I have idea how to implement “select all” on the whole dataset but I need assistance. I imagine that I can pass variable $selectAll in:

    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $selectAll )

    Afterwards, I should probably verify if we have that variable set to true in ssp class and retrieve all of the primary keys for the query. We can then place that in output like:

    /*
    * Output
    */
    return array(
    “draw” => intval( $request[‘draw’] ),
    “recordsTotal” => intval( $recordsTotal ),
    “recordsFiltered” => intval( $recordsFiltered ),
    “selected” => $selected,
    “data” => SSP::data_output( $columns, $data, $joinQuery )
    );

    When we get results back to the initial page where we do filtering and selecting, I would be able to retrieve all of the primary ids for the table and do whatever I want with them. 🙂 I just need help how to retrieve all of the primary ids without limiting query. Thanks in advance.

  18. Really great job, I have used in a project where I had to handle a table with 600,000 rows, when data appears in 1 second is really impressive! (Especially for the customers!!)

  19. can you help me to write this query with the Customized php class for Datatable.

    SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
    gl.tran_date,
    gl.type,
    gl.type_no,
    refs.reference,
    SUM(IF(gl.amount>0, gl.amount,0)) as amount,
    com.memo_,
    IF(ISNULL(u.user_id),”,u.user_id) as user_id
    FROM 0_gl_trans as gl
    LEFT JOIN 0_audit_trail as a ON
    (gl.type=a.type AND gl.type_no=a.trans_no)
    LEFT JOIN 0_comments as com ON
    (gl.type=com.type AND gl.type_no=com.id)
    LEFT JOIN 0_refs as refs ON
    (gl.type=refs.type AND gl.type_no=refs.id)
    LEFT JOIN 0_users as u ON
    a.user=u.id
    WHERE (gl.tran_date >= ‘2015-06-18’
    AND gl.tran_date <= '2015-07-18'
    AND gl.amount!=0) GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no LIMIT 0, 10

  20. hi erman can you help out about formatter i need
    to get two values or more in the the formater which i don’t know how
    or is the function available
    exaple below
    $fname
    $lname
    $status
    $id to get it on one formatter

    thanks

    • Emran Ul hadi says:

      Hello Paulo,

      If those field you selected in any column array then you can use those fields from the $row array via index within formatter function.

      On the other hand if those field are not selected in COLUMN array then you can CONCAT multiple column to get within one place.

      Example :

      array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`first_name`, `a`.`last_name` )’, ‘dt’ => 2, ‘field’ => ‘full_name’, ‘as’ => ‘full_name’ ),

      Reference: https://datatables.net/reference/api/concat()

      Hope it helps 🙂

      Thanks

  21. Hi Emran,

    i have done formatting from mysql date_format function like this:

    array( ‘db’ => “DATE_FORMAT(o.`order_date`,’%d/%m/%Y’)”, ‘dt’ => 6 ,’as’ => ‘order_date’,’field’ => ‘order_date’ ,’formatter’ => function( $d, $row ) {
    return $d;
    }),

    But can’t able sort it asc or desc bz in Order it appear like this “Order BY DATE_FORMAT(o.`order_date`,’%d/%m/%Y’) DESC “So its notable to sort.

    So used one more way

    array( ‘db’ => “o.`order_date` “, ‘dt’ => 6 ,’as’ => ‘order_date’,’field’ => “o.`order_date`” ,’formatter’ => function( $d, $row ) {
    $phpdate = strtotime( $d );
    $formatdate = date( ‘d/m/Y’, $phpdate );
    return $formatdate;
    }),

    Formatted using PHP
    Sorting worked fine But Search of DataTable is notable work. Because the ‘order_date’ is in datetime format in mysql, if search in that format it will show. but while search same format d/m/Y it will not work

    Could please help me out.
    To work both Sorting and Search.

  22. Hello Emranulhadi,
    Thanks for the great work. However I have found an issue that I would like to identify so that it can help someone else as it helped me to handle complex queries with multiple joins up to 4 or 5 while 2 of them were onto same table.

    I got an error while using AS for undefined field on output_data function. So I had to replace following on line 53 of ssp.customized.class.php
    $row[ $column[‘dt’] ] = ($isJoin) ? $data[$i][ $columns[$j][‘field’] ] : $data[$i][ $columns[$j][‘db’] ];
    with
    $row[ $column[‘dt’] ] = ($isJoin) ? (isset($columns[$j][‘as’])?$data[$i][ $columns[$j][‘as’] ]: $columns[$j][‘field’]) : $data[$i][ $columns[$j][‘db’] ];

    so that it regards as option when it exists while Join is set to true.

    Kind Regards,
    Aamir Hussain
    http://w3facility.com/

  23. Hi Emran .
    Thanks for the work. Am still having issues with it. Am getting illegal string offset at line 90.

    $table =”property”;
    $primaryKey = ‘property.propertyID’;

    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case simple
    // indexes
    /* $columns = array(
    array( ‘db’ => ‘propertyNAme’, ‘dt’ => 0 ),
    array( ‘db’ => ‘propertyAddress’, ‘dt’ => 1 ),
    array( ‘db’ => ‘propertyCode’, ‘dt’ => 2 ),
    array( ‘db’ => ‘propertyRegistrationNumber’, ‘dt’ => 3 ),
    array( ‘db’ => ‘createdAt’,’dt’ => 4,’formatter’ => function( $d, $row ) {return date( ‘jS M y’, strtotime($d)); }),
    array(‘db’ => ‘propertyPrice’, ‘dt’=> 5,’formatter’ => function( $d, $row ) { return ‘$’.number_format($d); } )
    );*/
    $sql_details = array(
    ‘user’ =>APP_SYSTEM_DB_USER_NAME,
    ‘pass’ => APP_SYSTEM_DB_USER_PASSWORD,
    ‘db’ => APP_SYSTEM_DB_NAME,
    ‘host’ => APP_SYSTEM_DB_HOST
    );
    $columns = array(
    array( ‘db’ => ‘p.propertyName’, ‘dt’ => 0, ‘field’ => ‘Property Name’ ),
    array( ‘db’ => ‘p.propertyAddress’, ‘dt’ => 1, ‘field’ => ‘Address’ ),
    array( ‘db’ => ‘p.propertyCode’, ‘dt’ => 2, ‘field’ => ‘Property Code’ ),
    array( ‘db’ => ‘p.propertyRegistrationNumber’, ‘dt’ => 3, ‘field’ => ‘Reg No’),
    array( ‘db’ => ‘p.propertyPrice’, ‘dt’ => 4, ‘field’ => ’email’ ),
    array( ‘db’ => ‘p.createdAt’, ‘createdAt’, ‘dt’ =>5,’field’ => ‘date’,’formatter’ => function( $d, $row ) {return date( ‘jS M y’, strtotime($d)); }),
    array( ‘db’ => ‘c.countryName’, ‘dt’ => 6, ‘field’ => ‘Country’));
    $joinQuery = ” FROM `{$table}` AS p LEFT JOIN country AS c ON (c.countryID = p.propertyCountryID)”;
    $extraWhere=””;
    $data= \app\api\models\datatable\helper\SSP::simple($_GET, $sql_details,$table, $primaryKey, $columns,$joinQuery, $extraWhere);

  24. https://wordpress.com/post/fchanblog.wordpress.com/4

    How to apply SUM(CASE WHEN h.mc_ee_level_id = 1 THEN 1 ELSE 0 END) AS L1 at database ssp ?

    // my mysql which can generate data as below
    SELECT s.id, s.surname,
    SUM(CASE WHEN h.mc_ee_level_id = 1 THEN 1 ELSE 0 END) AS L1,
    SUM(CASE WHEN h.mc_ee_level_id = 2 THEN 1 ELSE 0 END) AS L2,
    SUM(CASE WHEN h.mc_ee_level_id = 3 THEN 1 ELSE 0 END) AS L3,
    SUM(CASE WHEN h.mc_ee_level_id = 4 THEN 1 ELSE 0 END) AS L4,
    SUM(CASE WHEN h.mc_ee_level_id = 5 THEN 1 ELSE 0 END) AS L5,
    SUM(CASE WHEN h.mc_ee_level_id = 6 THEN 1 ELSE 0 END) AS L6
    FROM fchan_mc_ee_hw AS h
    LEFT JOIN fchan_mc_stud AS s ON s.id = h.stud_id
    LEFT JOIN fchan_users AS m ON m.id = s.member_id
    Where m.officer_id= 502
    GROUP BY h.stud_id

    // while using datatables ssp, I convert about mysql statement into below
    $table = ‘fchan_mc_ee_hw’;
    $primaryKey = ‘id’;
    $columns = array(
    array( ‘db’ => ‘`s`.`surname`’, ‘dt’ => 0, ‘field’ => ‘stud_name’ ),
    array( ‘db’ => ‘`h`.`officer_id`’, ‘dt’ => 1, ‘field’ => ‘officer_id’ ),
    array( ‘db’ => ‘`SUM(CASE WHEN h`.`mc_ee_level_id`.`= 1 THEN 1 ELSE 0 END)`’, ‘dt’ => 2, ‘field’ => ‘L1’),
    array( ‘db’ => ‘`SUM(CASE WHEN h`.`mc_ee_level_id`.`= 2 THEN 1 ELSE 0 END)`’, ‘dt’ => 3, ‘field’ => ‘L2’),
    array( ‘db’ => ‘`SUM(CASE WHEN h`.`mc_ee_level_id`.`= 3 THEN 1 ELSE 0 END)`’, ‘dt’ => 4, ‘field’ => ‘L3’),
    array( ‘db’ => ‘`SUM(CASE WHEN h`.`mc_ee_level_id`.`= 4 THEN 1 ELSE 0 END)`’, ‘dt’ => 5, ‘field’ => ‘L4’),
    array( ‘db’ => ‘`SUM(CASE WHEN h`.`mc_ee_level_id`.`= 5 THEN 1 ELSE 0 END)`’, ‘dt’ => 6, ‘field’ => ‘L5’)
    );
    require(‘ssp.customized.class.php’ );
    $joinQuery = “FROM `fchan_mc_ee_hw` AS `h`
    JOIN `fchan_mc_stud` AS `s` ON (`s`.`id` = `h`.`stud_id`)
    JOIN `fchan_users` AS `m` ON (`m`.`id` = `s`.`member_id`)”;
    $groupBy = ‘`h`.`stud_id`’;

    echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $groupBy,$extraWhere )
    );

    // and return error message as
    DataTables warning: table id = myTable3 – An SQL error occured: SQLSTATE [42S22] : Column not found: 1054
    Unknow column ‘SUM(CASE WHEN h.mc_ee_level_id = 1 THEN 1 ELSE 0 END) ‘ in ‘field list’

    Please help,
    Thanks

  25. How can we display child row by using ssp.customized.class.php ?

    as similar as http://datatables.net/examples/server_side/row_details.html

    it seems that the definition of ‘dt’ cannot used as above link because ssp.customized.class.php use ‘dt’ => 0 to identify which column to show the field, while the above link use ‘dt’ as field name;

    also even apply function format(d) {….}

    function format ( d ) {
    return ‘Full name: ‘+d.first_name+’ ‘+d.last_name+”+
    ‘Salary: ‘+d.salary+”+
    ‘The child row can contain any data you wish, including links, images, inner tables etc.’;
    }

    ssp.customized.class.php seems can generate ‘row_’.$d;

    array(
    ‘db’ => ‘id’,
    ‘dt’ => ‘DT_RowId’,
    ‘formatter’ => function( $d, $row ) {

    return ‘row_’.$d;
    }
    ),

    where I have already tried to convert as below to keep ‘dt’ => 0

    array(
    ‘db’ => ‘id’,
    ‘dt’ => 0 ,
    ‘field’ => ‘DT_RowId’,
    ‘as’ => ‘DT_RowId’,
    ‘formatter’ => function( $d, $row ) {

    return ‘row_’.$d;
    }
    ),

    If possible please give us an example on creating child row by using ssp.customized.class.php

    Thanks indeed

  26. Can we apply ssp.customized.class.php with datatables’ Search Api so that we can process multiple filters at one single column as below link ?

    http://www.datatables.net/release-datatables/examples/api/regex.html

    I have tried to put all functions inside the project but it is no outcome for inputting

    “London|Tokyo” at Column office as what the above link can show,

    Will there be any amendment on $sWhere at ssp.customized.class.php ?

    If possible, please post example for us

    Thanks indeed

  27. Q. can you explain the different on applying $where and $extraWhere ?
    on what condition should on apply one of them or
    can / should we apply them both ?

    any example for helping me to understand ? Thanks

  28. I need to $order the call_date in descending to get the last date before $groupBy but it still return me the smallest date, please help, my code as below

    $columns = array(
    array( ‘db’ => ‘`s`.`id`’,’dt’ => 0, ‘field’ => ‘id’, ‘as’=>’id’ ),
    array( ‘db’ => ‘`s`.`stud_name`’, ‘dt’ => 1,’field’ => ‘stud_name’ , ‘as’=>’stud_name’ ),
    array( ‘db’ => ‘`c`.`call_date`’,’dt’ => 2,’field’ => ‘Last_date’, ‘as’=>’Last_date’ ),
    array( ‘db’ => ‘COUNT(`c`.`stud_id`)’, ‘dt’ => 3, ‘field’ => ‘call_total’, “‘as’=>’call_total’ ) );

    $joinQuery = ” FROM `mc_stud` AS `s`
    LEFT JOIN `mc_call` A `c` ON (`c`.`stud_id` = `s`.`id`) “;

    $extraWhere = ‘count(`c`.`id`) =’.$today ‘ ;

    $order = ‘`c`.`call_date` DESC ‘;

    $groupBy = ‘ `c`.`stud_id`’;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $order, $groupBy)
    );

    • jonnyhnds7 says:

      Hi, How can i add my custom ”ORDER BY ” parameters. Also I want it on DESC order. so help me to work on it.

  29. Please help..
    Searching / Filtering not working if use GROUP_CONCAT ?

    array( ‘db’ => ‘GROUP_CONCAT(`ml`.`lawyeralias` )’, ‘dt’ => 3, ‘field’ => ‘lawyeralias’, ‘as’ => ‘lawyeralias’ ),

    • search this script (no 104 on spp.php):
      for ($i = 0, $ien = count($request[‘columns’]); $i < $ien; $i++) {
      $requestColumn = $request['columns'][$i];
      $columnIdx = array_search($requestColumn['data'], $dtColumns);
      $column = $columns[$columnIdx];

      and add this script in the below :
      if(strpos($column['db'], "CONCAT") !== false) {
      continue;
      }

      it means that you can skip the search for CONCAT / GROUP_CONCAT
      if you wish,

      (Sorry for bad english)

  30. darwinsantos says:

    Can the script be modified for implicit joins?
    Something like
    =========
    FROM `user` AS `u`,`user_details` AS `ud`
    WHERE `ud`.`user_id` = `u`.`id` AND `u`.`salary` >= 90000
    =========

    • Emran Ul hadi says:

      I think it should work in a same way like JOIN works

      
      $joinQuery = "FROM `user` AS `u`, `user_details` AS `ud` ";
      $extraWhere = " `ud`.`user_id` = `u`.`id` AND `u`.`salary` >= 90000";        
      
      

      Try this, and let me know your findings.

      Thanks

      • darwinsantos says:

        I tried that before posting my comment and it didn’t work, but now I tried it with your code and it worked flawlessly, thank you very much!

  31. Hi Emran .. been searching high and low for such solution .. used to be using ignited when it was 1.9.x .. however I need to ask you this … what is I have 2 tables what I wanna join but both tables have the same column name as index column .. namely ‘id’ … say a.id and b.id … what do I put as $primaryKey so it would not error as ambiguous?

  32. he sir
    how do i retrieve the Id in my linked file for the case of edit
    array( ‘db’ => ‘id’, ‘dt’ => 8, ‘formatter’ => function( $d, $row ) {
    return ‘ Edit‘;},
    ‘field’ => ‘id’ ),
    basing on that example when i head to view_sales.php how to i get the id.

    trhanx

  33. Hi,

    I’m using your ssp library for some time and I never had any problems, but now I’m facing one problems and I would like your help:

    I need to show in one result the text of two separated tables, for example in one table I have the first name of a person and at the second table I have the last name of a person, then I need to show the first name + last name together in a single result.

    How can I do this?

    (I did it by php function, the result is OK, but then it don’t reflect on search and ordering)

    Thanks.

  34. Hi Emran,

    Great job, just implemented it for left joins with concat_ws and it works perfect.

    array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`first_name`, `a`.`last_name` )’, ‘dt’ => 2, ‘field’ => ‘full_name’, ‘as’ => ‘full_name’ ),

    I have only a problem, maybe it’s my fault, in column with concat I have problem with search/order. Do you have any example or suggestion for have a column ()

    Thank you very much!

  35. Hi Emran,

    Great job, just implemented it with CONCAT_WS and it works perfect.

    array( ‘db’ => ‘CONCAT_WS( ” “, `a`.`first_name`, `a`.`last_name` )’, ‘dt’ => 2, ‘field’ => ‘full_name’, ‘as’ => ‘full_name’ ),

    But I have a problem with search/order with that column do you have example or sugestion to solve my problem ?

    Thank you very much!

  36. HELLO!

    Just wanted to say thanks for your script , i managed to achieve the multiple join queries , my sample code looks something like this

    ‘`u`.`first_name`’, ‘dt’ => 0, ‘field’ => ‘first_name’ ),
    array( ‘db’ => ‘`u`.`last_name`’, ‘dt’ => 1, ‘field’ => ‘last_name’ ),
    array( ‘db’ => ‘`u`.`position`’, ‘dt’ => 2, ‘field’ => ‘position’ ),
    array( ‘db’ => ‘`u`.`office`’, ‘dt’ => 3, ‘field’ => ‘office’),
    array( ‘db’ => ‘`ud`.`email`’, ‘dt’ => 4, ‘field’ => ’email’ ),
    array( ‘db’ => ‘`ud`.`phone`’, ‘dt’ => 5, ‘field’ => ‘phone’ ),
    array( ‘db’ => ‘`u`.`start_date`’, ‘dt’ => 6, ‘field’ => ‘start_date’, ‘formatter’ => function( $d, $row ) {return date( ‘jS M y’, strtotime($d));}),
    array(‘db’ => ‘`u`.`salary`’, ‘dt’ => 7, ‘field’ => ‘salary’, ‘formatter’ => function( $d, $row ) {return ‘$’.number_format($d);})
    );

    **/

    $columns = array(
    array( ‘db’ => ‘`la`.`appId`’, ‘dt’ => 0, ‘field’ => ‘appId’ ),
    array( ‘db’ => ‘`la`.`licFeeId`’, ‘dt’ => 1, ‘field’ => ‘licFeeId’ ),
    array( ‘db’ => ‘`la`.`applicationDate`’, ‘dt’ => 2, ‘field’ => ‘applicationDate’),
    array( ‘db’ => ‘`sc`.`statusName`’, ‘dt’ => 3, ‘field’ => ‘statusName’),
    array( ‘db’ => ‘`cd`.`companyName`’, ‘dt’ => 4, ‘field’ => ‘companyName’ ),
    array( ‘db’ => ‘`cd`.`phoneNumber`’, ‘dt’ => 5, ‘field’ => ‘phoneNumber’ ),
    array( ‘db’ => ‘`lf`.`applicationFee`’, ‘dt’ => 6, ‘field’ => ‘applicationFee’,’formatter’ => function( $d, $row ) {return ‘K’.number_format($d);}),
    array( ‘db’ => ‘`sc`.`statusId`’, ‘dt’ => 7, ‘field’ => ‘statusId’)
    );

    // SQL server connection information
    require(‘config.php’);
    $sql_details = array(
    ‘user’ => $db_username,
    ‘pass’ => $db_password,
    ‘db’ => $db_name,
    ‘host’ => $db_host
    );

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    * If you just want to use the basic configuration for DataTables with PHP
    * server-side, there is no need to edit below this line.
    */

    // require( ‘ssp.class.php’ );
    require(‘ssp.customized.class.php’ );

    $joinQuery = “FROM `license_application` AS `la`
    INNER JOIN `company_details` AS `cd`
    ON (`la`.`companyId` = `cd`.`companyId`)
    INNER JOIN `license_fee` AS `lf`
    ON (`la`.`licFeeId` = `lf`.`licFeeId`)
    INNER JOIN `status_catergory` AS `sc`
    ON (`la`.`statusId` = `sc`.`statusId`)
    “;

    $extraWhere = “`la`.`statusId` NOT IN (1,2)”;

    echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere )
    );

    WORKS PERFECT !

  37. Hi there,

    I use your SSP code all the time, but now I’ll struggling to implement a where clause with brackets.

    WHERE (this = that OR this = that) AND this = that AND this = that

    Let me know if you think this is possible.
    Thanks

    • Emran Ul hadi says:

      Hello Ellis

      Sorry for the Late reply.

      You have to pass the conditions like this

      $extraCondition = "`this`=that;
      

      whenever you have multiple condition then you can pass the condition statement like this

      $extraCondition = " (this = that OR this = that) AND this = that AND this = that";
      

      And pass it within `SSP::simple` function. It should work, I think.

      Let me know

  38. Hi and thank you for your post!

    I have the following error when I try to use the ‘formatter’ option:
    “DataTables warning: table id=table-experiences – Ajax error. For more information about this error, please see http://datatables.net/tn/7

    My code:

    array(
    ‘db’ => ‘e.ProducerId’,
    ‘dt’ => 13,
    ‘formatter’ => function( $d, $row ) {
    return $d;
    },
    ‘field’ => ‘ProducerName’,
    ‘as’ => ‘ProducerName’,
    ),

    Any ideas?? Thanks

    • Emran Ul hadi says:

      Hello Max,

      Without seeing all the code it’s difficult to find out the problem.
      First one it could be

      ‘db’ => `e`.`ProducerId`

      And in formatter I don’t see anything wrong, besides you don’t change any output.

      Hope you fix this out.

  39. Hi and thank you for your post!

    I have the following error when I try to formate a column server-side:

    “DataTables warning: table id=table-experiences – Ajax error. For more information about this error, please see http://datatables.net/tn/7

    My code:

    array( ‘db’ => ‘e.ProducerId’,
    ‘dt’ => 13,
    ‘formatter’ => function( $d, $row ) {
    return $d;
    },
    ‘field’ => ‘ProducerName’,
    ‘as’ => ‘ProducerName’,
    ),

    Any ideas?? Thanks

  40. Hi Emran, thanks for a great script!
    One question:

    I have multiple extra conditions and they work great. However, the result is multiple rows of results for each of the extra conditions. Is there a way to display each of the results for the same primary key as a separate column, depending on the extra condition it came from?

    Thx in advance.

  41. how concat if table not join ? because iam trying not work thanks this my script

    ‘username’, ‘dt’ => 1 ),
    array( ‘db’ => ‘nama’, ‘dt’ => 2 ),
    array( ‘db’ => ‘level’, ‘dt’ => 3 ),
    array( ‘db’ => ‘blokir_admin’, ‘dt’ => 4, ‘formatter’ => function( $data, $row ) {
    // Status Admin
    if($data==’T’){
    $status = ‘Aktif’;
    $status = “$status”;
    }else{
    $status = ‘Diblokir’;
    $status = “$status”;
    }
    return $status;
    }
    ),
    array( ‘db’ => ‘CONCAT_WS(” “,`kd_administrator`, `foto_admin`)’, ‘dt’ => 5, ‘field’ => ‘Name’, ‘as’ => ‘Name’, ‘formatter’ => function( $data, $row ) {
    // Tombol Action
    $tombol_action = ‘ Edit Hapus‘;
    return $tombol_action;
    }
    )
    );
    // SQL server connection information
    require(‘../../../include/koneksi.php’);
    $sql_details = array(
    ‘user’ => $user,
    ‘pass’ => $password,
    ‘db’ => $database,
    ‘host’ => $host
    );

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    * If you just want to use the basic configuration for DataTables with PHP
    * server-side, there is no need to edit below this line.
    */

    require( ‘../../../include/ssp.class.php’ );

    $join = NULL;
    $where = “username !=’admin'”;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $join, $where )
    );
    ?>

  42. When apply ssp.customized.class.php for having sum or count function with group function, search box in datatable is not workable, it show below sql error.

    can any one help ?

    my script is sth like

    $joinQuery = “FROM `xxx_users` AS `s`
    LEFT JOIN `xxx_pcc_stud_ceus` AS `sc` ON (`sc`.`stud_id` = `s`.`id`)”;

    $groupBy = ‘`s`.`id`’;

    $extraWhere = ‘`s`.`id` >=1000’ ;
    $where = ‘Having SUM(`sc`.`credits`) >=0’;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $groupBy,$extraWhere, $where)
    );

    DataTables warning: table id=firstTable – An SQL error occurred: SQLSTATE[HY000]: General error: 1111 Invalid use of group function

    Thanks a lot

    Fion

  43. can we apply WHERE NOT EXISTS ( ) as mysql like below at ssp.customized.class.php

    SELECT ….
    FROM tbl_A
    LEFT JOIN tbl_B ON tbl_B.a_id = tbl_A.id

    WHERE NOT EXISTS( SELECT * FROM tbl_C. WHERE tbl_C.a_id = tbl_A.id )

    AND tbl_A.is_active != 0

    Thanks

  44. My Query is like this:
    SELECT p.*,
    — tbl_figures
    CAST(GROUP_CONCAT(f.figure_caption SEPARATOR ‘,’) AS CHAR) as figure_caption,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.analysis_tools,’,’, COALESCE(t.analysis_tools, ”)) SEPARATOR ‘,’) AS CHAR) as analysis_tools,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.density,’,’, COALESCE(t.density, ”)) SEPARATOR ‘,’) AS CHAR) as density,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.internal_energy,’,’, COALESCE(t.internal_energy, ”)) SEPARATOR ‘,’) AS CHAR) as internal_energy,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.LCO_parameter,’,’, COALESCE(t.LCO_parameter, ”)) SEPARATOR ‘,’) AS CHAR) as LCO_parameter,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.AC_freezing_depth,’,’, COALESCE(t.AC_freezing_depth, ”)) SEPARATOR ‘,’) AS CHAR) as AC_freezing_depth,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.system_packing,’,’, COALESCE(t.system_packing, ”)) SEPARATOR ‘,’) AS CHAR) as system_packing,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.bulk_modulus,’,’, COALESCE(t.bulk_modulus, ”)) SEPARATOR ‘,’) AS CHAR) as bulk_modulus,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.IS_function,’,’, COALESCE(t.IS_function, ”)) SEPARATOR ‘,’) AS CHAR) as IS_function,
    CAST(GROUP_CONCAT(DISTINCT CONCAT(f.rotational_relaxation,’,’, COALESCE(t.rotational_relaxation, ”)) SEPARATOR ‘,’) AS CHAR) as rotational_relaxation,

    — tbl_tables
    CAST(GROUP_CONCAT(t.table_caption SEPARATOR ‘,’) AS CHAR) as table_caption,
    — CAST(GROUP_CONCAT(t.analysis_tools SEPARATOR ‘,’) AS CHAR) as analysis_tools,
    — CAST(GROUP_CONCAT(t.density SEPARATOR ‘,’) AS CHAR) as density,
    — CAST(GROUP_CONCAT(t.internal_energy SEPARATOR ‘,’) AS CHAR) as internal_energy,
    — CAST(GROUP_CONCAT(t.LCO_parameter SEPARATOR ‘,’) AS CHAR) as LCO_parameter,
    — CAST(GROUP_CONCAT(t.AC_freezing_depth SEPARATOR ‘,’) AS CHAR) as AC_freezing_depth,
    — CAST(GROUP_CONCAT(t.system_packing SEPARATOR ‘,’) AS CHAR) as system_packing,
    — CAST(GROUP_CONCAT(t.bulk_modulus SEPARATOR ‘,’) AS CHAR) as bulk_modulus,
    — CAST(GROUP_CONCAT(t.IS_function SEPARATOR ‘,’) AS CHAR) as IS_function,
    — CAST(GROUP_CONCAT(t.rotational_relaxation SEPARATOR ‘,’) AS CHAR) as rotational_relaxation,

    — tbl_simulationdata
    CAST(GROUP_CONCAT(DISTINCT(sd.material) SEPARATOR ‘,’) AS CHAR) as material,
    CAST(GROUP_CONCAT(DISTINCT(sd.simulation_granularity) SEPARATOR ‘,’) AS CHAR) as simulation_granularity,
    CAST(GROUP_CONCAT(DISTINCT(if(sd.liquid_cool = 1, ‘liquid cooling’, NULL)) SEPARATOR ‘,’) AS CHAR) as liquid_cool,
    CAST(GROUP_CONCAT(DISTINCT(if(sd.PVD = 1, ‘pvd’, NULL)) SEPARATOR ‘,’) AS CHAR) as PVD,
    CAST(GROUP_CONCAT(DISTINCT(sd.CoolingRate) SEPARATOR ‘,’) AS CHAR) as CoolingRate,
    CAST(GROUP_CONCAT(DISTINCT(sd.CRUnit) SEPARATOR ‘,’) AS CHAR) as CRUnit,
    CAST(GROUP_CONCAT(DISTINCT(sd.DepositionRate) SEPARATOR ‘,’) AS CHAR) as DepositionRate,
    CAST(GROUP_CONCAT(DISTINCT(sd.DRUnit) SEPARATOR ‘,’) AS CHAR) as DRUnit,
    CAST(GROUP_CONCAT(DISTINCT(sd.SubstrateTemp) SEPARATOR ‘,’) AS CHAR) as SubstrateTemp,
    CAST(GROUP_CONCAT(DISTINCT(sd.STUnit) SEPARATOR ‘,’) AS CHAR) as STUnit

    — tbl_papers
    FROM tbl_papers p
    LEFT JOIN tbl_paper_figure_map pfm ON pfm.paper_id = p.paper_id
    LEFT JOIN tbl_figures f ON f.figure_id = pfm.figure_id
    LEFT JOIN tbl_figure_simulationdata_map fsdm ON fsdm.figure_id = f.figure_id

    LEFT JOIN tbl_paper_table_map ptm ON ptm.paper_id = p.paper_id
    LEFT JOIN tbl_tables t ON t.table_id = ptm.table_id
    LEFT JOIN tbl_table_simulationdata_map tsdm ON tsdm.table_id = t.table_id

    LEFT JOIN tbl_simulationdata sd ON (sd.simulationdata_id = fsdm.simulationdata_id OR sd.simulationdata_id = tsdm.simulationdata_id)

    GROUP BY (p.paper_id);

    I am trying to figure out how can I use the customized SSP to handle these kind of select columns? Please help and suggest if any thoughts. Thanks,

  45. Sudhansu Bhatta says:

    Its a very good plugin and i have used this in couple of projects.
    Now i am using this class with composit primary key and getting duplicate rows.

    I found a sloution by changing SQL_CALC_FOUND_ROWS to DISTINCT in the select statement but that mess with the paging and number_of_rows .

    Can anyone help with this issue?

    Thanks in advance

  46. First of all I would like to thank you very much. But there is a Turkish character problem in the return from Json. How can I solve it. (Database encode UTF-8-general, PHP encode UTF-8) Return “E.SÖĞÜTÇÜK” Returned data “E.SÃ-ĞÌTà ‡ ÃœK” There are problems with Turkish special characters. (I, İ, ş, Ş, ğ, Ğ) etc

  47. Hi Emran.
    I have a suggestion for your DataTables SSP class.

    The variable $joinQuery (a string) is passed into the “simple()” function. However, when it is passed internally to other functions it is referred to as $isJoin with a default of “false”. This makes it seem to be a boolean. I suggest it is referred to as $joinQuery throughout.

    Regards,

  48. Hi Emran.

    I have a problem with join table, if can you help about this subject i’m very happy for this.

    My problem is
    I have an employers table for working area and before working area, for example now working area code value is “123” and before working area value is “456”, when i want to show working area names, its comes same name, but workings area is different. so can i use

    array( ‘db’ => ‘`area`.`area_name`’, ‘dt’ => 0, ‘field’ => ‘working_area_first’, ‘as’ => ‘working_area_first’),
    array( ‘db’ => ‘`area`.`area_name`’, ‘dt’ => 1, ‘field’ => ‘working_area_second’, ‘as’ => ‘working_area_second’),

    the working area values comes, employer table ?

  49. Does not work

    ‘`u`.`user`’, ‘dt’ => 0, ‘field’ => ‘user’, ‘as’ => ‘user’ ),
    array( ‘db’ => ‘`u`.`contrasenia`’, ‘dt’ => 1, ‘field’ => ‘contrasenia’ ),
    array( ‘db’ => ‘`u`.`pin`’, ‘dt’ => 2, ‘field’ => ‘pin’ ),
    array( ‘db’ => ‘`u`.`status`’, ‘dt’ => 3, ‘field’ => ‘status’),
    array( ‘db’ => ‘`ud`.`user`’, ‘dt’ => 4, ‘field’ => ‘user’, ‘as’ => ‘user’ ),
    array( ‘db’ => ‘`u`.`fecha_vencimiento`’, ‘dt’ => 5, ‘field’ => ‘fecha_vencimiento’, ‘formatter’ => function( $d, $row ) {
    return date( ‘d-M-Y’, strtotime($d));
    }),
    array(‘db’ => ‘`u`.`idu_dispositivo`’, ‘dt’ => 6, ‘field’ => ‘idu_dispositivo’),
    array(‘db’ => ‘`u`.`idu_dispositivo`’, ‘dt’ => 7, ‘field’ => ‘idu_dispositivo’)
    );
    // SQL server connection information
    require(‘config.php’);
    $sql_details = array(
    ‘user’ => $db_username,
    ‘pass’ => $db_password,
    ‘db’ => $db_name,
    ‘host’ => $db_host
    );
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    * If you just want to use the basic configuration for DataTables with PHP
    * server-side, there is no need to edit below this line.
    */
    // require( ‘ssp.class.php’ );
    require(‘ssp.customized.class.php’ );
    $joinQuery = “FROM `usuario_vod` AS `u` JOIN `usuario` AS `ud` ON (`ud`.`cve` = `u`.`cve_dealer`)”;
    $extraWhere = “”;
    echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere )
    );
    In the user fields I throw the same data since they use the same field the 2 tables

  50. GROUP CONCAT ERROR in datatables server-side processing.php

    as below coding,

    array( ‘db’ => ‘CAST(GROUP_CONCAT(DISTINCT(xxx_pcc_stud_cour.course_code) SEPARATOR ‘,’)AS course_code)’, ‘dt’ => 4, ‘field’ => ‘course_code’, ‘as’ => ‘course_code’),

    error message :

    An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘AS course_code, CAST(GROUP_CONCAT(DISTINCT(aasfp_pcc_stud_cour.prog_code) SEPARA’ at line 1

    Please point out where is my mistakes

    Thanks

    Fchan1234

  51. How do you handle one to many joins for example the one below
    LEFT JOIN `user_accounts` AS `driverdetails` ON (`driverdetails`.`id` = `driver_asset_allocation`.`driver_id`)
    LEFT JOIN `user_accounts` AS `addedbydetails` ON (`addedbydetails`.`id` = `driver_asset_allocation`.`added_by`)
    LEFT JOIN `user_accounts` AS `clearedbydetails` ON (`clearedbydetails`.`id` = `driver_asset_allocation`.`cleared_by`)

    And then reference the same column for each join.

    • Emran Ul hadi says:

      Hello Victor,

      I think it doesn’t matter how many joins do you have, it should work in a same way.
      Each table you join has the alias name right, by which you can prepare the column array.

      I think that will help you.

      Thanks

  52. URGENT ! URGENT ! URGENT !
    1111 Invalid use of group function !
    1111 Invalid use of group function !
    1111 Invalid use of group function !

    When apply ssp.customized.class.php for having SUM or COUNT function with group function, search box in datatable is not workable, it show below sql error.

    can any one help ?

    my script is sth like

    $joinQuery = “FROM `xxx_users` AS `s`
    LEFT JOIN `xxx_pcc_stud_ceus` AS `sc` ON (`sc`.`stud_id` = `s`.`id`)”;

    $groupBy = ‘`s`.`id`’;

    $extraWhere = ‘`s`.`id` >=1000’ ;
    $where = ‘Having SUM(`sc`.`credits`) >=0’;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $groupBy,$extraWhere, $where)
    );

    DataTables warning: table id=firstTable – An SQL error occurred: SQLSTATE[HY000]: General error: 1111 Invalid use of group function

    Thanks a lot

    P.S. I have searched Google most of the findings show using ‘Having’ to solve the problem but ssp.customized.class.php do not have ‘having’ right?
    so how to solve the ‘1111 Invalid use of group function’

    Fion

    • Emran Ul hadi says:

      Hello Wing Chan,

      First of all the present ssp class doesn’t support HAVING query right now, where did you get this??
      you changed this one yourself or something, then it works.

      by the way, the problem is in how you passed the parameter in wrong order.

      Group by parameter is after extra condition. Like this.

      echo json_encode(
      SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery,$extraWhere, $groupBy, $where)
      );

      For use HAVING, i have send you another version of `ssp.customized.class.php` via Mail,
      I will update repo and readme as well very soon

      Hope that helps.

      Thanks

      • Dear Emran

        Thanks so much for your prompt reply , HOWEVER !

        i have tried to apply the $having as below , however still got 1111 invalid of group function

        array( ‘db’ => ‘`m`.`helper_rm`’, ‘dt’ => 15, ‘field’ => ‘helper_rm’ , ‘as’ => ‘helper_rm’),
        array( ‘db’ => ‘count(`mc`.`id`)’, ‘dt’ =>16, ‘field’ =>’call_total’, ‘as’ => ‘call_total’ )
        );

        require(‘/home/abc/public_html/modules/datatablesRes/php/config.php’);
        $sql_details = array(
        ‘user’ => $db_username,
        ‘pass’ => $db_password,
        ‘db’ => $db_name,
        ‘host’ => $db_host
        );

        require(‘ssp.customized.class.php’);

        $joinQuery = “FROM `abc_users` AS `m`
        LEFT JOIN `abc_mc_call_mem` AS `mc` ON (`mc`.`member_id` = `m`.`id`)
        LEFT JOIN `abc_mc_dn_gp` AS `gp` ON (`gp`.`cus_id` = `m`.`cus_id`)”;

        $extraWhere = ‘`m`.`super_id` = ‘. $superID . ‘ AND m.sales_id != 0’ ;
        $groupBy = ‘`m`.`id`’;
        $having = ‘count(`mc`.`id`)>=0’;

        echo json_encode(
        SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy, $having)
        );

  53. $columns = array(
    array(‘db’ => ‘code_assetregist’, ‘dt’ => ‘code_assetregist’),
    array(‘db’ => ‘nopolisi’, ‘dt’ => ‘nopolisi’),
    array( ‘db’ => ‘CONCAT_WS( ” “, `nm_asset`, `merk` )’, ‘dt’ => 2, ‘field’ => ‘name’, ‘as’ => ‘name’ ),

    error in last line :
    {“error”:”An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘`, `merk` )`,

    kindly your help

  54. I found the answer myself. i must be fill in variabel joinquery.
    $joinQuery =’FROM `arp` AS `a`’;

    follow my script below :
    $columns = array(
    array(‘db’ => ‘code_assetregist’, ‘dt’ => ‘code_assetregist’,’field’ => ‘code_assetregist’ ),
    array(‘db’ => ‘nopolisi’, ‘dt’ => ‘nopolisi’,’field’ => ‘nopolisi’ ),
    array( ‘db’ => ‘CONCAT(`nm_asset`,” “, `merk`,””,`tipe`)’, ‘dt’ => ‘nmkendaraan’,’field’ => ‘name’, ‘as’ => ‘name’),
    // array( ‘db’ => ‘CONCAT_WS( ” “, `u`.`first_name`, `u`.`last_name` )’, ‘dt’ => 0, ‘field’ => ‘name’, ‘as’ => ‘name’ ),
    /* array(‘db’ => ‘nopolisi’, ‘dt’ => ‘nmkendaraan’),*/
    //array(‘db’ => ‘nopolisi’, ‘dt’ => ‘filekendaraan’,’field’ => ‘nopolisi’ ),
    // array(‘db’ => ‘nopolisi’, ‘dt’ => ‘ketkendaraan’,’field’ => ‘nopolisi’ ),
    /*array( ‘db’ => ‘CONCAT(`nm_asset`, ” “, `merk`, “”, `tipe`)’,
    ‘dt’ => “nmkendaraan”),*/
    /*array( ‘db’ => “CONCAT(‘nm_asset’, ‘ ‘, ‘merk’, ”, ‘tipe’)”,
    ‘dt’ => “filekendaraan”),*/
    array( ‘db’ => “CONCAT(‘Image Kendaraan‘, ”, ‘Image STNK
    , ‘Image BPKB‘)”,
    ‘dt’ => “filekendaraan”,’field’ => ‘filekendaraan’, ‘as’ => ‘filekendaraan’),
    array(‘db’ => ‘stnkexpire’ , ‘dt’ => ‘stnkexpire’,’field’ => ‘stnkexpire’ ),
    array(‘db’ => ‘statuskendaraan’ , ‘dt’ => ‘statuskendaraan’,’field’ => ‘statuskendaraan’ ),
    array( ‘db’ => “CONCAT(‘Pengguna : ‘,`pengguna`, ”, `Tanggal Jual : `, `tanggaljual`,”
    , ‘Jual Kepada : ‘, `jualkepada`,”,’Bukti Jual : ‘,`image_bukti_jual`)”,
    ‘dt’ => “ketkendaraan”),
    array(‘db’ => ‘ptasuransi’ , ‘dt’ => ‘ptasuransi’,’field’ => ‘ptasuransi’ ),
    array(‘db’ => ‘tipeasuransi’ , ‘dt’ => ‘tipeasuransi’,’field’ => ‘tipeasuransi’ ),
    array(‘db’ => ‘asuransiexpire’ , ‘dt’ => ‘asuransiexpire’,’field’ => ‘asuransiexpire’ ),
    array(
    ‘db’ => ‘code_assetregist’,
    ‘dt’ => ‘aksi’,
    ‘field’ => ‘code_assetregist’,
    ‘formatter’ => function( $d ) {
    return “Detail”;
    }

    ),
    );
    $joinQuery =’FROM `arp` AS `a`’;
    $where = ”;

    // SQL server connection information

    echo json_encode(
    SSP::simple($_GET, $this->dttable, $table, $primaryKey, $columns,$joinQuery,$where)
    );

    }

    i am using view in arp on database

    thanks your library

  55. Hi Emran,

    Thanks so much for providing these scripts, they’re great!

    I’ve been building a pretty complex datatable and I’ve hit a snag which I think is similar to Fion Chan’s above. At least the error is the same: “1111 Invalid use of group function” when trying to search using the datatables search box.

    I’ll explain my setup in case you or anyone else has worked through this.

    I have 2 tables that I need to join.

    Table 1
    id form_id
    1 1
    2 2
    3 2
    4 1
    5 1

    Table 2
    item_id field_id meta_value
    1 1 John
    1 2 NULL
    1 3 Smith
    2 4 Yes
    3 4 Yes
    4 1 Bob
    4 2 NULL
    4 3 Ross
    5 1 Billy
    5 2 Bob
    5 3 Johnson

    I want to join and pivot these tables into a datatable like this:

    id first_name middle_name last_name
    1 John NULL Smith
    4 Bob NULL Ross
    5 Billy Bob Johnson

    I started out using your script like this, with several joins:

    $columns = array(
    array( ‘db’ => ‘`first_name`.`meta_value`’, ‘dt’ => 0, ‘field’ => ‘first_name’, ‘as’ => ‘first_name’ ),
    array( ‘db’ => ‘`middle_name`.`meta_value`’, ‘dt’ => 1, ‘field’ => ‘middle_name’, ‘as’ => ‘middle_name’ ),
    array( ‘db’ => ‘`last_name`.`meta_value`’, ‘dt’ => 2, ‘field’ => ‘last_name’, ‘as’ => ‘last_name’ ));

    $joinQuery = “FROM `table1`
    LEFT JOIN `table2` AS `id` ON (`table1`.`id` = `id`.`item_id`)
    LEFT JOIN `table2` AS `first_name` ON (`table1`.`id` = `first_name`.`item_id` AND `first_name`.`field_id`=1)
    LEFT JOIN `table2` AS `middle_name` ON (`table1`.`id` = `middle_name`.`item_id` AND `middle_name`.`field_id`=2)
    LEFT JOIN `table2` AS `last_name` ON (`table1`.`id` = `last_name`.`item_id` AND `last_name`.`field_id`=3)”;

    $extraWhere = “`table1`.`form_id` = ‘1’”;

    $groupBy = ‘`table1`.`id`’;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy )
    );

    All was going great until I added 61 joins and found out the MySQL can only do that many. It won’t do more. My second table currently has over 80 field_ids (attributes and corresponding meta_values). I’ve seen suggestions to create multiple temporary tables via less than 61 joins and then join the temporary tables for your query. I haven’t tried that yet.

    I did find a different approach that seems to work and doesn’t hit the 61 joins limit, but that’s where I’m getting the “1111 Invalid use of group function” error when searching via datatables search box.

    $columns = array(
    array( ‘db’ => ‘MAX( IF(item_metas.field_id = 1, item_metas.meta_value, NULL) )’, ‘dt’ => 0, ‘field’ => ‘first_name’, ‘as’ => ‘first_name’ ),
    array( ‘db’ => ‘MAX( IF(item_metas.field_id = 2, item_metas.meta_value, NULL) )’, ‘dt’ => 1, ‘field’ => ‘middle_name’, ‘as’ => ‘middle_name’ ),
    array( ‘db’ => ‘MAX( IF(item_metas.field_id = 3, item_metas.meta_value, NULL) )’, ‘dt’ => 2, ‘field’ => ‘last_name’, ‘as’ => ‘last_name’ ));

    $joinQuery = “FROM `table1` LEFT JOIN `table2` `item_metas` ON `table1`.`id` = `item_metas`.`item_id`”;

    $extraWhere = “`table1`.`form_id` = ‘1’”;

    $groupBy = ‘`table1`.`id`’;

    echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy )
    );

    Any guidance would be much appreciated.

    Is there a way to us “HAVING” and your new script in my second scenario?

    Thanks again.

    Adam

    • Emran Ul hadi says:

      Thanks for your comment.

      I have added the HAVING option in the latest customised SSP class.
      You can check and use as well

  56. Dear Emran

    Please help 🙂

    I have just that columns:

    $columns = array(
    array( ‘db’ => ‘`r`.`ID`’, ‘dt’ => 0, ‘field’ => ‘ID’),
    array( ‘db’ => ‘`sd1`.`wartosc3`’, ‘dt’ => 1, ‘field’ => ‘wartosc3’),
    array( ‘db’ => ‘`sd2`.`wartosc3`’, ‘dt’ => 2, ‘field’ => ‘wartosc3’),
    array( ‘db’ => ‘`r`.`data_transakcji`’, ‘dt’ => 3, ‘field’ => ‘data_transakcji’),
    array( ‘db’ => ‘`sd3`.`wartosc3`’, ‘dt’ => 4, ‘field’ => ‘wartosc3’),
    array( ‘db’ => ‘`r`.`nr_czesci`’, ‘dt’ => 5, ‘field’ => ‘nr_czesci’),
    array( ‘db’ => ‘`sd4`.`wartosc3`’, ‘dt’ => 6, ‘field’ => ‘wartosc3’),
    );

    And i have MULTI JOIN to these same table. Is dictionary table.

    My Query:

    $query = “FROM `rejestr_tab` AS `r`
    LEFT JOIN `slownik_dane_tab` AS `sd1` ON `r`.`typ_obrotu_s` = `sd1`.`ID`
    LEFT JOIN `slownik_dane_tab` AS `sd2` ON `r`.`forma_transferu_s` = `sd2`.`ID`
    LEFT JOIN `slownik_dane_tab` AS `sd3` ON `r`.`opis_technologii_s` = `sd3`.`ID`
    LEFT JOIN `slownik_dane_tab` AS `sd4` ON `r`.`towar_s` = `sd4`.`ID`
    “;

    Execute this gives the result that in join field has always the last result of join.

    So the all field has has the result of join for last field.

    What is wrong?

    Thanks Michael.

    • Emran Ul hadi says:

      Hi Michel,

      Sorry for the Late reply but the it’s better to late than never, right 😉

      So the thing is, you missed the renaming part, as you have same field name then you must have to rename it otherwise it will replace by the last value, right. And may be you noticed theat you can rename the field name via defining as index at array column.

      The array column would be like this
      “`
      $columns = array(
      array( ‘db’ => ‘`r`.`ID`’, ‘dt’ => 0, ‘field’ => ‘ID’),
      array( ‘db’ => ‘`sd1`.`wartosc3`’, ‘dt’ => 1, ‘field’ => ‘wartosc3’, ‘as’ => ‘first_ wartosc3’),
      array( ‘db’ => ‘`sd2`.`wartosc3`’, ‘dt’ => 2, ‘field’ => ‘wartosc3’, , ‘as’ => ‘second_ wartosc3’),
      array( ‘db’ => ‘`r`.`data_transakcji`’, ‘dt’ => 3, ‘field’ => ‘data_transakcji’),
      array( ‘db’ => ‘`sd3`.`wartosc3`’, ‘dt’ => 4, ‘field’ => ‘wartosc3’, , ‘as’ => ‘third_ wartosc3’),
      array( ‘db’ => ‘`r`.`nr_czesci`’, ‘dt’ => 5, ‘field’ => ‘nr_czesci’),
      array( ‘db’ => ‘`sd4`.`wartosc3`’, ‘dt’ => 6, ‘field’ => ‘wartosc3’, , ‘as’ => ‘fourth_ wartosc3’),
      );
      “`
      Hope that helps.

      Cheers!!

      • Hi Emran

        Thanks for reply 🙂

        Temporary i used View’s on mysql database – so I solvet that this problem. 😉
        But I’m already doing another one datatables form and now i can used Your solution. 🙂

        Your parser is powerfull – keep going, don’t stop. 🙂
        Any response time is good 😉

  57. DataTables warning: table id=firstTable – An SQL error occurred: SQLSTATE[HY000]: General error: 1111 Invalid use of group function

    Ever apply your new script with $having

    Can you provide as an example with count / sum field which is finally apply $having for our better reference

    Thanks so much

    • Dear Emran,

      My previous failure example as below : –
      $having is in right order as your previous teaching: –

      :
      :
      array( ‘db’ => ‘`m`.`helper_rm`’, ‘dt’ => 15, ‘field’ => ‘helper_rm’ , ‘as’ => ‘helper_rm’),
      array( ‘db’ => ‘count(`mc`.`id`)’, ‘dt’ =>16, ‘field’ =>’call_total’, ‘as’ => ‘call_total’ )
      );

      require(‘config.php’);
      :
      :
      require(‘ssp.customized.class.php’);

      $joinQuery = “FROM `abc_users` AS `m`
      LEFT JOIN `abc_mc_call_mem` AS `mc` ON (`mc`.`member_id` = `m`.`id`)
      LEFT JOIN `abc_mc_dn_gp` AS `gp` ON (`gp`.`cus_id` = `m`.`cus_id`)”;

      $extraWhere = ‘`m`.`super_id` = ‘. $superID . ‘ AND m.sales_id != 0’ ;
      $groupBy = ‘`m`.`id`’;
      $having = ‘count(`mc`.`id`)>=0’;

      echo json_encode(
      SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy, $having)
      );

      Please teach me where I have wrong or would you mind giving us an example of count / sum function at $columns with $having

      Thanks so much for great help

      Fion

      • Dear Emran,

        It seems I found the solution on ERROR 1111 invalid of group function

        1. count / sum function not in $column but in $joinQuery
        2. no need to use $having

        I have amend my example as below:-

        //———— Fion example ———-//
        :
        :
        array( ‘db’ => ‘`m`.`helper_rm`’, ‘dt’ => 15, ‘field’ => ‘helper_rm’ , ‘as’ => ‘helper_rm’),
        array( ‘db’ => ‘`mc`.`call_total`’, ‘dt’ =>16, ‘field’ =>’call_total’, ‘as’ => ‘call_total’ )
        );

        require(‘config.php’);
        :
        :
        require(‘ssp.customized.class.php’);

        $joinQuery = “FROM `abc_users` AS `m`
        LEFT JOIN (SELECT member_id, COUNT(id) as call_total FROM abc_mc_call_mem GROUP BY member_id) AS `mc` ON (`mc`.`member_id` = `m`.`id`)
        LEFT JOIN `abc_mc_dn_gp` AS `gp` ON (`gp`.`cus_id` = `m`.`cus_id`)”;

        $extraWhere = ‘`m`.`super_id` = ‘. $superID . ‘ AND m.sales_id != 0’ ;
        $groupBy = ‘`m`.`id`’;

        echo json_encode(
        SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy)
        );

        Hope it can give some help for friends that have facing the same problem on “An SQL error occurred: SQLSTATE[HY000]: General error: 1111 Invalid use of group function”

        Fion

  58. Hi Emran Ul hadi.Thank’s for this great script.It helps me a lot.But i’am facing with a problem.Table is displaying but serching and ordering is not working.It dose’nt show any error ,jast dose’n search or order.What i’am doing wrong ?

    • Emran Ul hadi says:

      Hello Armand,

      I am glad that it helped. And It’s little bit difficult to give solution without seeing your code.
      So can you please share your code block, so that I can help.

      Thank you

    • Emran Ul hadi says:

      Hello,

      There are plenty of example in above comments and in the blog as well.
      You can add as much as where clause within the script.

      I have copied from one comment and it works via something like this.

      $joinQuery = ” FROM `mc_stud` AS `s`
      LEFT JOIN `mc_call` A `c` ON (`c`.`stud_id` = `s`.`id`) “;
      
      $extraWhere = ‘count(`c`.`id`) =’.$today ‘ ;
      
      $order = ‘`c`.`call_date` DESC ‘;
      
      $groupBy = ‘ `c`.`stud_id`’;
      
      echo json_encode(
      SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $order, $groupBy)
      );
      

      Thanks

  59. URGENT URGENT URGENT

    Notice: Undefined index: columns in /home/xxx/public_html/modules/datatablesRes/php/ssp.customized.class.php on line 135
    Notice: Undefined index: draw in /home/xxx/public_html/modules/datatablesRes/php/ssp.customized.class.php on line 229

    {“draw”:0,”recordsTotal”:12409,”recordsFiltered”:7,”data”:[[“4114″,”7266″,”H060769″,”LAU, xxx”,”2″,”0 | 0″,”60 | 100″,null],[“4114″,”9949″,”H100507″,”CHEUNG, xxx”,”2″,”0 | 0″,”-2 | 100″,null],[“4114″,”10491″,”H110210″,”YEUNG, xxx”,”2″,”0 | 0″,”60 | 100″,null],[“4114″,”10981″,”H110701″,”LAM, xxx”,”2″,”0 | 0″,”-2 | 100″,null],[“4114″,”13400″,”H140765″,”CHAN, xxx”,”2″,”0 | 0″,”60 | 100″,null],[“4114″,”13409″,”H140774″,”LEUNG, xxx”,”2″,”0 | 0″,”60 | 100″,null],[“4114″,”13421″,”H140786″,”WONG, xxx”,”2″,”0 | 0″,”60 | 100″,null]]}

    as above comment in Chrome console, the data is actually loaded but cannot put into datatables

    //—— server-side-scripting — //
    $columns = array(
    array( ‘db’ => ‘`sm`.`course_id`’, ‘dt’ => 0, ‘field’ => ‘cid’, ‘as’ => ‘cid’ ),
    array( ‘db’ => ‘`sm`.`stud_id`’, ‘dt’ => 1, ‘field’ => ‘stud_id’, ‘as’ => ‘stud_id’ ),
    array( ‘db’ => ‘`s`.`username`’, ‘dt’ => 2, ‘field’ => ‘stud_code’, ‘as’ => ‘stud_code’ ),
    array( ‘db’ => ‘`s`.`name_en`’, ‘dt’ => 3, ‘field’ => ‘stud_name’, ‘as’ => ‘stud_name’ ),
    array( ‘db’ => ‘`c`.`exam_pass_num`’,’dt’ => 4, ‘field’ => ‘exam_pass_num’,’as’ => ‘exam_pass_num’ ),
    array( ‘db’ => ‘CONCAT_WS(” | “, format(SUM(`sm`.`stud_mark` * (1-abs(sign(`sm`.`exam_type_id`-4)))), 0), format(SUM(`sm`.`require_total` * (1-abs(sign(`sm`.`exam_type_id`-4)))), 0)) ‘, ‘dt’ => 5, ‘field’ => ‘T4_TH’, ‘as’ => ‘T4_TH’ ),
    array( ‘db’ => ‘CONCAT_WS(” | “, format(SUM(`sm`.`stud_mark` * (1-abs(sign(`sm`.`exam_type_id`-5)))), 0), format(SUM(`sm`.`require_total` * (1-abs(sign(`sm`.`exam_type_id`-5)))), 0)) ‘, ‘dt’ => 6, ‘field’ => ‘T5_TH’, ‘as’ => ‘T5_TH’ ),
    array( ‘db’ => ‘DATE(`r`.`remark`)’, ‘dt’ => 7, ‘field’ => ‘stud_remark’, ‘as’ => ‘stud_remark’ )
    );

    // ————- default.php
    function childRow ( id, data) {
    var table_3= jQuery(”);
    jQuery(‘#’+id)
    .empty()
    .append( table_3);

    table_3.DataTable({
    ajax: ‘/modules/mod_aa_cour_hist/php/studs.php?course_id=’+data[1],
    columns: [
    { title:”,
    data: null,
    defaultContent: ”,
    className: ‘select-checkbox’,
    orderable: false
    },
    {title: “CID”, field: “cid”},
    {title: “stud ID”, field: “stud_id”},
    {title: “stud name”, field: “name_en”},
    {title: “exam pass num”,field: “exam_pass_num”},
    {title: “TH”, field: “T4_TH”},
    {title: “PA”, field: “T5_PA”},
    {title: “stud remark”, field: “stud_remark”}
    ],
    select: {
    style: ‘os’,
    selector: ‘td:first-child’
    },

  60. HI ,
    i have this error :
    Notice: Undefined index: in ….on line 43

    Notice: Undefined index: field ….. on line 43

    this is my join query:
    $joinQuery = “FROM scarico_x_doc INNER JOIN vettore ON (scarico_x_doc.id_vettore = vettore.id_vettore) INNER JOIN cliente ON (scarico_x_doc.id_cliente = cliente.id_cliente) INNER JOIN articolo ON (scarico_x_doc.id_articolo = articolo.id_articolo) INNER JOIN gruppo_um ON (articolo.id_um = gruppo_um.id_um);

    how can i do to solve this problem ?
    thanks

  61. Hi, Great script you have developed.

    I read all above questions and answers and you solved them. Nice.

    Please help me out,
    I need:

    array( ‘db’ => ‘`s`.`id`’, ‘dt’ => 0, ‘field’ => ‘id’ ),
    array( ‘db’ => ‘`c`.`company_name`’, ‘dt’ => 1, ‘field’ => ‘company_name’ , ‘formatter’ => function( $d, $row ) {
    return ‘$d‘;
    })

    So here, i need id value from first array into

    Please let me know at earliest.

  62. Hi,

    Please answer this question asap. thanks will be from my side.

    Please help me out,
    I need:

    array( ‘db’ => ‘`s`.`id`’, ‘dt’ => 0, ‘field’ => ‘id’ ),
    array( ‘db’ => ‘`c`.`company_name`’, ‘dt’ => 1, ‘field’ => ‘company_name’ , ‘formatter’ => function( $d, $row ) {
    return $id.$d;
    })

    So here, i need $id value from first array into second array

Leave a Reply to Fabio Iyl Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s