Determine when order, search or length is active when posting server-side

AndreOosthuizen

Test case can be found here - https://bid4anything.com/ims_test_page.php. Please note this works fine as only length select statement was loaded, I had to comment the other 2 (as below) out for page to work. Please ignore css as this is only test page.

Firstly, thank you very much again for all the help thus far in getting to know datatables's operations, I am reading up as much as possible.

I am now stuck at getting ordering/filtering and searching to work. Using your example code in ssp.class. php, (after doing my own), I still got the same results.

I have 3 data select queries, each checks for the following -
1) length - which returns fine as per the case test above, pagination works and total records per page works fine as long as I use ONLY this select statement - $query .= "SELECT * FROM wp_ims_dncpartfeed WHERE dealer_id = '".$_SESSION['dealer_id']."' AND quantity > '0' "; this concatenate with the second part to read the start/length parameters to become - $query .= "SELECT * FROM wp_ims_dncpartfeed WHERE dealer_id = '".$_SESSION['dealer_id']."' AND quantity > '0' ORDER BY agedays DESC LIMIT '.$query_per_page_start.', '.$query_per_page_end.'';
2) order - When I check for the order validation, it's select statement gets added to the original one above, meaning that ORDER BY gets added twice i.e. ORDER BY agedays DESC LIMIT '.$query_per_page_start.', '.$query_per_page_end. ORDER BY ID which results in an error in mysql statement.
3) search - same happens here, it's select statement also gets added to the original 2 above.

I then went to create a unique variable for each select statement, this loaded the page fine but search and ordering did not refresh the page. Draw was visible and pages/posts was successful as below.

My question thus, how can I determine if the page just loaded, draw is on 1, user clicked on order/filter, draw is on 2, user enters a search criteria, draw is on 3, 4 or 5 etc.

I do see these pages in the browser network, no problem so draw works fine, search shows the criteria, order shows the column values, my problem is that it does not refresh/re-load my data WHEN I used the unique variables, it keeps on loading the length select statement. Remember the above 1, 2 and 3 does not work as the select statement is concatenated.

If I can determine how to manipulate draw variable in jquery, I can manipulate my php to load the correct select statement, no idea however where to start.

Thanks, much appreciated.

Code for test page above -

$(document).ready(function () {
            var ims_data_table = $('#all_parts').DataTable({
                "lengthMenu": [
                    [25, 25, 50, 75, -1],
                    [25, 25, 50, 75, "ALL"]
                ],
                "stateSave": false,
                "paginate": true,
                "processing": true,
                "serverSide": true,
                "order" : [],
                "ajax": {
                    "url": "../includes/ims_table_process.php",
                    "type": "POST"
                },
                "columnDefs": [
                    {
                    "target" : [4, 7],
                        "orderable": false
                    },
                ],
            });
        });

in ims_table_process, the following select statement code -

//Set basic select query...
$query .= "SELECT * FROM wp_ims_dncpartfeed WHERE dealer_id = '".$_SESSION['dealer_id']."' AND quantity > '0' ";

if (isset($_POST["search"]["value"])) {
    $query .= ' AND partnumber =  '.$_POST["search"]["value"].' ';
    $query .= ' OR partdescription LIKE "%'.$_POST["search"]["value"].'%" ';
}

if (isset($_POST["order"])) {
    $post_order = $_POST['order'][0]['column'];

    switch ($post_order) {
        case 0 :
            $order_name = "ID";

            break;
        case 1 :
            $order_name = "partnumber";

            break;
        case 2 :
            $order_name = "partdescription";

            break;
        case 3 :
            $order_name = "costprice";

            break;
        case 4 :
            $order_name = "costprice";

            break;
        case 5 :
            $order_name = "sellingprice";

            break;
        case 6 :
            $order_name = "agedays";

            break;
        case 7 :
            $order_name = "quantity";

            break;
    }

    $query .= ' ORDER BY '.$order_name.' DESC';
} else {
    $query .=' ORDER BY ID DESC ';
}

if ($_POST['length'] != -1) {
    $query_per_page_start = $_POST['start'];
    $query_per_page_end = $_POST['length'];
    $query .= 'ORDER BY agedays DESC LIMIT '.$query_per_page_start.', '.$query_per_page_end.'';
}

$statement = $db->get_results($query);

and lastly after a returned array, the return of data to test page code -

$output = array(
        "draw"                      => intval($_POST["draw"]),
        "recordsTotal"          => $filtered_rows,
        "recordsFiltered"   => $total_records,
        "data"                      => $data
    );

    echo json_encode($output);

With above code running, this is the returned error -

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OR partdescription LIKE "%%" ORDER BY ID DESC ORDER BY agedays DESC LIMIT 0, 25' at line 1 in

kthorngren

I'm don't use PHP but it seems like you need to change your PHP code a bit.

You have:

    $query .= ' ORDER BY '.$order_name.' DESC';
} else {
    $query .=' ORDER BY ID DESC ';
}

Which looks like it adds one of those two ORDER BY strings to the query.

Then you have:

if ($_POST['length'] != -1) {
    $query_per_page_start = $_POST['start'];
    $query_per_page_end = $_POST['length'];
    $query .= 'ORDER BY agedays DESC LIMIT '.$query_per_page_start.', '.$query_per_page_end.'';
}

The last line probably shouldn't have ORDER BY. I think ORDER BY is a comma separated list of columns. So you should have something like this:

``
`if ($_POST['length'] != -1) {
$query_per_page_start = $_POST['start'];
$query_per_page_end = $_POST['length'];
$query .= ', agedays DESC LIMIT '.$query_per_page_start.', '.$query_per_page_end.'';
}
```

Replace ORDER BY with comma.

Kevin

AndreOosthuizen

Hi Kevin, again I am leaning on your shoulders it seems, thank you!

Irrespective of removing the comma, the statements still gets concatenated, I need to separate them based on what the user did. If you look at the test case, it loads fine with this statement as the ONLY statement. If I start doing the other checks i.e. order or search, it wants to add all the statements together, thus I need to determine via java - is the data loaded on form loaded, did the user enter search criteria, did the user filter a column.

So, if document loads var where = 1, user did search, var where = 2 or user filtered by column(3), var where = 3.

Currently it seems, if I look at the browser network, that all conditions runs as true, that is way php is trying to run all 3 statements together as well.

Based on this I can then call my php statements. hope this makes sense?

I'm don't use PHP but it seems like you need to change your PHP code a bit

I am more on PHP, ajax is still beating me over the head...

kthorngren

Why don't you want to use the ssp.class.php as the base for customizing to what you need?

For each search or sort I perform the same rows are being returned. Maybe someone else can help with your PHP script.

I noticed the value you return for recordsTotal is incorrect. From the SSP docs:

Total records, before filtering (i.e. the total number of records in the database)

In your case the value is 25, the page length.

Kevin

AndreOosthuizen

I believe you are right Kevin, I already started with the ssp page, seems that will be the way forward, I will keep you posted on the outcome.

Total records, did not notice, just a small change in php page, thanks for showing it out to me.