pagination attempt works in mysql but not in odbc

[fife]

Hi guys. 

 

Ok im trying to achieve pagination through PHP on a ODBC query.  I have used the pagination before on mysql database so I know it works.  I have copied the code and changed it up so it match's the correct criteria for ODBC or mssql

 

however its not working.  Or should I say it is partly.

 

Basically the buttons appear to move through the records and the total records returned counter seems to be working but the loop brings back no results.  If i remove the pagination parts from the query and load the page the records are returned. This show me that the query is at least right but something in my pagination is killing it when I put it in.

 

 $currentPage = $_SERVER["PHP_SELF"]; $maxRows_rs_installs = 5;$pageNum_rs_installs = 0;if (isset($_GET['pageNum_rs_installs'])) {  $pageNum_rs_installs = $_GET['pageNum_rs_installs'];}$startRow_rs_installs = $pageNum_rs_installs * $maxRows_rs_installs; $query_rs_installs = "SELECT    allcalldataall.Call_Ref, allcalldataall.Call_Status_Description, allcalldataall.Date_Received, allcalldataall.Order_No, allcalldataall.Scheduled_Date_Time, allcalldataall.Link_to_Contract_Header, allcalldataall.Severity_Description, allcalldataall.repdonedate,    Clients.Co_Name, Clients.Post_Code,    LU_Call_Types.Type_Band,    LU_Company_Types.Company_Type_DescriptionFROM    { oj ((Siclops_Dispense.dbo.allcalldataall allcalldataall LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON        allcalldataall.Call_Type_Description = LU_Call_Types.Call_Type_Description)     LEFT OUTER JOIN Siclops_Dispense.dbo.Clients Clients ON        allcalldataall.Client_Ref = Clients.Client_Ref)     LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON        Clients.Company_Type = LU_Company_Types.Company_Type_Code}WHERE    allcalldataall.Call_Ref >= 710000 AND    (allcalldataall.Call_Status_Description <> 'Reported Done' AND    allcalldataall.Call_Status_Description <> 'PTF Rep Done' AND    allcalldataall.Call_Status_Description <> 'Proforma Sent' AND    allcalldataall.Call_Status_Description <> 'Paperwork Recvd' AND    allcalldataall.Call_Status_Description <> 'Left On Site' AND    allcalldataall.Call_Status_Description <> 'In Query' AND    allcalldataall.Call_Status_Description <> 'Cryo PW Sent' AND    allcalldataall.Call_Status_Description <> 'Complete' AND    allcalldataall.Call_Status_Description <> 'Cancelled Admin' AND    allcalldataall.Call_Status_Description <> 'Cancelled' AND    allcalldataall.Call_Status_Description <> 'Awaiting Invoic' AND    allcalldataall.Call_Status_Description <> 'Await TB Return' AND    allcalldataall.Call_Status_Description <> 'ApplicationSent') AND    (LU_Call_Types.Type_Band = 'Project' OR    LU_Call_Types.Type_Band = 'Install') AND    allcalldataall.Link_to_Contract_Header = '".$row_rs_member['companyident']."'ORDER BY    allcalldataall.Call_Ref DESC"; //im thinking the problem might be occuring on the line below but im not sure$query_limit_rs_installs = sprintf("%s LIMIT %d, %d", $query_rs_installs, $startRow_rs_installs, $maxRows_rs_installs); $rs_installs = odbc_exec($conn, $query_limit_rs_installs);$row_rs_installs = odbc_fetch_array($rs_installs); if (isset($_GET['totalRows_rs_installs'])) {  $totalRows_rs_installs = $_GET['totalRows_rs_installs'];} else {  $all_rs_installs = odbc_exec($conn, $query_rs_installs);  $totalRows_rs_installs = odbc_num_rows($all_rs_installs);}$totalPages_rs_installs = ceil($totalRows_rs_installs/$maxRows_rs_installs)-1;  $queryString_rs_installs = "";if (!empty($_SERVER['QUERY_STRING'])) {  $params = explode("&", $_SERVER['QUERY_STRING']);  $newParams = array();  foreach ($params as $param) {    if (stristr($param, "pageNum_rs_installs") == false &&         stristr($param, "totalRows_rs_installs") == false) {      array_push($newParams, $param);    }  }  if (count($newParams) != 0) {    $queryString_rs_installs = "&" . htmlentities(implode("&", $newParams));  }}$queryString_rs_installs = sprintf("&totalRows_rs_installs=%d%s", $totalRows_rs_installs, $queryString_rs_installs); 
 
// then further down the page i have a loop which looks as follows
//first the pagination
 <table border="0">  <tr>    <td><?php if ($pageNum_rs_installs > 0) {  ?>        <a href="<?php printf("%s?pageNum_rs_installs=%d%s", $currentPage, 0, $queryString_rs_installs); ?>">First</a>        <?php }  ?></td>    <td><?php if ($pageNum_rs_installs > 0) {  ?>        <a href="<?php printf("%s?pageNum_rs_installs=%d%s", $currentPage, max(0, $pageNum_rs_installs - 1), $queryString_rs_installs); ?>">Previous</a>        <?php }  ?></td>    <td><?php if ($pageNum_rs_installs < $totalPages_rs_installs) {  ?>        <a href="<?php printf("%s?pageNum_rs_installs=%d%s", $currentPage, min($totalPages_rs_installs, $pageNum_rs_installs + 1), $queryString_rs_installs); ?>">Next</a>        <?php }  ?></td>    <td><?php if ($pageNum_rs_installs < $totalPages_rs_installs) {  ?>        <a href="<?php printf("%s?pageNum_rs_installs=%d%s", $currentPage, $totalPages_rs_installs, $queryString_rs_installs); ?>">Last</a>        <?php }  ?></td>  </tr></table>   // then below that i have a do while loop  do { //some stuff }while($row_rs_installs = odbc_fetch_array($rs_installs));   

[Barand]

LIMIT x, y is peculiar to MySQL and is not present in MSSQL.

 

MySQL was developed by developers for developers - with MSSQL you get what Microshaft are prepared to give you. Compare the date functions too if you want a laugh.

 

If using MSSQL you have

 

 

SELECT TOP y .... FROM ...

 

but AFAIK there is no way to to get the equivalent of TOP Y starting at x.

 

There is a clunky workaround and that is to select the top y records that were not in the previously selected top x records.

Edited March 14, 2013 by Barand

[kicken]

For the latest SQL Server (2012) they did add an equivilant of mysql's LIMIT feature. It looks like:

SELECT 

	*

FROM blah

ORDER BY

	someField

OFFSET X ROWS FETCH NEXT Y ROWS ONLY

For older versions of SQL server, you have to do a few sub-queries using TOP to emulate it. Looks something like this:

SELECT * FROM (

	SELECT TOP Y * FROM (

		SELECT TOP X+Y

			*

		FROM blah

		ORDER BY

			someField ASC

	) t1

	ORDER BY

		someField DESC

) t2

ORDER BY

	someField ASC

Top X+Y means you have to do the sum of your # of desired records and the offset. So if you wanted to start at record 100 and get 10 records, the TOP X+Y would be 'TOP 110'