Is it possible to use an array of values for db parameter of $columns array in SSP???

mantonio

Please forgive me if this is something that is super simple, but I cannot find a solution for the life of me. I am trying to create a datatable using server side processing which is not the hard part at all. The table I need to create has around 90 columns in the database. I will need to reduce the amount of columns by combining several database columns into one DataTable column depending on the data. For example, let's say we have columnA, columnB, and columnC in the database table all containing information that can be displayed in columnABC of the DataTable. I have tried concatenating database columns, but get a generic SQL error: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use'

$columns = array(array('db' => "CONCAT(`State`, `State_Province_Region`)", 'dt' => 0)) 
**_Source: https://stackoverflow.com/questions/33860060/datatables-format-column-from-multiple-sourcesserver-side-processing_**

OR....

function getStates() 
{
    $states = array("State", "State_Province_Region");

    foreach($states as $state) {

        return $state;

    }
}
$columns = array(array(getStates(), 'dt' => 0)) 

The second alternative actually displays valid data, but since the return is inside the foreach loop I only get the first index of the array. If the return is outside of the loop then I get SQL error: Unknown column 'Array' in 'field list'.
I've tried several other options, but can't even remember which ones at this point since I've been at this for the past 2 days with no success. Seems hard to believe that nobody else has run into this issue and if they have then maybe the supposed solutions I found were for older versions? By the way this is for DataTables 1.10.15. If anyone has any suggestions or solutions PLEASE let me know!

tangerine

What are you using for your server-side script?

You need to define a field alias when using a CONCAT .

 "CONCAT(`State`, `State_Province_Region`) as my_field" 

mantonio

That was actually something I also tried but still get a SQL error as seen in the attached screenshot. The server-side script I'm using is the ssp.class.php unmodified. The processing script just defines the database table being used, primary key, columns, and SQL server connection details which are then JSON encoded like so

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

The DataTable is initialized like so

var myTable = $( "#table" ).DataTable( {
    "dom": "B<'clear'>lfrtip",
    "select": true
    "ordering": true,
    "searching": true,
    "stateSave": true,
    "paging": true
    "pagingType": "full_numbers",
    "buttons": [
        'csv'
     ],
     "lengthMenu": [ [ -1, 100, 50, 25, 10 ], [ "ALL", 100, 50, 25, 10 ] ],
     "deferRender": true,
     "processing": true,
     "serverSide": true,
     "ajax": {
         "url": "/path-to-processingScript/processing.php",
         "type": "POST"
      }
} );

allan

The server-side script I'm using is the ssp.class.php unmodified.

I don't think the demo script allows for SQL functions. It would most likely need to be modified to support them (I could be wrong, but I didn't intend for them to work initially when writing the demo script).

Allan

mantonio

That probably explains why it doesn't work! Concatenating isn't really what I'm looking for anyway, but was trying to understand how to basically display data from two different columns into one if either of those fields in the row are empty. This data is coming from a form (Gravity Forms Wordpress) so if the State field was not filled in then State_Province_Region would be and that would be the result to display and vice versa. Something simple like

$state = 'State';
$stateProvinceRegion = 'State_Province_Region';

if ($state == '') {
    echo $stateProvinceRegion;
}else {
    echo $state;
}

I already have an application using DataTables and this same data, but it's not server side processing and the table loads very slow. The data is retrieved within a couple seconds and displays in the table before the table itself is fully formatted so I wanted to attempt server side processing and see if that helps at all.

Within the application is another DataTable using server-side processing, but there was no need to combine any of the data into one column. Before I set it up to use server-side processing this DataTable was also very slow to load. I'm guessing it's the amount of columns slowing these two tables down, but not positive. If you have any other suggestions or my explanation isn't clear enough please let me know!

tangerine

how to basically display data from two different columns into one if either of those fields in the row are empty.

columns.render will do that.
https://datatables.net/reference/option/columns.render
but you'll still have to load all the relevant columns.

My preferred solution would be an upgraded server-side script. I have used this one regularly:
https://emranulhadi.wordpress.com/2014/06/05/join-and-extra-condition-support-at-datatables-library-ssp-class/