Paging using PHP & MSSQL

Thanks cpradio that works perfectly now.

Thank you very much, you have really helped a lot, and makes me wonder how long people like you have been at this php game, as although I'm trying my best it still doesnt match this sort of knowledge.

But thank you again.

Hi cpradio,

Me again, I'm following up from the other thread with the wildcard issue, which worked perfectly well, but it didnt work perfectly with the pageing.

Basically it does the right thing by only displaying the contracts but the pageing doesnt react accordingly.

Whats happening is that although the contracts on show have been reduced due to the wildcard fix, the pageing doesnt react to the number of contracts on show, it still shows the number of contracts in the database.

The code below is what changed with the wildcard:

PHP Code:

---

  $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit order by ID DESC"; 


---

You need to update the query that has COUNT() in it

PHP Code:

---

$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; // Added WHERE clause
$result = sqlsrv_query($conn, $sql); 
$r = sqlsrv_fetch_array($result); 
$numrows = $r[0]; 


---

LOl fair play cpradio!!!

I do have one more thing, and will post it after this, as Im yet to work out how to post and I will also have a go at it myself.

Thanks again

Thank you cpradio I got it sorted myself...

Thanks to your use of the echo feedback.

Thanks again for everything

Not problem, if you ever need additional help, don't hesitate to ask here on Sitepoint

As it goes I got a small issue which I cant seem to work out.

Basically to allow the user to show the contracts in different ways, I created a drop down and made it liek this ,although you will probably think its a bit old fashioned, it worked so I stuck with it.

But in some cases it works and in others it doesnt.

Here is the code:

PHP Code:

---

$search=$_POST['Search'];

<form name="form1" action="contracts.php" method="post" enctype="multipart/form-data">
<td height="27" style="position:relative;left:16px;"><span style="font-family:Verdana, Arial, Helvetica, sans-serif; color:#333333; font-size:13px;position:relative;">View By:</span> 
<select name="Search">
<option value="">Select Status</option> 
<!--<option <?php if($cStatus == "Active") echo "selected"; ?> value="Active">Active</option>--> 
<option value="Order by ID ASC">All Contracts (Old-New)</option>
<option value="Order by ID DESC">All Contracts (New-Old)</option>
<option value="WHERE (Contract_Status='On Hold') Order by Contract_Number">All Contracts (On Hold)</option>
<option value="WHERE (Contract_Status='Active') Order by Contract_Number">All Contracts (Active)</option>
<option value="WHERE (Contract_Status='Terminated') Order by Contract_Number">All Contracts (Terminated)</option>  
<option value="Order by Country ASC">By Country (A-Z)</option> 
<option value="Order by Region ASC">By Region (A-Z)</option> 
<option value="Order by No_of_Modules ASC">By Number of Modules (0-10)</option>
<option value="Order by Annual_Contract_Value">By Contract Value (Low-High)</option>
<option value="Order by Annual_Contract_Value DESC">By Contract Value (High-Low)</option>
<option value="WHERE (Food_Safety='1') order by Contract_Number ASC">By FoodCheck (Yes)</option>
<option value="WHERE (Swimming_Pool_Safety='1') order by Contract_Number ASC">By PoolCheck (Yes)</option>
<option value="WHERE (Legionella_Safety='1') order by Contract_Number ASC">By AquaCheck (Yes)</option>
<option value="WHERE (Fire_Safety='1') order by Contract_Number ASC">By FireCheck (Yes)</option>
<option value="WHERE (Hurricane='1') order by Contract_Number ASC">By CrisisCheck (Yes)</option>
<option value="WHERE (Green='1') order by Contract_Number ASC">By EcoCheck (Yes)</option>
<option value="WHERE (Guest='1') order by Contract_Number ASC">By SafetyCheck (Yes)</option>
<option value="WHERE (Room_Safety='1') order by Contract_Number ASC">By RoomCheck (Yes)</option>
<option value="WHERE (Quality_Safety='1') order by Contract_Number ASC">By QualityCheck (Yes)</option>
<option value="WHERE (Supply_Safety='1') order by Contract_Number ASC">By SupplyCheck (Yes)</option>
<option value="WHERE (Tourcheck='1') order by Contract_Number ASC">By Tourcheck (Yes)</option>
</select>
<input type="submit" name="btnsubmit" value="Submit" class="btn-search">
</td>
</form>

