SSP::complex using mysql date function can't render the rows in pages correctly

EnzoCheng

Description of problem: SSP is working but when I use date() mysql function, the results returned are all shown on the first page

JavaScript:

let table =  $('#salesTable').DataTable({
        //responsive: true,
        // "sDom": 'Blftip',
        // buttons: [
        //     'print'
        // ],
        "deferRender": true,
        "aLengthMenu": [
          [5, 10, 15, -1],
          [5, 10, 15, "All"]
        ],
        "iDisplayLength": 10,
        "language": {
          search: "Search"
        },
        "processing": true,
        "serverSide": true,
        "ajax": {
          url:requestSalesTablePath,
          type:"POST",
          data:{
              startDate:"123",
              endDate:"123"
          },
          error:(xhr, error, code)=>{
            console.log(JSON.stringify(xhr));
            console.log(error);
          },
        },
  
     });

PHP:

<?php

use App\DataTable\SSP;

include realpath("../../vendor/autoload.php");

use const App\Mysql\DB_HOST;
use const App\Mysql\DB_NAME;
use const App\Mysql\DB_PASSWORD;
use const App\Mysql\DB_USER;

require("C:/xampp/.config.php");


$dbDetails = array (
    "host" => DB_HOST,
    "user" => DB_USER,
    "pass" => DB_PASSWORD,
    "db" => DB_NAME

);

if(isset($_POST["startDate"]) && isset($_POST["endDate"])){

       
        $table = "sales";

        $primaryKey = "SALES_TRANSNO";

        $columns = array(

            array("db"=>"SALES_TRANSNO",
                "dt"=>"DT_RowId",
                'formatter'=>function($d,$row){
                    return "row_".$d;   
                }),
            array("db"=>"SALES_TRANSNO","dt"=>0),
            array("db"=>"SALES_STORE","dt"=>1),
            array("db"=>"SALES_STATUS","dt"=>2),
            array("db"=>"SALES_DATETIME","dt"=>3,
            'formatter' => function($d,$row){
                return date('Y-m-d',strtotime($d));
            }),
            array("db"=>"SALES_NETSALES","dt"=>4),

        );


        $where = "DATE(SALES_DATETIME) > '2020-09-10';";
    
        echo json_encode(
            SSP::complex($_POST,$dbDetails,$table,$primaryKey,$columns,$where)
        );
}

53 rows returned are all shown on the first page:

The rows are the same on the fourth page:

colin

Are you saying that when you request the data for any page, it's only showing the data from the first page?

Can you link to your page so we can take a look?

Colin

EnzoCheng

Sorry the website is still under development, it is accessed locally.

I found out that it has nothing to do with mysql date() function. With WHERE clause, if results are more than iDisplayLength, all the results will be rendered on the first page of the table

This table says Showing 1 to 10 of 14 entries (filtered from 17 total entries)
but all 14 records are shown, not just 10.

Searching is functioning well.

colin

Can you post the JSON returned from the server when that happens, please.

Colin

EnzoCheng

JSON returned:

{"draw":1,"recordsTotal":17,"recordsFiltered":14,
"data":[
{"0":"3010281","1":"MHQ ","2":"2020-02-13 11:16:59","3":"2","4":"0","DT_RowId":"row_3010281"},
{"0":"3010283","1":"MHQ ","2":"2020-02-12 11:14:35","3":"0","4":"0","DT_RowId":"row_3010283"},
{"0":"3010290","1":"MHQ ","2":"2020-02-12 15:36:53","3":"30","4":"0","DT_RowId":"row_3010290"},
{"0":"3010292","1":"MHQ ","2":"2020-02-13 11:16:59","3":"15","4":"0","DT_RowId":"row_3010292"},
{"0":"3010302","1":"MHQ ","2":"2020-02-13 11:17:05","3":"-36","4":"0","DT_RowId":"row_3010302"},
{"0":"3012066","1":"MHQ ","2":"2020-02-12 11:14:30","3":"-50","4":"0","DT_RowId":"row_3012066"},
{"0":"3012077","1":"MHQ ","2":"2020-02-13 11:16:59","3":"6","4":"0","DT_RowId":"row_3012077"},
{"0":"3012081","1":"MHQ ","2":"2020-02-13 11:17:03","3":"3","4":"0","DT_RowId":"row_3012081"},
{"0":"3012089","1":"MHQ ","2":"2020-02-12 15:36:53","3":"15","4":"0","DT_RowId":"row_3012089"},
{"0":"3012370","1":"MHQ ","2":"2020-02-12 15:36:53","3":"18","4":"0","DT_RowId":"row_3012370"},
{"0":"3012399","1":"MHQ ","2":"2020-02-13 11:17:05","3":"-30","4":"0","DT_RowId":"row_3012399"},
{"0":"3012447","1":"MHQ ","2":"2020-02-13 11:17:05","3":"7","4":"0","DT_RowId":"row_3012447"},
{"0":"9980013","1":"MHQ ","2":"2020-02-12 11:14:30","3":"19","4":"0","DT_RowId":"row_9980013"},
{"0":"9980029","1":"MHQ ","2":"2020-02-12 11:14:24","3":"-100","4":"0","DT_RowId":"row_9980029"}]}

