DataTable ssp.class.php

CanisLupus

I asked this on StackOverflow, but it might be better to ask here since this is the DataTables home forum. I hope this makes sense.

Simply listing the contents of a MySQL table in DataTables, using server side-processing (ssp.class.php) is no problem at all. Works like a charm and I want to stress the fact that this does not concern the function "ShippingMethod" nor how to use regular DataTables in general.

I want to have a column in the DataTable that does NOT have a corresponding column in the MySQL table, and I want to populate this column with computed values, that take other column values from the MySQL table as parameters, how do I do?

MySQL table:
Contains three columns. As you can see, there is NO column here for Shipping Method.

Name | PartnerID | Express
Abe  | 23        | true
Eve  | 47        | false
Joe  | 47        | true

Pseudo-PHP function:
Returns a text-string containing shipping method, like "Plane", "Train" or "Automobile"

function ShippingMethod(PartnerID,Express) {
  return $computedDataTextString
}

HTML DataTable:
As you can see, the column Shipping Method here does not correspond to any column in the MySQL table.

<thead>
  <th>First Name</th>
  <th>Shipping Method</th>
</thead>

Prepping data for ssp.class.php
This is where I run into trouble. How do I get the results of the PHP-function (i.e. Shipping Method) in here? And how would I go about prepping the array, since there is no table column for this data?

$columns = array(
  array( 'db' => 'Name', 'dt' => 0 ), 
  array( 'db' => ???????, 'dt' => 1,

allan

In your Javascript, add an extra column to the columns array and set the columns.data option for it to be null. Then use columns.render to create the rendered string.

The server-side doesn't need to know anything about it.

Allan

CanisLupus

Hey Allan and thanks for the reply.

How would I do that? Doesnt' the renderer run after all server-side code has executed? How can I pass data from the javascript to the PHP-function? This doesn't work very well.

$('.datatablePrintLabels').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": "printlabel-ajax-call.php",
    "columnDefs": [{
      "targets":6,
      "render": function (data,type,row,meta) {
        var partner = row[1];
        var servicetype = row[2];
        var servicetypesubid = row[3];
        var shipmentid = row[4];
        var kollino = row[5];
        return "<?php getSupplierInfo(getSupplierByService("+partner+"|"+servicetype+"|"+servicetypesubid+"),"+shipmentid+";"+kolliid+");?>";
      },{
        "visible": false,
        "targets": [1,2,3,4,5]
      }
    }]
  });

CanisLupus

Too late to edit the previous comment... The above code is the actual code I use, not the simplified code in the initial question. In the seventh column of the table, I need to have the output from the PHP functions

getSupplierInfo(getSupplierByService($p1,$p2,$p3),$p4.";".$p5);

allan

Oh sorry - I see what you mean. I had thought you were computing a value on the client-side rather than on the server-side.

I don't think the demo SSP class will do what you are looking for at the moment. You'd probably need to modify it a little to check if there is a db parameter for the column passed in. If not then don't add it to the list to get from the database.

Allan

CanisLupus

What a pity. I spent all day trying to make it work

Since I need SSP-for only one, and very specific use, I'll see if I can "hack it" for the time being. I do not need it to work with anything else but this very specific scenario, so it won't matter. It won't affect anything else.

Thanks for your time and excellent support.

CanisLupus

Allan - I modified the ssp.class.php a bit and got it to work. This is a "quick hack" and I am fully aware of the fact that it is lacking safety checks and pretty error messages and all such things.

Also, I am guessing that if done properly, one should extend the SSP-class instead of placing extra code directly in it, or something like that.

Nevertheless, I thought you might find it interesting to see what I did, and perhaps smarter programmers than me could fix it so that it works for everyone, in all scenarios.

Finally, the two functions konka and shippingMethod are just test functions - not the real functions I need to use for my real case.

Preparing the $columns

$columns = array(
    array( 'db' => 'printdate', 'dt' => 0 ),
    array( 'db' => 'kollino'  , 'dt' => 1 ),
    array( 'db' => 'partner', 'dt' => 2, 'compute' => 'shippingMethod'),
    array( 'db' => 'servicetype', 'dt' => 2, 'compute' => 'shippingMethod'),
    array( 'db' => 'city', 'dt' => 3),
    array( 'db' => 'name', 'dt' => 4),
    array( 'db' => 'zipcode', 'dt' => 5, 'compute' => 'konka'),
    array( 'db' => 'country', 'dt' => 5, 'compute' => 'konka')
);

Changes to ssp.class.php

static function data_output ( $columns, $data )
    {
        $out = array();

        for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
        $row = array();
            $params = array();
            for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
        $column = $columns[$j];

                // Is there a formatter?
        if ( isset( $column['formatter'] ) ) {
                    $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
                }

        // Do we need to compute?
                elseif (isset( $column['compute'] ) ) {
                    $params[ $column['dt'] ]['compute'] = $column['compute'];
                    $params[ $column['dt'] ]['params'][] = $data[$i][ $columns[$j]['db'] ];
                }

                else {
                    $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                }

            }

            if ( !empty( $params ) ) {

                foreach ( $params as $dt => $parameters) {
                    $parameterStr = "";
                    foreach ( $parameters['params'] as $p) {
                        $parameterStr .= "$p,";
                    }
                    $parameterStr = rtrim($parameterStr,",");
                    $row[ $dt ] = self::$parameters['compute']($parameterStr);
                }
            }
            ksort($row);
            $out[] = $row;
        }

        return $out;
    }

    static function shippingMethod($parameterString) {
        $output = "";
        $parameters = explode(",",$parameterString);
        foreach($parameters as $p)
            $output .= "(".$p .")";
        return $output;
    }

    static function konka($parameterString) {
        $output = "";
        $parameters = explode(",",$parameterString);
        foreach($parameters as $p)
            $output .= "***".$p ."***";
        return $output;
    }

allan

Great to hear you've got it working - thanks for sharing your code with us!

Allan

Raywachaga

What also worked for me is creating a view in the database that you then use as the table to be queried. This was you don't have to change the structure of the server side and client side code much.