---

Then in the code below I added $search to finish off the select function as below:

PHP Code:

---

  $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit $search"; 


---

All is fine when $search does star with a WHERE, but works without it. Is there something wrong with the way my code is working.

Here is the error when I ask it to display using the following:

<option value="WHERE (Contract_Status='Active') Order by Contract_Number">All Contracts (Active)</option>

echo = WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 WHERE (Contract_Status='Active') Order by Contract_Number [1] 2 > >> Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 265

Okay, you should really re-work this 100%. First you have opened yourself to a SQL Injection attack using this approach (not good).

So I would instead assign each <option> a value of something along these lines: AllContractsOldNew, AllContractsNewOld, AllContractsOnHold, etc.

Then use a switch statement to build your query, or an array (examples shown below)
Switch Statement:

PHP Code:

---

switch($search)
{
    case "AllContractsOldNew":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by ID ASC";  
        break;
     ...
    case "AllContractsOnHold":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";  
        break;
      ...
} 


---

Example of Array:

PHP Code:

---

$searchQueries = array();
$searchQueries['AllContractsOldNew'] = "Order by ID ASC";
...
$searchQueries['AllContractsOnHold'] = "AND (Contract_Status='On Hold') Order by Contract_Number";
...

$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit " . $searchQueries[$search]; 


---

This way you are in control of the final SQL statement and not the user.

OK thank you cpradio, I didnt know about the SQL injection. I will do as you say, and having looked at your answer I think the first one site easiest with me.

Will make the change now.

Cheers

Okay, and if you run into problems, post your updated code so I can help you resolve it.

Hi cpradio,

The new way of doing the order of the contracts by the drop downs worked perfectly.

But as usual I noticed a bug.

What seems to be happening is that again its displaying more contracts than it should, and after page 1 the ordering doesnt carry through.

I'e posted the code below, but I'm pretty sure your going to say its the this bit below, but cant see how to work this in.

PHP Code:

---

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$result = sqlsrv_query($conn, $sql);
$r = sqlsrv_fetch_array($result);
$numrows = $r[0]; 


---

All the code together is below

PHP Code:

---

<?php
// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$result = sqlsrv_query($conn, $sql);
$r = sqlsrv_fetch_array($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 2;// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {   
// cast var as int   
$currentpage = (int) $_GET['currentpage'];
} else {   
// default page num   
$currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {   
// set current page to last page   
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {   
// set current page to first page   
$currentpage = 1;
} // end if

// the offset of the list, based on current page  
$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset - 1) + $rowsperpage; 

if ($search==""){ 
  $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit order by ID DESC"; 
} else { 
  switch($search)
{
    case "AllContractsOld-New":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by ID ASC";  
        break;
    case "AllContractsNew-Old":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by ID DESC";  
        break;
    case "AllContractsOnHold":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";  
        break;
    case "AllContractsActive":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='Active') Order by Contract_Number";  
        break;
    case "AllContractsTerminated":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='Terminated') Order by Contract_Number";  
        break;
    case "ByCountryA-Z":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Country ASC";  
        break;
    case "ByCountryZ-A":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Country DESC";  
        break;
    case "ByRegionA-Z":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Region ASC";  
        break;
    case "ByRegionZ-A":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Region DESC";  
        break;
    case "ByNumberModulesO-X":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by No_of_Modules ASC";  
        break;
    case "ByNumberModulesX-O":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by No_of_Modules DESC";  
        break;
    case "ByContractValueLow-High":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Annual_Contract_Value ASC";  
        break;
    case "ByContractValueHigh-Low":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Annual_Contract_Value DESC";  
        break;
    case "ByFoodCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Food_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByPoolCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByAquaCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Legionella_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByFireCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Fire_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByCrisisCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Hurricane='1') Order by Contract_Number ASC";  
        break;
    case "ByEcoCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Green='1') Order by Contract_Number ASC";  
        break;
    case "BySafetyCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Guest='1') Order by Contract_Number ASC";  
        break;
    case "ByRoomCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Room_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByQualityCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Quality_Safety='1') Order by Contract_Number ASC";  
        break;
    case "BySupplyCheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Supply_Safety='1') Order by Contract_Number ASC";  
        break;
    case "ByTourcheckYes":
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Tourcheck='1') Order by Contract_Number ASC";  
        break;
    }  
}  
//echo $query;
$stmt = sqlsrv_query($conn, $query);  

