How to pass value for where-clause filtering in server-side processing?

ckryanco

Hello. We are trying to use PHP and DataTables' server-side processing to display only a single ballot's entries from a MySQL table that contains many votes from many ballots -- and then order them with the most recent votes at the top.

We have successfully extracted all data from the table and get it to show within DataTables' resulting web page. But we're stumped on how to pass the value for the where clause and sort order data to the ssp.class.php file referenced in the DataTables Examples for server-side processing.

Here is the javascipt in the main HTML page:

$(document).ready(function() {
    
    $('#entries').DataTable( {
        dom: "frtip",
        paging:   true,
        responsive: true,
        processing: true,
        serverSide: true,
        ajax: {
            "url": "./controller_entries_data_ssp_dfoaijerioqjdoifaodfijq.php",
            "data": {
                "Ballot_ID": <?php echo($Ballot_ID); ?>
            }
        },  
        search: {
            return: true,
        },        
        pagingType: "numbers",     
        select: true    

    } );

} );

Is this the correct way to pass the Ballot ID to the server-side processing script?

We are using the server-side processing script in the DataTables Examples section for server-side processing, only modifying the columns section as follows:

$columns = array(
    array( 'db' => 'Entry_ID',  'dt' => 0 ),         
    array( 'db' => 'Ballot_ID',  'dt' => 1, 'search' => 1419 ),    
    array(
        'db'        => 'Ballot_DateTimeCast',
        'dt'        => 2,
        'formatter' => function( $d, $row ) {
            return date( 'M j, Y - h:i:s a', strtotime($d));
        }
    ),    
    array( 'db' => 'Voter_Name',  'dt' => 3 ),
    array( 'db' => 'Voter_EMail',   'dt' => 4 ),
    array( 'db' => 'Ballot_Disqualified',     'dt' => 5 ),
    array( 'db' => 'Verification_Status',     'dt' => 6 ),        
    array( 'db' => 'Comments',     'dt' => 7 ),
    array( 'db' => 'Entry_Category_01',     'dt' => 8 ),
    array( 'db' => 'Entry_Category_02',     'dt' => 9 ), 
    array( 'db' => 'Entry_Category_03',     'dt' => 10 ), 
    array( 'db' => 'Entry_Category_04',     'dt' => 11 ),
    array( 'db' => 'Entry_Category_05',     'dt' => 12 ),

Our experiments with adding a search value to the Ballot_ID column are not working (see the third line of the code above). 1419 is the Ballot_ID value we're testing (we're not sure how to access the value passed in the additional ajax data in the javascript). What is the proper way of doing this?

The code in ssp.class.php file references building both a WHERE and an ORDER BY phrase in the server-side processing, so I assume this is possible. Any guidance would be appreciated.

Thank you!

);

ckryanco

To follow up on this question, it is referring to the first example code posted on the Datatables.net site for server-side processing:

https://datatables.net/examples/server_side/simple.html

The ssp.class.php file is what Allen posted in the comment on that example.

Thanks again.

allan

Is this the correct way to pass the Ballot ID to the server-side processing script?

Looks fine to me. It isn't secure, so if you are worried about people accessing data for other Ballot_IDs, then you wouldn't use that method (it would need to be fully server-side), but that's fine for a basic use case.

If you want to apply a WHERE condition, then use the SSP::complex method - see here.

Allan

ckryanco

Thank you, Allan. We successfully used the SSP::complex method you suggested, and the DataTables portion of this project is working perfectly. (Very slick!)

However, we don't see how to apply Editor in this case. We have a separate test page that uses Editor (client-side) that works beautifully. But the data changes are being ignored when they are submitted in the server-side processing project. No errors are apparent.

The successful client-side server Editor uses this code to apply changes to the test database:

<?php

/*
 * Controller For test_table table
 */

// DataTables PHP library
include( "../../AE_Custom/DataTables/Editor-PHP-1.9.4/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use 
    DataTables\Database, 
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST

Editor::inst( $db, 'test_table', 'Person_Id' )
    ->fields(
        Field::inst( 'Name_Last' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A last name is required' )  
            ) ),
        Field::inst( 'Name_First' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A first name is required' ) 
            ) ),
        Field::inst( 'Hire_Date' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
        Field::inst( 'Favorite_Color' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A favorite color is required' ) 
            ) ),
        Field::inst( 'Phone_Number' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A phone number is required' )   
            ) ),
        Field::inst( 'Salary' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( 'Comments' )
    )
    ->process( $_POST )
    ->json();

This is completely different from the server-side processing script of :

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simple to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// GET DATABASE DATA
include '/var/www/database_data.php';
 
// DB table to use
$table = 'adeverywhere_products_rc_ballots_entries';
 
// Table's primary key
$primaryKey = 'Entry_ID';

// WHERE Phrase
$whereResult = 'Ballot_ID = ' . intval($_POST['Ballot_ID']) . ' AND Site_ID = ' . intval($_POST['Site_ID']);

// Set Passed Variable Values
$User_UTC_Offset = intval($_POST['UTC_Offset']) * 60;
$Ballot_RequireEMail_Confirmation = intval($_POST['Ballot_RequireEMail_Confirmation']);
 
