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

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

webpointzwebpointz Posts: 126Questions: 30Answers: 4
edited April 2015 in DataTables 1.9

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>   

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

    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

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    Answer ✓

    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

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

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

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    I try both with the same result

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

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

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

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

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

    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

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4
    edited April 2015

    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.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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>   
    
  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Just found it. As soon as I removed

    "type": 'POST',
    

    Everything works!?

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

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

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    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!

This discussion has been closed.