jqGrid Pagination with Teradata

Amos_Mike
Amos_Mike used Ask the Experts™
on
I have successfully set up a jQGrid and now I'm trying to set up pagination successfully
The default is to use the LIMIT function in a MySQL query similar to this:

"SELECT a.id, a.invdate, b.name, a.amount,a.tax,a.total,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id ORDER BY $sidx $sord LIMIT $start , $limit";


so this will select records, order by the request parameters and then limit the output based on the two variables start and limit.

So, for example, on page 1 start = 0 and limit = 10, returning records 0-10
on page 2, start = 10 and limit = 10, returning records 10-20

Now, my problem is, Teradata SQL does not have a LIMIT function, only a SAMPLE and RANDOM function that returns a given number of rows, but at random, which obviously does not work for pagination.

Does anyone have any advice on a workaround or maybe some alternate logic that would allow me to use pagination as it is a requirement that I connect to a Teradata database.  

Attached is the javascript that populates the grid
<?php
// Include the information needed for the connection to
// MySQL data base server. 
include("dbconfig.php");
//since we want to use a JSON data we should include
//encoder and decoder for JSON notation
//If you use a php >= 5 this file is not needed
//include("JSON.php");
 
// create a JSON service
//$json = new Services_JSON();
 
// to the url parameter are added 4 parameter
// we shuld get these parameter to construct the needed query
// for the pager
 
// get the requested page
$page = $_REQUEST['page'];
// get how many rows we want to have into the grid
// rowNum parameter in the grid
$limit = $_REQUEST['rows'];
// get index row - i.e. user click to sort
// at first time sortname parameter - after that the index from colModel
$sidx = $_REQUEST['sidx'];
// sorting order - at first time sortorder
$sord = $_REQUEST['sord']; 
 
// if we not pass at first time index use the first column for the index
if(!$sidx) $sidx =1;
// connect to the MySQL database server
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());
 
// select the database
mysql_select_db($database) or die("Error conecting to db.");
 
// calculate the number of rows for the query. We need this to paging the result
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];
 
// calculation of total pages for the query
if( $count >0 ) {
	$total_pages = ceil($count/$limit);
} else {
	$total_pages = 0;
}
 
// if for some reasons the requested page is greater than the total
// set the requested page to total page
if ($page > $total_pages) $page=$total_pages;
 
// calculate the starting position of the rows
$start = $limit*$page - $limit; // do not put $limit*($page - 1)
// if for some reasons start position is negative set it to 0
// typical case is that the user type 0 for the requested page
if($start <0) $start = 0;
 
// the actual query for the grid data
$SQL = "SELECT a.id, a.invdate, b.name, a.amount,a.tax,a.total,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());
 
// constructing a JSON
$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $responce->rows[$i]['id']=$row[id];
    $responce->rows[$i]['cell']=array($row[id],$row[invdate],$row[name],$row[amount],$row[tax],$row[total],$row[note]);
    $i++;
}
// return the formated data
//echo $json->encode($responce);
echo json_encode($responce);
?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
What you can do is do 2 queries. One that fetches  the ids sorted and store them into an array, then traverse the array 20 at a time to get the results....something like select * from table where id>a[0] and id<a[19].................

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial