there are many fine articles out there on how to do it, and i don’t think an impromptu reply on a forum is going to be of higher quality, so you should really pick one of those articles and give it a serious try
unfortunately, LIMIT is a proprietary sql feature that exists only in mysql
Ok I have made some in roads here as I did find a tutorial after but it was for php and MySQL, and I need it in MSSQL, but one person replied in a thread on that site and added some changes to accomodate MSSQL, but in honesty Im not sure where he means to put or make the change.
Her is his change for MSSQL:
// CREATE A LIMIT QUERY FOR MS SQL DATABASES
$query = "WITH LIMIT AS(
SELECT FIELD1, FIELD2,
ROW_NUMBER() OVER (ORDER BY FIELD1) AS 'RowNumber'
FROM some_table)
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit";
$result = mssql_query($query);
// while there are rows to be fetched...
while ($list = mssql_fetch_array($result)) {
echo $list['field1'] . " : " . $list['field2'] . "<br />";
}
And here is my code again with the changes.
<?php
// find out how many rows are in the table
$sql = "SELECT COUNT(*) FROM Intranet";
$result = sqlsrv_query($conn, $sql);
$r = sqlsrv_fetch_array($result);
$numrows = $r[0];
//echo $result;
// number of rows to show per page
$rowsperpage = 1;// 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;
$limit=$offset + $rowsperpage;
if ($search==""){
$query = "select * from Intranet order by ID DESC";
} else {
$query = "select * from Intranet $search";
}
$stmt = sqlsrv_query($conn, $query);
//echo $query;
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
//echo $data['ID'];
/****** build the pagination links ******/// range of num links to show
$range = 3;
// 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 "<tr>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&Admin=Edit'><img src='images/icon_edit.gif' alt='Edit Item' height='13'></a></td>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='read_contract.php?ID=".$data["ID"]."'><img src='images/icon_view.gif' alt='View Item & Print'></td>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&AdminDelete'><img src='images/delete_Icon.jpg' alt='Delete Item'></a></td>";
echo "<td width='70' class='tableData'>".$data["Contract_Number"]."</td>";
echo "<td width='70' class='tableData'>".$data["Contract_Status"]."</td>";
echo "<td width='55' class='tableData'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80' class='tableData'>Contract Start</td>";
echo "<td width='80' class='tableData'>Contract Current</td>";
echo "<td width='60' class='tableData'>".$data["Contract_Length"]."</td>";
echo "<td width='80' class='tableData'>Renewal Date</td>";
echo "<td width='110' class='tableData'>".$data["Site_Name"]."</td>";
echo "<td width='80' class='tableData'>".$data["Street"]."</td>";
echo "<td width='80' class='tableData'>".$data["City"]."</td>";
echo "<td width='100' class='tableData'>".$data["State"]."</td>";
echo "<td width='100' class='tableData'>".$data["Country"]."</td>";
echo "<td width='60' class='tableData'>".$data["Region"]."</td>";
echo "<td width='60' class='tableData'>";
if ($data["Group_Member"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='100' class='tableData'>".$data["Group_Name"]."</td>";
echo "<td width='45' class='tableData'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90' class='tableData'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60' class='tableData'>";
if ($data["CSF"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Food_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Swimming_Pool_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Legionella_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Fire_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Hurricane"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Green"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Guest"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Room_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Quality_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Supply_Safety"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Tourcheck"]==1){
echo "Yes";
} else {
echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>".$data["No_of_Modules"]."</td>";
echo "<td width='60' class='tableData'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60' class='tableData'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60' class='tableData'>".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='45' class='tableData'>".$data["Seasonal_or_Full"]."</td>";
echo "<td width='65' class='tableData'>".$data["Month_Opens"]."</td>";
echo "<td width='65' class='tableData'>".$data["Month_Closes"]."</td>";
echo "<td width='65' class='tableData'>".$data["Invoicing_Profile"]."</td>";
echo "</tr>";
}
?>
Like I said it seems to work as I have only 4 contracts in the database at the moment, so I cut the number of contracts to display per page to 1.
What I get then is the actual pageination bit showing up, it not directly controlling the content but also the pageination bit is duplicating itself in accordance with the same number of contracts.
I can PM people my details if you would like to see it on the site.
You will want to move your pagination logic outside of your while loop. It should be either before or after your while loop (depending on if you want it above or below your records)
From what I can tell, your queries are spot on, so good job.
Ah yes, that solved the issue of the pageination creating itself the same number of times as the number of contracts, which is great.
The pageination is working as such, but the contracts arent only showing the number I set it to which was 1, they are all outputting, so the pageination has no effect on it.
Let me show you first where I put just to be sure its in the right place.
if ($search==""){
$query = "select * from Intranet order by ID DESC";
} else {
$query = "select * from Intranet $search";
}
echo $query;
$result = mssql_query($query);
This is the error that came up
select * from Intranet order by ID DESC Fatal error: Call to undefined function mssql_query() in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 221
Umm, I’m sorry Im not sure what you mean by CTE. Do you mean I need to add this bit that was posted in the other thread.
// CREATE A LIMIT QUERY FOR MS SQL DATABASES
$query = “WITH LIMIT AS(
SELECT FIELD1, FIELD2,
ROW_NUMBER() OVER (ORDER BY FIELD1) AS ‘RowNumber’
FROM some_table)
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit”;
$result = mssql_query($query);
// while there are rows to be fetched…
while ($list = mssql_fetch_array($result)) {
echo $list[‘field1’] . " : " . $list[‘field2’] . “<br />”;
}
Okay, remove the mssql_query() reference, as that I just took from one of your other code snippets, but it isn’t necessary in your entire file snippet.
// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
$limit=$offset + $rowsperpage;
if ($search==""){
$query = "select * from Intranet order by ID DESC";
} else {
$query = "select * from Intranet $search";
}
$stmt = sqlsrv_query($conn, $query);
You have the above, but that isn’t using the LIMIT CTE that you created.
So you need to switch it to:
// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
$limit=$offset + $rowsperpage;
if ($search==""){
$query = "WITH LIMIT AS(
SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'
FROM Intranet)
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit order by ID DESC";
} else {
$query = "WITH LIMIT AS(
SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'
FROM Intranet)
select * from LIMIT WHERE $search AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
}
$stmt = sqlsrv_query($conn, $query);
Yes brilliant that worked, but its now doing something strange.
On the first page only 1 shows which is right, but on the second page the first one shows and the second one, and then on the third page the third one shows and the second one and so on.
Its showing the correct one on each page, plus the one before it.
Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 0 AND 1 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 1 AND 2 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 2 AND 3 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 3 AND 4 order by ID DESC << < 1 2 3 [4]
That unfortunately didnt fix it, what it did by the seems was add one to the front page so it showed 2 instead of 1 and took it away from the last page where it showed 1 only.
Is it dooable so that if you set it to say display 25 it only displays that number on each page, and each time they are fresh results