/******  build the pagination links ******/// range of num links to show
$range = 30;
echo "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>";
// if not on page 1, don't show back links
if ($currentpage > 1) {   
// show << link to go back to page 1 
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";   
// get previous page num   
$prevpage = $currentpage - 1;   
// show < link to go back to 1 page   
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {   
// if it's a valid page number...   
if (($x > 0) && ($x <= $totalpages)) {      
// if we're on current page...      
if ($x == $currentpage) {         
// 'highlight' it but don't make a link         
echo " [<b>$x</b>] ";      
// if not current page...      
} else {         
// make it a link         
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";      
} // end else   
} // end if 
} // end for

// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {   
// get next page   
$nextpage = $currentpage + 1;    
// echo forward link for next page    
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";   
// echo forward link for lastpage   
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
echo "</span>";
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))          
{

---

Back to we need to update the COUNT() query. I've moved some of your code around to make it easier to set both queries in a single switch statement.

PHP Code:

---

<?php 
// find out how many rows are in the table  

switch($search) 
{ 
    case "AllContractsOld-New":                 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by ID ASC";   
        break; 
    case "AllContractsNew-Old": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by ID DESC";   
        break; 
    case "AllContractsOnHold": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";   
        break; 
    case "AllContractsActive": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='Active') Order by Contract_Number";   
        break; 
    case "AllContractsTerminated": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Contract_Status='Terminated') Order by Contract_Number";   
        break; 
    case "ByCountryA-Z": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Country ASC";   
        break; 
    case "ByCountryZ-A": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Country DESC";   
        break; 
    case "ByRegionA-Z": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Region ASC";   
        break; 
    case "ByRegionZ-A": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Region DESC";   
        break; 
    case "ByNumberModulesO-X": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by No_of_Modules ASC";   
        break; 
    case "ByNumberModulesX-O": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by No_of_Modules DESC";   
        break; 
    case "ByContractValueLow-High": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Annual_Contract_Value ASC";   
        break; 
    case "ByContractValueHigh-Low": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit Order by Annual_Contract_Value DESC";   
        break; 
    case "ByFoodCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Food_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByPoolCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByAquaCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Legionella_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByFireCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Fire_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByCrisisCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Hurricane='1') Order by Contract_Number ASC";   
        break; 
    case "ByEcoCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Green='1') Order by Contract_Number ASC";   
        break; 
    case "BySafetyCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Guest='1') Order by Contract_Number ASC";   
        break; 
    case "ByRoomCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Room_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByQualityCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Quality_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "BySupplyCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Supply_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByTourcheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit AND (Tourcheck='1') Order by Contract_Number ASC";   
        break;
    default:
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit order by ID DESC";  
        break;
}

$result = sqlsrv_query($conn, $sql); 
$r = sqlsrv_fetch_array($result); 
$numrows = $r[0]; 

// number of rows to show per page 
$rowsperpage = 2;// find out total pages 
$totalpages = ceil($numrows / $rowsperpage); 

// get the current page or set a default 
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {    
// cast var as int    
$currentpage = (int) $_GET['currentpage']; 
} else {    
// default page num    
$currentpage = 1; 
} // end if 

// if current page is greater than total pages... 
if ($currentpage > $totalpages) {    
// set current page to last page    
$currentpage = $totalpages; 
} // end if 
// if current page is less than first page... 
if ($currentpage < 1) {    
// set current page to first page    
$currentpage = 1; 
} // end if 

// the offset of the list, based on current page   
$offset = ($currentpage - 1) * $rowsperpage + 1; 
$limit=($offset - 1) + $rowsperpage;  

//echo $query; 
$stmt = sqlsrv_query($conn, $query);   

/******  build the pagination links ******/// range of num links to show 
$range = 30; 
echo "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>"; 
// if not on page 1, don't show back links 
if ($currentpage > 1) {    
// show << link to go back to page 1  
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";    
// get previous page num    
$prevpage = $currentpage - 1;    
// show < link to go back to 1 page    
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; 
} // end if 

// loop to show links to range of pages around current page 
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {    
// if it's a valid page number...    
if (($x > 0) && ($x <= $totalpages)) {       
// if we're on current page...       
if ($x == $currentpage) {          
// 'highlight' it but don't make a link          
echo " [<b>$x</b>] ";       
// if not current page...       
} else {          
// make it a link          
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";       
} // end else    
} // end if  
} // end for 

