How to pass a PHP form variable to server processing for use in WHERE clause?

webpointz

I have a form in my application where you select a "customer" and a "startdate" and "enddate".

When the user clicks on "submit", I want to pass these 3 POST variables through to datatables to be used in a WHERE clause to filter the results.

I've tried a bunch of things with no success. Using Firefox I can see the variable "formCustomer" in the "params".

Here's my code but how do I read the "formCustomer" variable in the page "scripts/server_processing_stock.php"?

    <script type="text/javascript" language="javascript" class="init">

        $.extend( $.fn.dataTable.defaults, {
            responsive: true
        } );        
        
        $(document).ready(function() {
            $('#stock_report').dataTable( {
                "language": { "infoFiltered": ""},          
                "order": [[ 1, "desc" ]],
                "processing": true,
                "serverSide": true,
                "ajax": {
                        "type": 'POST',
                        "url": 'scripts/server_processing_stock.php',
                        "data": {
                            formCustomer: '<?php echo $_POST["sel_customer"] ?>',
                        }
                    }

            } );
        } );

    </script>   

allan

Use ajax.data as a function rather than a static object. That way you can read the values from the DOM and insert them into the request object on-the-fly.

Allan

webpointz

I tried that too. The variables seem to be passed, BUT, in the ssp.class.php file, how do I read them in and use them in a where clause like the following:

WHERE customerid= 12 AND (date(insertdate) >= '2015-03-30' AND date(insertdate) <= '2015-04-06')

I try $customerid = $_POST['formCustomer']; etc to no avail.

webpointz

So, in my server_processing.php I AM getting the variables as $_POST type variables, but the $where clause passed through the

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

isn't working.

allan

The SSP class is designed to be super simple. It will require modifications if you want to perform some more complex conditions beyond just the simple global and column options of DataTables.

Have a look at the complex static method however, it also will require modification to match your needs.

Allan

webpointz

Hi Allan, yes, I'm using the "complex" example above and it is now filtering.

One problem though is that when the results are returned to the table instead of the default "showing 10" records, it shows ALL 16 records with 2 pages in pagination and says "Showing 1 to 10 of 16 entries" when in fact it is showing ALL records.

allan

Are you using the whereResult or whereAll option of the complex method. Sounds like you want whereAll.

Allan

webpointz

I try both with the same result

webpointz

It's as though anything above 10 records breaks pagination.

allan

Can you link to the page and show me the code please?

webpointz

It's behind a login but I can send you credentials via email with steps to the page in question and send you the code.

webpointz

Seeing as this particular datatable is for a report, I turned off pagination, however, there seems to be a bug with the "complex" PHP file and pagination.

allan

I've just tried this with my basic server-side processing example:

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "first_name like '%a%'" )
);

And it seems to work as expected (including specifically the paging).

Allan

webpointz

Hi Allan

I'm using the following:

$dateToday = date("Y-m-d");

$customerid = $_POST['formCustomer'];

if ( isset($_POST['formStartDate']) && $_POST['formStartDate'] != '' ) {
    $startdate = $_POST['formStartDate'];
} else {
    $startdate = '2010-01-01';
}   

if ( isset($_POST['formEndDate']) && $_POST['formEndDate'] != '' ) {
    $enddate = $_POST['formEndDate'];
} else {
    $enddate = '.$dateToday.';
}   



/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply 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
 */

// DB table to use
$table = 'activityreport';

// Table's primary key
$primaryKey = 'id';

// 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' => 'productid', 'dt' => 0 ),
    array( 'db' => 'username',   'dt' => 1 ),
    array( 'db' => 'ordernumber',  'dt' => 2 ),
    array( 'db' => 'orderdate',             
        'dt' => 3,
        'formatter' => function( $d, $row ) {
            return date( 'Y-m-d', strtotime($d));
        }
    ),
    array( 'db' => 'qty_sum',  'dt' => 4 )  
);


require( '../edi-includes/sql-connect.php' );


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 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' );

$whereAll = " customerid =".$customerid." AND date( orderdate ) >= '".$startdate."' AND date( orderdate ) <= '".$enddate."'";

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $whereAll)
);

Running against "ssp.class.php" in your static link to GitHub.

webpointz

Allan: I found the issue but not sure how to fix it. The issue is with the <script> code on the calling page with AJAX. The issue is with AJAX and DATA and how it's constructed.
It brings back data and displays it BUT all records show but pagination shows pages etc and also search and sort at headers doesn't work either.

What am I doing wrong here?

Here is my code.

    <script type="text/javascript" language="javascript" class="init">

        $.extend( $.fn.dataTable.defaults, {
            responsive: true
        } );        
        
        $(document).ready(function() {
            $('#example').dataTable( {
                
                "language": { "infoFiltered": ""},          
                "order": [[ 1, "desc" ]],
                "processing": true,
                "serverSide": true,
        
                "ajax": {
                        "type": 'POST',
                        "url": 'scripts/server_processing_activity.php',
                "data": {                        
                    formCustomer: '<?php echo $_POST["sel_customer"] ?>',  
                    formStartDate: '<?php echo $_POST["dp3"] ?>',
                    formEndDate: '<?php echo $_POST["dp4"] ?>',
                    }               
                }               
            } );
        } );

    </script>   

webpointz

Just found it. As soon as I removed

"type": 'POST',

Everything works!?

allan

Your server_processing_activity.php script is expecting GET parameters then (line 73 in the PHP script above uses $_GET confirming this).

Allan

webpointz

Thanks Allan, I removed the "type": 'POST' and changed the parameters in the server_processing_activity.php to be $_GET and everything is running fine now.

Many thanks...what a great product!