colin

Yep - there's 14 rows in there! The server should be returning the number of records requested, which in your case would be 10.

Colin

EnzoCheng

Why is this happening? I copied the SSP class from github. Any idea?

allan

Can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Or even better would be a link to a test case showing the issue.

Thanks,
Allan

EnzoCheng

Hi Allan, the code is ulifam, https://debug.datatables.net/ulifam

allan

Thanks!

In your PHP above you have:

if(isset($_POST["startDate"]) && isset($_POST["endDate"])){

but those two parameters are not being send to the server. So I'm guessing it is dropping into an else which is not shown in the code above? Can you show the full PHP file please?

Allan

EnzoCheng

I did send the parameters via the JavaScript I posted here

Now I put an esle condition in this PHP:

<?php

use App\DataTable\SSPjoin;
use App\DataTable\SSP;

include realpath("../../vendor/autoload.php");

use const App\Mysql\DB_HOST;
use const App\Mysql\DB_NAME;
use const App\Mysql\DB_PASSWORD;
use const App\Mysql\DB_USER;

require("C:/xampp/.config.php");


$dbDetails = array (
    "host" => DB_HOST,
    "user" => DB_USER,
    "pass" => DB_PASSWORD,
    "db" => DB_NAME

);

$table = "pos_sales";


$primaryKey = "SALES_TRANSNO";

$columns = array(

array("db"=>"SALES_TRANSNO",
    "dt"=>"DT_RowId",
    'formatter'=>function($d,$row){
        return "row_".$d;   
    }),
array("db"=>"SALES_TRANSNO","dt"=>0),
array("db"=>"SALES_STORE","dt"=>1),
array("db"=>"SALES_STATUS","dt"=>2),
array("db"=>"SALES_DATETIME","dt"=>3,
'formatter' => function($d,$row){
    return date('Y-m-d',strtotime($d));
}),
array("db"=>"SALES_NETSALES","dt"=>4),

);

if(isset($_POST["startDate"]) && isset($_POST["endDate"])){


        $where = "DATE(SALES_DATETIME) > '2020-09-10';";
    
        echo json_encode(
            SSP::complex($_POST,$dbDetails,$table,$primaryKey,$columns,$where)
        );


}

else{
    echo json_encode(
        SSP::simple($_POST,$dbDetails,$table,$primaryKey,$columns)
    );
}

The datatable is functioning well without WHERE clause.

Is it because of there are multiple primary keys in this database table?

allan

That doesn't match what your debug trace shows though:

    "ajax": {
        "url": "http://localhost//CRM-develop/src/Query/queryStockBasedOnRangeAmount.php",
        "type": "POST",
        "data": {
            "lessThan": "100"
        }
    },

There are no startDate and endDate parameters being sent in the request in the trace you sent me above.

Allan

EnzoCheng

Hi allan, the correct debug code for this table is ijihol, https://debug.datatables.net/ijihol

allan

Thank you. That looks like it should be working to me - I don't immediately see anything that would be causing the issue you are seeing.

Can you update to DataTables 1.10.22 (from 1.10.16) please? I don't think that will make a difference though. Could you also link to a page showing the issue so I can trace it through?

Thanks,
Allan

EnzoCheng

Hi Allan, this is the link testing.qubeorder.com.my/demo/demo.php

allan

Thank you. Can you also show me queryTesting.php? I see you are passing anything: '123' as the data. I don't see anything in the above PHP that is handling that.

Allan

EnzoCheng

Hi Allan, this is queryTesting.php:

<?php

require("SSP.php");

require(".config.php");

$dbDetails = array (
    "host" => DB_HOST,
    "user" => DB_USER,
    "pass" => DB_PASSWORD,
    "db" => DB_NAME

);


$table = "mas_membership_dummy";

$primaryKey = "M_CODE";

$columns = array(

    array("db"=>"M_CODE",
          "dt"=>"DT_RowId",
        'formatter'=>function($d,$row){
            return "row_".$d;   
        }),
    array("db"=>"M_CODE","dt"=>0),
    array("db"=>"M_DESC","dt"=>1),
    array("db"=>"M_DESC2","dt"=>2),

);

if(isset($_POST["anything"])){

    $where = "M_CODE < 100;";

    echo json_encode(
        SSP::complex($_POST,$dbDetails,$table,$primaryKey,$columns,$where)
    );
    
}

else{

    echo json_encode(
        SSP::simple($_GET,$dbDetails,$table,$primaryKey,$columns)
    );
    
}

the front-end part:

<body>  

                <h2>Without Where</h2>

                <table id="withoutWhere" class="table">

                        <thead>
                        <tr>
                            <th>Member No.</th>
                            <th>First Name</th>
                            <th>Last Name</th>
 
                        </tr>
                        </thead>

                </table>

                <br>
                <h2>With Where</h2>


                <table id="withWhere" class="table">

                        <thead>
                            <tr>
                                <th>Member No.</th>
                                <th>First Name</th>
                                <th>Last Name</th>

                            </tr>
                        </thead>

                </table>
                    
            </body>

            <script>

                $(function(){   


                                let tableWithoutWhere =  $('#withoutWhere').DataTable({
                                    destroy:true,
                                    
                                    "aLengthMenu": [
                                        [5, 10, 15, -1],
                                        [5, 10, 15, "All"]
                                    ],
                                    "pageLength": 10,
                                    "language": {
                                        search: "Search"
                                    },
                                    "processing": true,
                                    "serverSide": true,
                                    "ajax": {
                                        url:"queryTesting.php",
                                        type:"GET",
                                        error:(xhr, error, code)=>{
                                            console.log(JSON.stringify(xhr));
                                            console.log(error);
                                        },
                                    },

                            });


                            let tableWithWhere =  $('#withWhere').DataTable({
                                    destroy:true,
                                    
                                    "aLengthMenu": [
                                        [5, 10, 15, -1],
                                        [5, 10, 15, "All"]
                                    ],
                                    "pageLength": 10,
                                    "language": {
                                        search: "Search"
                                    },
                                    "processing": true,
                                    "serverSide": true,
                                    "ajax": {
                                        url:"queryTesting.php",
                                        type:"POST",
                                        data:{
                                            anything:"123",
                                        },
                                        error:(xhr, error, code)=>{
                                            console.log(JSON.stringify(xhr));
                                            console.log(error);
                                        },
                                    },

                            });

                  
                        
                       
            });
            </script>

allan

Are you using this version of the script?

I've just tried my basic example with:

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, 'id < 10' )
);