// if not on last page, show forward and last page links         
if ($currentpage != $totalpages) {    
// get next page    
$nextpage = $currentpage + 1;     
// echo forward link for next page     
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";    
// echo forward link for lastpage    
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; 
} // end if 
/****** end build pagination links ******/ 
echo "</span>"; 
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))           
{

---

It came back with an error below:

Notice: Undefined variable: offset in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 311 Notice: Undefined variable: limit in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 311

So I'm guessing that we need to perhaps put:

PHP Code:

---

// the offset of the list, based on current page   
$offset = ($currentpage - 1) * $rowsperpage + 1; 
$limit=($offset - 1) + $rowsperpage; 


---

Before the switch statement

Sorry, I completely missed that, here is the updated code:

PHP Code:

---

<?php 
// find out how many rows are in the table  

switch($search) 
{ 
    case "AllContractsOld-New":                 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by ID ASC";   
        break; 
    case "AllContractsNew-Old": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by ID DESC";   
        break; 
    case "AllContractsOnHold": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number";   
        break; 
    case "AllContractsActive": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number";   
        break; 
    case "AllContractsTerminated": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number";   
        break; 
    case "ByCountryA-Z": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Country ASC";   
        break; 
    case "ByCountryZ-A": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Country DESC";   
        break; 
    case "ByRegionA-Z": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Region ASC";   
        break; 
    case "ByRegionZ-A": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Region DESC";   
        break; 
    case "ByNumberModulesO-X": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC";   
        break; 
    case "ByNumberModulesX-O": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC";   
        break; 
    case "ByContractValueLow-High": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC";   
        break; 
    case "ByContractValueHigh-Low": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC";   
        break; 
    case "ByFoodCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByPoolCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByAquaCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByFireCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByCrisisCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC";   
        break; 
    case "ByEcoCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC";   
        break; 
    case "BySafetyCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC";   
        break; 
    case "ByRoomCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByQualityCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "BySupplyCheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC";   
        break; 
    case "ByTourcheckYes": 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC";   
        break;
    default:
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE {limit clause} order by ID DESC";  
        break;
}

$result = sqlsrv_query($conn, $sql); 
$r = sqlsrv_fetch_array($result); 
$numrows = $r[0]; 

// number of rows to show per page 
$rowsperpage = 2;// find out total pages 
$totalpages = ceil($numrows / $rowsperpage); 

// get the current page or set a default 
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {    
// cast var as int    
$currentpage = (int) $_GET['currentpage']; 
} else {    
// default page num    
$currentpage = 1; 
} // end if 

// if current page is greater than total pages... 
if ($currentpage > $totalpages) {    
// set current page to last page    
$currentpage = $totalpages; 
} // end if 
// if current page is less than first page... 
if ($currentpage < 1) {    
// set current page to first page    
$currentpage = 1; 
} // end if 

// the offset of the list, based on current page   
$offset = ($currentpage - 1) * $rowsperpage + 1; 
$limit=($offset - 1) + $rowsperpage;  
$query = str_replace('{limit clause}', "RowNumber BETWEEN $offset AND $limit", $query);

//echo $query; 
$stmt = sqlsrv_query($conn, $query);   

/******  build the pagination links ******/// range of num links to show 
$range = 30; 
echo "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>"; 
// if not on page 1, don't show back links 
if ($currentpage > 1) {    
// show << link to go back to page 1  
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";    
// get previous page num    
$prevpage = $currentpage - 1;    
// show < link to go back to 1 page    
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; 
} // end if 

// loop to show links to range of pages around current page 
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {    
// if it's a valid page number...    
if (($x > 0) && ($x <= $totalpages)) {       
// if we're on current page...       
if ($x == $currentpage) {          
// 'highlight' it but don't make a link          
echo " [<b>$x</b>] ";       
// if not current page...       
} else {          
// make it a link          
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";       
} // end else    
} // end if  
} // end for 

// if not on last page, show forward and last page links         
if ($currentpage != $totalpages) {    
// get next page    
$nextpage = $currentpage + 1;     
// echo forward link for next page     
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";    
// echo forward link for lastpage    
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; 
} // end if 
/****** end build pagination links ******/ 
echo "</span>"; 
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))           
{

---

That hasnt come back with any errors, and it has solved the issue of only showing whats relevant to the ordering selection.

As in if only 1 needs to show, only 1 does, where before 1 appeared on page 1 but then the rest appeared on the other pages.

But, and I'm sorry this is going on there is still 1 more issue.

When you say select the highest contracts first it works fine on page 1 as in highest first, but the contracts on the other pages because of the pageing dont comply, they just appear as they wish, well you know what I mean, it doesnt comply with the ordering of page 1.

Thank again cpradio, I am very grateful.

echo out the query for each page and post it here, that will likely lead to the problem.

I just checked it with a 'Terminated' contract ordering as there only 1 and the contract didnt show, so looked into it and as I have set it so that 2 contracts are to be displayed per page if anything that would have fallen onto the second page has to now appear on the first page, it doesnt happen.

They are still odering by how the are in the database if you can see what I mean.

case "AllContractsOld-New":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by ID ASC

case "AllContractsNew-Old":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by ID DESC

case "AllContractsOnHold":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number

case "AllContractsActive":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number

case "AllContractsTerminated":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number

case "ByCountryA-Z":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Country ASC

case "ByCountryZ-A":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Country DESC

case "ByRegionA-Z":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Region ASC

case "ByRegionZ-A":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Region DESC

case "ByNumberModulesO-X":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC

case "ByNumberModulesX-O":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC

case "ByContractValueLow-High":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC

case "ByContractValueHigh-Low":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC

case "ByFoodCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC

case "ByPoolCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC

case "ByAquaCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC

case "ByFireCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC

case "ByCrisisCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC

case "ByEcoCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC

case "BySafetyCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC

case "ByRoomCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC

case "ByQualityCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC

case "BySupplyCheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC

case "ByTourcheckYes":
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC

Default:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} order by ID DESC

