Sorry, I am not a great programmer, have learned from Google and trial and error :)
Where is the "magic" happening in the working examples? I don't see where it is telling it the number of records, pages, etc. Is it: SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
I am needing to build the Where statement using a form that is above the grid so I chose to use sessions. To get something to display I just put in a phony number of records at the end. Do I need to use the SSP command as in the example you provided?
<?php
require_once('config.php');
//$staffID = access_control();
$where = '';
$dockDate = $_SESSION["dockDate"];
$raNumber = $_SESSION["raNumber"];
$ocNumber = $_SESSION["ocNumber"];
$trackNumber = $_SESSION["trackNumber"];
$cmNumber = $_SESSION["cmNumber"];
$delim = '';
if ($dockDate !='') {
$where = "LoadingDockDate='" . $dockDate . "'";
$delim = " and ";
}
if ($raNumber !='') {
$where .= $delim . "RANumber like '%". $raNumber . "%'";
$delim = " and ";
}
if ($ocNumber !='') {
$where .= $delim . "OCNumber like '%" . $ocNumber . "%'";
$delim = " and ";
}
if ($trackNumber !='') {
$where .= $delim . "TrackingNumber like '%" . $trackNumber . "%'";
$delim = " and ";
}
if ($cmNumber !='') {
$where .= $delim . "CreditMemoNumber='$cmNumber'";
$delim = " and ";
}
if ($where =='') {
$where = "LoadingDockDate='1700-01-01'";
}
$_SESSION['where'] = $where;
$sql = "Select
RAHeader.RA_ID,
RAHeader.RANumber,
RAHeader.OCNumber,
RAHeader.OldRANumber,
RAHeader.LoadingDockDate,
CartonHeader.TrackingNumber,
concat(CartonHeader.Qty , (case when IsPallet=1 then ' pallet' else '' end)) as QtyOut,
CreditMemo.CreditMemoNumber,
CreditMemo.CreditMemoDate
From
RAHeader
left Join CartonHeader on CartonHeader.RA_ID=RAHeader.RA_ID
left Join CreditMemo on CreditMemo.RA_ID=RAHeader.RA_ID
Where " . $where;
$result = $mysqli->query($sql);
echo '{"data":[';
$delim='';
while ($row = $result->fetch_assoc())
{
echo $delim.json_encode($row);
$delim=',';
}
echo '],"options":[],"draw":1,"recordsTotal":"3","recordsFiltered":"3"}';
?>
Ok, it is showing the page navigation buttons, but way too many nav buttons needed for the small returned recordset, and instead of showing only 10 it is showing the entire recordset (75 records).
if I am sending length: 10, why do i get the entire recordset showing on the first page? and when I click any of the nav buttons I get the same recordset showing.
Ok, here is an example of the server side code using the parameters sent (search text box, column order)
//I have an initial search form the user submits before data is loaded
// The search form $_POST gets put into $where
// I never return the entire recordset
$sql .= " Where " . $where;
$result = $mysqli->query($sql);
$totalRecords = $result->num_rows;
//has the user entered anything into the DataTable search textbox?
//searchField is a concat of all the fields visible in the DataTable
$search = $_POST['search'];
if (!empty($search['value'])) {
$sql .= " Having searchField like '%".$search['value']."%'";
}
//has the user clicked on column header to change sort?
$orderBy = $_POST['order'];
$orderCol = intval($orderBy[0]['column']);
$sql .= " order by " . $orderCol . " " . $orderBy[0]['dir'];
$result = $mysqli->query($sql);
$filteredRecords = $result->num_rows;
//paging of data
$sql .= " Limit " . $_POST['start'] . ','. $_POST['length'];
$result = $mysqli->query($sql);
//return json to dataTables
echo '{"data":[';
$delim='';
while ($row = $result->fetch_assoc())
{
echo $delim.json_encode($row);
$delim=',';
}
echo '],"options":[],"draw":'.$draw.',"recordsTotal":'.$totalRecords.',"recordsFiltered":'.$filteredRecords.'}';
Answers
Working example.
Reference documentation. The key is to return the required parameters such as
recordsTotalas detailed in the documentation.Allan
Sorry, I am not a great programmer, have learned from Google and trial and error :)
Where is the "magic" happening in the working examples? I don't see where it is telling it the number of records, pages, etc. Is it: SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
I am needing to build the Where statement using a form that is above the grid so I chose to use sessions. To get something to display I just put in a phony number of records at the end. Do I need to use the SSP command as in the example you provided?
Correct. If you have a look at the
SSPclass it builds three different SQL statements and executes them to get the required information.Allan
No - you can write your own code, but you need to implement the protocol as described in the documentation.
Allan
Ok, it is showing the page navigation buttons, but way too many nav buttons needed for the small returned recordset, and instead of showing only 10 it is showing the entire recordset (75 records).
Here is what is being sent:
and here is the response:
if I am sending length: 10, why do i get the entire recordset showing on the first page? and when I click any of the nav buttons I get the same recordset showing.
recordsFilteredandrecordsTotalshould be integers - per the documentation.Because whatever is doing the processing on the server-side is returning all 75 rather than just the 10 it should be.
Allan
I found the SSP js file but I am not sure how to use the various functions in it with my code. How do I tell the server to get records 11-20?
ok. I am almost there. I figured out to use 'Limit' in the sql command.
So there are 75 records total that need to be returned, 10 per page.
but when I do this:
I get this:
Showing 1 to 10 of 10 entries (filtered from 75 total entries)
Ahh, I think I get it. The filtered is if I were to pass a search, not the number being returned for the page.
Ok, here is an example of the server side code using the parameters sent (search text box, column order)