Can you put the echo statement back in and give me the query output on each page again, as I thought it should have changed the queries to BETWEEN 1 AND 1, 2 AND 2, 3 AND 3, 4 AND 4, for each page respectively.
Hi,
I had to leave the office pretty much straight after my last post, so would you mind if I reply tomorrow.
Thank you very much for all the help its been great, and to get somewhere too quickly is a bonus.
Cheers
Not a problem. Just post the results when you get a chance and we’ll keep working on a complete solution 
Morning cpradio,
OK I put the echo back in and these are the errors.
Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 order by ID DESC [1] 2 3 4 > >>
Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 2 AND 3 order by ID DESC << < 1 [2] 3 4 > >>
Page 3:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC << < 1 2 [3] 4 > >>
Page 4:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 4 AND 5 order by ID DESC << < 1 2 3 [4]
Thank again for the support
Okay, change
$limit=$offset + $rowsperpage;
to
$limit=($offset - 1) + $rowsperpage;
Summary of the recent changes:
This change was necessary because ROW_NUMBER starts at 1, not 0. So without the +1, you will get an additional record on pages 2+
$offset = ($currentpage - 1) * $rowsperpage + 1;
The following code is necessary to ensure the limit is met, since we added an additional 1 to the offset, we need to subtract it from the limit so instead of 2, it will remain 1 (or instead of 26, it will be 25; if rowsperpage was 25)
$limit=($offset - 1) + $rowsperpage;
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:
$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
$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:
$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:
$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:
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:
$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.
// 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
// 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
// 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:
// 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
// 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.