Datatables Filter/Search with MSSQL 2008

in-dex

I have managed to make the pagination work (thanks to zhorov in stack overflow) but somehow it's conflicting with the search/filter function.

Tried to put each query in each $_post to see if the select query will not conflict and when I put the first select query at the bottom it will override the query for pagination. When I put the pagination query on top it wont display anything.

Here is the complete code that I have

`<?php

include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["search"]["value"]))
{

$query .= "WHERE emp_id LIKE '%".$_POST["search"]["value"]."%'  ";
$query .= "OR id LIKE '%".$_POST["search"]["value"]."%'  ";

}
if(isset($_POST["order"]))
{
$query .= "ORDER BY ".$_POST['order']['0']['column']." ".$_POST['order']['0']['dir']." ";
}
else
{
$query .= "ORDER BY id DESC ";
}
if ($_POST["length"] != -1)
{

 $query = "SELECT *
    FROM (
        SELECT 
            *, 
            ROW_NUMBER() OVER (ORDER BY id DESC) AS Rn
        FROM users )";
$query .=         
    "sub WHERE  status = 'Active' AND Rn BETWEEN ".($_POST["start"])." AND ".($_POST["start"]  + $_POST["length"] - 1 );        

}

$menu="";
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{

$sub_array = array();
$sub_array[] = $row['id'];
$sub_array[] = $row['emp_id'];
$sub_array[] = $row['username'];
$sub_array[] = $row['password'];
$sub_array[] = $row['email'];
$sub_array[] = $row['firstname'];
$sub_array[] = $row['middlename'];
$sub_array[] = $row['lastname'];
$sub_array[] = $row['location'];
$sub_array[] = $row['contact_no'];
$sub_array[] = $row['gender'];
$sub_array[] = $row['access_type'];
$sub_array[] = $row['status'];
$sub_array[] = $row['date_reg'];    
    $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Deactivate</button>';
$data[] = $sub_array;

}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $filtered_rows,
"recordsFiltered" => get_total_all_records(),
"data" => $data
);
echo json_encode($output);

<?php >` ?>

AS of now only the Pagination works but the filter/search does not.

aharro12

How is your datatable being initialized? Did you define POST by default it uses GET

in-dex

Hi! its in POST.

      **var dataTable = $('#user_data').DataTable({
        "processing":true,
        "serverSide":true,
        "responsive":true,``
        "order":[],
        "ajax":{
          url:"fetch.php",
          type:"POST"
        },**