// 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' => 'Entry_ID',  'dt' => 'DT_RowId' ),   
    array( 'db' => 'Entry_ID',  'dt' => 'Entry_ID' ),         

    array(
        'db'        => 'Ballot_DateTimeCast',
        'dt'        => 'Ballot_DateTimeCast',
        'formatter' => function( $d, $row ) {
            global $User_UTC_Offset;
            $d_AsTime = strtotime($d);
            $d_AsTime_Localized = $d_AsTime - $User_UTC_Offset;
            return date( 'M j, Y - h:i:s A', $d_AsTime_Localized); 
        }
    ),    

    array( 
        'db' => 'Ballot_Disqualified',
        'dt' => 'Ballot_Disqualified',
        'formatter' => function( $d, $row ) {
            if ($d == 1) {
                $d_formatted = 'Disqualified';
            } else {
                $d_formatted = 'Valid';
            }        
            return $d_formatted; 
        }),
    
    array( 
        'db' => 'Voter_Name',
        'dt' => 'Voter_Name',
        'formatter' => function( $d, $row ) {
            $d_cleaned = filter_var($d, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
            $d_cleaned = str_replace("\r\n", " ", $d_cleaned);
            $d_cleaned = str_replace("\r", " ", $d_cleaned);
            $d_cleaned = str_replace("\n", " ", $d_cleaned);            
            return $d_cleaned; 
        } ),      

    array( 
        'db' => 'Voter_EMail',
        'dt' => 'Voter_EMail',
        'formatter' => function( $d, $row ) {
            $d_cleaned = filter_var($d, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
            $d_cleaned = str_replace("\r\n", " ", $d_cleaned);
            $d_cleaned = str_replace("\r", " ", $d_cleaned);
            $d_cleaned = str_replace("\n", " ", $d_cleaned);            
            return $d_cleaned; 
        } ),        

    array( 
        'db' => 'Verification_Status',
        'dt' => 'Verification_Status',
        'formatter' => function( $d, $row ) {
            global $Ballot_RequireEMail_Confirmation;
            
            if ($Ballot_RequireEMail_Confirmation == 1) {
                
                if ($d == 1) {
                    $d_formatted = 'Verified';
                } else {
                    $d_formatted = 'Not Verified';
                }                  
                
            } else {
                $d_formatted = 'N/A';
            }
      
            return $d_formatted; 
        }),      
    array( 'db' => 'Ballot_StaffEntered',     'dt' => 'Ballot_StaffEntered',
        'formatter' => function( $d, $row ) {
            if ($d == 1) {
                $d_formatted = 'Staff Entered';
            } else {
                $d_formatted = 'Online Entry';
            }        
            return $d_formatted; 
        }),      
    
    array( 
        'db' => 'Entry_Category_01',
        'dt' => 'Entry_Category_01',
        'formatter' => function( $d, $row ) {
            $d_cleaned = filter_var($d, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
            $d_cleaned = str_replace("\r\n", " ", $d_cleaned);
            $d_cleaned = str_replace("\r", " ", $d_cleaned);
            $d_cleaned = str_replace("\n", " ", $d_cleaned);            
            return $d_cleaned; 
        } ),      
    
/* REDUNDANT CODE DELETED HERE TO SHORTEN THE POST */
    
    array( 
        'db' => 'Entry_Category_50',
        'dt' => 'Entry_Category_50',
        'formatter' => function( $d, $row ) {
            $d_cleaned = filter_var($d, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
            $d_cleaned = str_replace("\r\n", " ", $d_cleaned);
            $d_cleaned = str_replace("\r", " ", $d_cleaned);
            $d_cleaned = str_replace("\n", " ", $d_cleaned);            
            return $d_cleaned; 
        } ),  
    
    array( 
        'db' => 'Comments',
        'dt' => 'Comments',
        'formatter' => function( $d, $row ) {
            $d_cleaned = filter_var($d, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
            $d_cleaned = str_replace("\r\n", " ", $d_cleaned);
            $d_cleaned = str_replace("\r", " ", $d_cleaned);
            $d_cleaned = str_replace("\n", " ", $d_cleaned);            
            return $d_cleaned; 
        } ),             
  
    array( 
        'db' => 'Ballot_IPAddress',     
        'dt' => 'Ballot_IPAddress',
        'formatter' => function( $d, $row ) {
            $d_array = explode(",", $d);
            return $d_array[0]; 
        } ),  

); 
 

// SQL server connection information
$sql_details = array(
    'user' => $username,
    'pass' => $password,
    'db'   => $dbname,
    'host' => $servername
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 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( '../../AE_Custom/DataTables/ssp.class.php' );
 
echo json_encode(
    SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns, $whereResult )
);

The Editor server-side processing example at https://editor.datatables.net/examples/simple/server-side-processing.html shows code similar to the first code above. How would we incorporate that functionality into the server-side code that uses your ssp.class.php library?

Thank you again!

allan

If you are using Editor throw away the SSP class - you don't need it. The Editor libraries have server-side processing support built in.

Allan

ckryanco

That did the trick. Thank you!