and it appears to work just fine.

I'd suggest that if it isn't an out of date script, then you need to echo out the SQL that is constructed in complex and see what is wrong with that.

Allan

EnzoCheng

Hi Allan,

the SQL I get looks like this:

SSP:

return array(
            // "draw"            => isset ( $request['draw'] ) ?
            //  intval( $request['draw'] ) :
            //  0,
            // "recordsTotal"    => intval( $recordsTotal ),
            // "recordsFiltered" => intval( $recordsFiltered ),
            "data"            => self::data_output( $columns, $data ),
            // "sql"              => $data
        );

The result looks like this:

{"data":[{"DT_RowId":"row_0","0":"0","1":"Name10","2":"Name20"},
{"DT_RowId":"row_1","0":"1","1":"Name11","2":"Name21"},
{"DT_RowId":"row_2","0":"2","1":"Name12","2":"Name22"},
{"DT_RowId":"row_3","0":"3","1":"Name13","2":"Name23"},
{"DT_RowId":"row_4","0":"4","1":"Name14","2":"Name24"},
{"DT_RowId":"row_5","0":"5","1":"Name15","2":"Name25"},
{"DT_RowId":"row_6","0":"6","1":"Name16","2":"Name26"},
{"DT_RowId":"row_7","0":"7","1":"Name17","2":"Name27"},
{"DT_RowId":"row_8","0":"8","1":"Name18","2":"Name28"},
{"DT_RowId":"row_9","0":"9","1":"Name19","2":"Name29"},
{"DT_RowId":"row_10","0":"10","1":"Name110","2":"Name210"},
{"DT_RowId":"row_11","0":"11","1":"Name111","2":"Name211"},
{"DT_RowId":"row_12","0":"12","1":"Name112","2":"Name212"},
{"DT_RowId":"row_13","0":"13","1":"Name113","2":"Name213"},
{"DT_RowId":"row_14","0":"14","1":"Name114","2":"Name214"},
{"DT_RowId":"row_15","0":"15","1":"Name115","2":"Name215"},
{"DT_RowId":"row_16","0":"16","1":"Name116","2":"Name216"},
{"DT_RowId":"row_17","0":"17","1":"Name117","2":"Name217"},
{"DT_RowId":"row_18","0":"18","1":"Name118","2":"Name218"},
{"DT_RowId":"row_19","0":"19","1":"Name119","2":"Name219"},
{"DT_RowId":"row_20","0":"20","1":"Name120","2":"Name220"},
{"DT_RowId":"row_21","0":"21","1":"Name121","2":"Name221"},
{"DT_RowId":"row_22","0":"22","1":"Name122","2":"Name222"},
{"DT_RowId":"row_23","0":"23","1":"Name123","2":"Name223"},
{"DT_RowId":"row_24","0":"24","1":"Name124","2":"Name224"},
{"DT_RowId":"row_25","0":"25","1":"Name125","2":"Name225"},
{"DT_RowId":"row_26","0":"26","1":"Name126","2":"Name226"},
{"DT_RowId":"row_27","0":"27","1":"Name127","2":"Name227"},
{"DT_RowId":"row_28","0":"28","1":"Name128","2":"Name228"},
{"DT_RowId":"row_29","0":"29","1":"Name129","2":"Name229"},
{"DT_RowId":"row_30","0":"30","1":"Name130","2":"Name230"},
{"DT_RowId":"row_31","0":"31","1":"Name131","2":"Name231"},
{"DT_RowId":"row_32","0":"32","1":"Name132","2":"Name232"},
{"DT_RowId":"row_33","0":"33","1":"Name133","2":"Name233"},
{"DT_RowId":"row_34","0":"34","1":"Name134","2":"Name234"},
{"DT_RowId":"row_35","0":"35","1":"Name135","2":"Name235"},
{"DT_RowId":"row_36","0":"36","1":"Name136","2":"Name236"},
{"DT_RowId":"row_37","0":"37","1":"Name137","2":"Name237"},
{"DT_RowId":"row_38","0":"38","1":"Name138","2":"Name238"},
{"DT_RowId":"row_39","0":"39","1":"Name139","2":"Name239"},
{"DT_RowId":"row_40","0":"40","1":"Name140","2":"Name240"},
{"DT_RowId":"row_41","0":"41","1":"Name141","2":"Name241"},
{"DT_RowId":"row_42","0":"42","1":"Name142","2":"Name242"},
{"DT_RowId":"row_43","0":"43","1":"Name143","2":"Name243"},
{"DT_RowId":"row_44","0":"44","1":"Name144","2":"Name244"},
{"DT_RowId":"row_45","0":"45","1":"Name145","2":"Name245"},
{"DT_RowId":"row_46","0":"46","1":"Name146","2":"Name246"},
{"DT_RowId":"row_47","0":"47","1":"Name147","2":"Name247"},
{"DT_RowId":"row_48","0":"48","1":"Name148","2":"Name248"},
{"DT_RowId":"row_49","0":"49","1":"Name149","2":"Name249"},
{"DT_RowId":"row_50","0":"50","1":"Name150","2":"Name250"},
{"DT_RowId":"row_51","0":"51","1":"Name151","2":"Name251"},
{"DT_RowId":"row_52","0":"52","1":"Name152","2":"Name252"},
{"DT_RowId":"row_53","0":"53","1":"Name153","2":"Name253"},
{"DT_RowId":"row_54","0":"54","1":"Name154","2":"Name254"},
{"DT_RowId":"row_55","0":"55","1":"Name155","2":"Name255"},
{"DT_RowId":"row_56","0":"56","1":"Name156","2":"Name256"},
{"DT_RowId":"row_57","0":"57","1":"Name157","2":"Name257"},
{"DT_RowId":"row_58","0":"58","1":"Name158","2":"Name258"},
{"DT_RowId":"row_59","0":"59","1":"Name159","2":"Name259"},
{"DT_RowId":"row_60","0":"60","1":"Name160","2":"Name260"},
{"DT_RowId":"row_61","0":"61","1":"Name161","2":"Name261"},
{"DT_RowId":"row_62","0":"62","1":"Name162","2":"Name262"},
{"DT_RowId":"row_63","0":"63","1":"Name163","2":"Name263"},
{"DT_RowId":"row_64","0":"64","1":"Name164","2":"Name264"},
{"DT_RowId":"row_65","0":"65","1":"Name165","2":"Name265"},
{"DT_RowId":"row_66","0":"66","1":"Name166","2":"Name266"},
{"DT_RowId":"row_67","0":"67","1":"Name167","2":"Name267"},
{"DT_RowId":"row_68","0":"68","1":"Name168","2":"Name268"},
{"DT_RowId":"row_69","0":"69","1":"Name169","2":"Name269"},
{"DT_RowId":"row_70","0":"70","1":"Name170","2":"Name270"},
{"DT_RowId":"row_71","0":"71","1":"Name171","2":"Name271"},
{"DT_RowId":"row_72","0":"72","1":"Name172","2":"Name272"},
{"DT_RowId":"row_73","0":"73","1":"Name173","2":"Name273"},
{"DT_RowId":"row_74","0":"74","1":"Name174","2":"Name274"},
{"DT_RowId":"row_75","0":"75","1":"Name175","2":"Name275"},
{"DT_RowId":"row_76","0":"76","1":"Name176","2":"Name276"},
{"DT_RowId":"row_77","0":"77","1":"Name177","2":"Name277"},
{"DT_RowId":"row_78","0":"78","1":"Name178","2":"Name278"},
{"DT_RowId":"row_79","0":"79","1":"Name179","2":"Name279"},
{"DT_RowId":"row_80","0":"80","1":"Name180","2":"Name280"},
{"DT_RowId":"row_81","0":"81","1":"Name181","2":"Name281"},
{"DT_RowId":"row_82","0":"82","1":"Name182","2":"Name282"},
{"DT_RowId":"row_83","0":"83","1":"Name183","2":"Name283"},
{"DT_RowId":"row_84","0":"84","1":"Name184","2":"Name284"},
{"DT_RowId":"row_85","0":"85","1":"Name185","2":"Name285"},
{"DT_RowId":"row_86","0":"86","1":"Name186","2":"Name286"},
{"DT_RowId":"row_87","0":"87","1":"Name187","2":"Name287"},
{"DT_RowId":"row_88","0":"88","1":"Name188","2":"Name288"},
{"DT_RowId":"row_89","0":"89","1":"Name189","2":"Name289"},
{"DT_RowId":"row_90","0":"90","1":"Name190","2":"Name290"},
{"DT_RowId":"row_91","0":"91","1":"Name191","2":"Name291"},
{"DT_RowId":"row_92","0":"92","1":"Name192","2":"Name292"},
{"DT_RowId":"row_93","0":"93","1":"Name193","2":"Name293"},
{"DT_RowId":"row_94","0":"94","1":"Name194","2":"Name294"},
{"DT_RowId":"row_95","0":"95","1":"Name195","2":"Name295"},
{"DT_RowId":"row_96","0":"96","1":"Name196","2":"Name296"},
{"DT_RowId":"row_97","0":"97","1":"Name197","2":"Name297"},
{"DT_RowId":"row_98","0":"98","1":"Name198","2":"Name298"},
{"DT_RowId":"row_99","0":"99","1":"Name199","2":"Name299"}]}