Sorry, probably should have been a bit more specific on location and what I would need to see.

Change

PHP Code:

---

$stmt = sqlsrv_query($conn, $query); 


---

To

PHP Code:

---

echo $query;
$stmt = sqlsrv_query($conn, $query); 


---

For example, just pick one search type (one that filters the results), then visit page 1, copy the query and paste it here, visit page 2, copy the query, and paste it here.

Sorry mate, OK have choosen the 'Contract Value Low High'

Here is the echo of Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC

Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC

Just to point it out, the way they are order in the database is:

DOM00001 - 16800
DOM00002 - 1000
DOM00003 - 3000
DOM00004 - 0
EGY00001 - 2000

And this is how they appear in the pageing

Page 1:
DOM00002 - 1000
DOM00001 - 16800

Page 2:
DOM00004 - 0
DOM00003 - 3000

Okay, first thing you should notice is that the ORDER BY clause is different for both pages.

Page 1 orders by Annual_Contract_Value and Page 2 orders by ID.

So what that means, is you need to some how persist the $search value across each page so it knows to sort it the correct way.

One way would be to pass it along with each page

Find

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; 


---

Replace with

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Search=$search'><<</a> "; 


---

Find

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; 


---

Replace with

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Search=$search'><</a> "; 


---

Find

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; 


---

Replace with

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Search=$search'>$x</a> "; 


---

Find

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; 


---

Replace with

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Search=$search'>></a> "; 


---

Find

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; 


---

Replace with

PHP Code:

---

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Search=$search'>>></a> "; 


---

Right I see yes.

I did that, and it carried through search, but its didnt appear to re-adjust the ordering for each page.

I tested it with the contract value low high ordering and the results where the same.

Okay, give me the query outputs again, and also records that are being displayed (their Contract_Number and their Annual_Contract_Value)

OK Have gone for High to Low Contract Values again, and here is the echo for
Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC

Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC
The ordering is:

Page 1:
DOM00001 - 16800
DOM00002 - 1000

Page 2:
DOM00004 - 0
DOM00003 - 3000

Okay, here is where it gets complicated again (also note that your $search variable was persisted on Page 2...)

You need to update the WITH LIMIT statement to (ORDER BY Annual_Contract_Value DESC)

Example:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%')

This way it will number the records based on how you want to receive them. Likewise, if you have any where clauses (example: (Quality_Safety='1')), you need to add that to the LIMIT statement too, like so:

PHP Code:

---

        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC"; 


---