I have managed to make the pagination work (thanks to @zhorov) 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); ?>`
Both the Pagination and Filter/Serch should work.
Advertisement
Answer
It’s probably too late for this answer, but I think that next approach may help. You just need to put your WHERE
clause in the subquery.
<?php ... # WHERE clause $where = "WHERE (status = 'Active') "; if (isset($_POST["search"]["value"])) { $where .= " AND ((emp_id LIKE '%".$_POST["search"]["value"]."%') OR (id LIKE '%".$_POST["search"]["value"]."%')) "; } # Whole statement if ($_POST["length"] != -1) { $query = "SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS Rn FROM users ".$where." ) "; $query .= "sub WHERE Rn BETWEEN ".($_POST["start"])." AND ".($_POST["start"] + $_POST["length"] - 1 ); } if (isset($_POST["order"])) { $query .= " ORDER BY ".$_POST['order']['0']['column']." ".$_POST['order']['0']['dir']." "; } else { $query .= " ORDER BY id DESC "; } ... ?>
Notes:
This example is based on your code. Note, that although you use prepared statements ($connection->prepare($query)
), it’s necessery to use parameters to prevent SQL injection, when you have user input.