allan

Can you echo out the SQL that is generated here please?

Allan

EnzoCheng

Hi Allan,

query:

The query is logged to console and can be seen here testing.qubeorder.com.my/demo/demo.php

allan

You've got the DataTable commented out and a plain jQuery Ajax request being made to the server without any of the parameters that DataTables would be sending - such as the page length:

                            // let tableWithWhere =  $('#withWhere').DataTable({

                            //         destroy:true,

                                    

                            //         "aLengthMenu": [

                            //             [5, 10, 15, -1],

                            //             [5, 10, 15, "All"]

                            //         ],

                            //         "pageLength": 10,

                            //         "language": {

                            //             search: "Search"

                            //         },

                            //         "processing": true,

                            //         "serverSide": true,

                            //         "ajax": {

                            //             url:"queryTesting.php",

                            //             type:"POST",

                            //             data:{

                            //                 anything:"123",

                            //             },

                            //             error:(xhr, error, code)=>{

                            //                 console.log(JSON.stringify(xhr));

                            //                 console.log(error);

                            //             },

                            //         },



                            // });
                            
                            $.ajax({
                                

                                    url:"queryTesting.php",

                                    type:"POST",

                                    data:{

                                        anything:"123",

                                    },
                                    success:(data)=>{
                                        console.log(data);
                                    },

                                    error:(xhr, error, code)=>{

                                        console.log(JSON.stringify(xhr));

                                        console.log(error);

                                    },

                                    
                            })

Can you remove the $.ajax call and uncomment the DataTable initialisation please?

Allan

EnzoCheng

Hi Allan,

I have removed the ajax call and uncommented the DataTable initialization.