jquery Datatable loading multiple rows

I am using jquery datatables to load data into my table: https://datatables.net/examples/data_sources/server_side

I have set a class which returns the formatted json string:

$columns = array(
  array( 'db' => 'first_name', 'dt' => 0 ),
  array( 'db' => 'last_name', 'dt' => 1 ),
  array( 'db' => 'position', 'dt' => 2 ),
  array( 'db' => 'office', 'dt' => 3 ),
  array(
    'db' => 'start_date',
    'dt' => 4,
    'formatter' => function( $d, $row ) {
      return date( 'jS M y', strtotime($d));
    }
  ),
  array(
    'db' => 'salary',
    'dt' => 5,
    'formatter' => function( $d, $row ) {
      return '$'.number_format($d);
    }
  )
);

How is it possible to get multiple db inside an array? For example, I would like to combine first_name and last_name inside a column.

Solution

You can do this, but I think its complicated from the server side, at least using the class provided by datatables. I offer you the option to do it from the client side as well that is much simpler, but you pick

Option 1, from the server side

If you are using the code reference used here you cannot do that. Taking a look at the code reveals that the list of $columns that you pass to the SSP::simple function are wrapped in back ticks `` so even if you attempt to use a concatenation function it will be taken as a column name, thus throwing an error.

As datatables themselves say in the code:

The static functions in this class are just helper functions (...) These functions obviously do not represent all that can be done with server-side processing, they are intentionally simple to show how it works.

So for more "customized" you can write your own server-side processor that allows you to generate a query with functions.

It would look something like this (based on the current ssp.class.php):

first you change the pluck function defined in line 558, so when you pass an array, it calls the concatenate function

    static function pluck ( $a, $prop )
    {
        $out = array();
    
        for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
            if ( empty($a[$i][$prop]) && $a[$i][$prop] !== 0 ) {
                continue;
            }
            // NEW CODE HERE, if an array is received, then concatenate it in the query
            if (is_array($a[$i][$prop])) {
                $out[$i] = "CONCAT(".implode(",",$a[$i][$prop]).") AS ".$prop;
            }else {
                //removing the $out array index confuses the filter method in doing proper binding,
                //adding it ensures that the array data are mapped correctly
                $out[$i] = $a[$i][$prop];
            }
        }
        return $out;
    }

then you change the concatenation logic in line 260, remove the backticks so the column list can accept functions (this reduces security and will throw errors for columns that contain spaces, if you are concerned by that you would have to make a bit more changes)

    $data = self::sql_exec( $db, $bindings,
        "SELECT ".implode(", ", self::pluck($columns, 'db'))."
         FROM `$table`
         $where
         $order
         $limit"
    );

and then finally you would use it like this

    $columns = array(
        array( 'db' => 'normal_column', 'dt' => 0 ),
        array( 'db' => array('first_name', 'second_name'), 'dt' => 0 )
    )

Option 2 from the client side

This is way more simple cause datatables offers a render function that you can call for your column data, and it has access to all the other columns, so you can easily concatenate them in the frontend. The only disadvantage that I see is that you are going to process it from the client, and if you want to offload the most possible from the client and do most processing on the database, you would have to go for option 1... but I dont thing a couple of concatenations are going to add a lot of overhead to your frontend anyway

as you can read here: https://datatables.net/examples/advanced_init/column_render.html you can declare in your configuration a row render function for that column you want. It would look something like this (changing vars to your needs of course)

    $(document).ready(function() {
        $('#example').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": "../server_side/scripts/server_processing.php",
            "columnDefs": [
                {
                    "render": function ( data, type, row ) {
                        return `${row["first_name"]} ${row["last_name"]}`;
                        // return `${row[1]} ${row[2]}`; // if your data is sequential
                    },
                    "targets": 0 // index of your target column
                }
            ]
        } );
    } );

If you want to have this, not in a cell but in a details row, there is also a good example in the datatables site https://datatables.net/examples/server_side/row_details.html

Good luck