Ok im am going through myself too, as dont want you thinking im not doing anything.
Have emailed you a .txt file with it all in.
Okay, I see what is going on. You need two queries.
You currently have this
$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
Notice, it is ONLY returning the COUNT(*)
So you need a second query that returns the contracts.
$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
$queryContracts = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select * from LIMIT WHERE {limit clause}";
Then you need to update the limit clause in the new query too
$query = str_replace('{limit clause}', $limitClause, $query); // added this line
$queryContracts = str_replace('{limit clause}', $limitClause, $queryContracts); // added this line
Then we need to limit the results of the Contracts query so the following line
$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
echo $query;
$stmt = sqlsrv_query($conn, $query);
Becomes
$queryContracts .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
echo $queryContracts;
$stmt = sqlsrv_query($conn, $queryContracts);
Then you need to use the new query’s results by replacing the following line
while ($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
With
while ($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
Ah sorry cpradio, I missed that you replied, was trying to see how to fix it, but wouldnt have got there by the seems, and have put your code in and the pageing on page 1 is correct, as 50 display and the contracts are there to see, which is fantastic.
I’m sorry to say but when I click page 2 the page refreshes, not pageing goes away and the contracts dont display, it basically goes back to how it is when you first go into the page, so I’m guessing im missing a variable or something.
Again as I dont want to bother you all day today again, will see if I can see it.
Thank you again cpradio, its looking great
I’m guessing this is the troubel
<?
if(isset($_POST['advrows']))
{
As its relying on the button click from the form to perform the search
<input type="submit" name="advrows" value="Submit">
Yeah, I knew the paging wouldn’t work going from page to page, but that is due to entirely different reasons, I wanted to make sure you got the results on Page 1 first before pursuing the rest.
This is going to be a bit of a process (so please ask questions, if you don’t understand how it works).
First off, the following IF statement contains TOO much code
if (isset($_POST['advrows'])) {
It contains EVERYTHING for your output, and that isn’t necessary. What it needs to contain should be limited to building the query.
So to get this on the right track, here is the updated IF statement (make sure you remove the additional } near the end of your page).
//var_dump($_POST);
$queryIsValid = true;
//$query = "SELECT * FROM Intranet WHERE";
$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
$queryContracts = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select * from LIMIT WHERE {limit clause}";
$limitClause = "";
if (isset($_POST['advrows'])) {
for ($i = 0; $i < sizeof($_POST['field_names']); $i++) {
if (strlen(trim($_POST['field_names'][$i])) !== 0 && strlen(trim($_POST['operator'][$i])) !== 0)
//&& strlen(trim($_POST['keyword'][$i])) !== 0)
{
if (($_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
$field_name = ($_POST['field_names'][$i]);
$operator = ($_POST['operator'][$i]);
$keyword = ($_POST['keyword'][$i]);
if ($i === 0) {
if ($operator == "like") {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
} else {
if ($operator == "like") {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
}
} else {
$queryIsValid = false;
echo "required information missing or is invalid - query was not executed";
break;
}
}
}
$_SESSION['query'] = $query; // added this line
$_SESSION['queryContracts'] = $queryContracts; // added this line
}
Okay, then we need to add an else statement to the above IF
else
{
if (isset($_SESSION['query']) && isset($_SESSION['queryContracts']))
{
$query = $_SESSION['query'];
$queryContracts = $_SESSION['queryContracts'];
}
}
The above will allow your queries to be passed from page to page now. And when a new search is performed, it will update the session with the new search criteria.
Finally, in the code to build the paging, remove ALL references to “&Search=$search&q=$searchKeyword”, those DO NOT exist in this context and were likely copied forward from your prior scripts (I counted 6 references to these).
You can also get rid of these sections of code, as they don’t apply
if ($search == "") {
} else {
echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:auro;line-height:20px;top:-4px;left:7px;margin-right:10px;>Search: <strong>'$search'</strong> | </span>";
}
if (isset($_GET['q'])) {
$searchKeyword = @$_GET['q'];
if ($searchKeyword == "") {
} else {
$trimmed = $searchKeyword;
echo "<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:2px;top:-3px;font-size:13px;color:#555555;font-family:verdana;position:relative;>Search results for your keyword: <strong>'" . $trimmed . "'</strong></span></div>";
}
}
AHHHHHHHHHHHH,
Sorry cpradio, I changed to your code, and went through it and through it and page 2 of pageing is still having the same problem, in that all the contracts go away and the pageing goes away too, it reverts back to how it is when you first enter the page. I wish I could help you out some more, and have tried.
if(isset($_POST['advrows']))
{
//var_dump($_POST);
$queryIsValid = true;
//$query = "SELECT * FROM Intranet WHERE";
$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
$queryContracts = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select * from LIMIT WHERE {limit clause}";
$limitClause = "";
if (isset($_POST['advrows'])) {
for ($i = 0; $i < sizeof($_POST['field_names']); $i++) {
if (strlen(trim($_POST['field_names'][$i])) !== 0 && strlen(trim($_POST['operator'][$i])) !== 0)
//&& strlen(trim($_POST['keyword'][$i])) !== 0)
{
if (($_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
$field_name = ($_POST['field_names'][$i]);
$operator = ($_POST['operator'][$i]);
$keyword = ($_POST['keyword'][$i]);
if ($i === 0) {
if ($operator == "like") {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
} else {
if ($operator == "like") {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
}
} else {
$queryIsValid = false;
echo "required information missing or is invalid - query was not executed";
break;
}
}
}
$_SESSION['query'] = $query; // added this line
$_SESSION['queryContracts'] = $queryContracts; // added this line
}
else
{
if (isset($_SESSION['query']) && isset($_SESSION['queryContracts']))
{
$query = $_SESSION['query'];
$queryContracts = $_SESSION['queryContracts'];
}
}
if ($queryIsValid)
{
$query = str_replace('{limit clause}', $limitClause, $query); // added this line
$queryContracts = str_replace('{limit clause}', $limitClause, $queryContracts); // added this line
//$result = sqlsrv_query($conn, $query);
//$result2 = sqlsrv_query($conn, $query);
$result = sqlsrv_query($conn, $query);
//if( $result=== false ) {
//die( print_r( sqlsrv_errors(), true));
//}
$r = sqlsrv_fetch_array($result);
$numrows = $r[0];
// number of rows to show per page
if ($rows == ""){
$rowsperpage = 50;
} else {
$rowsperpage = $rows;// 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);
//$query = str_replace('{limit clause}', $limitClause, $query); // added this line
//$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
//echo $query;
$queryContracts .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
//echo $queryContracts;
$stmt = sqlsrv_query($conn, $queryContracts);
//$stmt = sqlsrv_query($conn, $query);
//var_dump($conn, $query);
/****** build the pagination links ******/// range of num links to show
$range = 30;
echo "<div style=position:relative;height:30px;line-height:30px;><span style=font-family:Arial;font-size:14px;color:#333333;margin-top:9px;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
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Rows=$rows' style='color:#333333'><<</a>";
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Rows=$rows' style='color:#333333'><</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
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Rows=$rows' style='color:#333333'>$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
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Rows=$rows' style='color:#333333'>></a> ";
// echo forward link for lastpage
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Rows=$rows' style='color:#333333'>>></a> ";
} // end if
/****** end build pagination links ******/
echo "</span>";
if ($rows=="1000000"){
echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:143px;line-height:20px;top:-4px;left:30px;>Showing <strong>'All'</strong>. | </span>";
} else {
echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:200px;line-height:20px;top:-4px;left:30px;>Showing <strong>'$rowsperpage'</strong> per page | </span>";
}
if(isset($_GET['q']))
{
$searchKeyword = @$_GET['q'];
if ($searchKeyword=="") {
} else {
$trimmed = $searchKeyword;
echo "<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:2px;top:-3px;font-size:13px;color:#555555;font-family:verdana;position:relative;>Search results for your keyword: <strong>'" .$trimmed. "'</strong></span></div>";
}
}
// To change line colour
$color="1";
//echo $query;
while ($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
Sorry, I screwed up. It wasn’t $query and $queryContracts that the session needed to keep track of, but rather $limitClause.
You have this code
$_SESSION['query'] = $query; // added this line
$_SESSION['queryContracts'] = $queryContracts; // added this line
} else {
if (isset($_SESSION['query']) && isset($_SESSION['queryContracts'])) {
$query = $_SESSION['query'];
$queryContracts = $_SESSION['queryContracts'];
}
}
Replace it with
$_SESSION['limitClause'] = $limitClause; // added this line
} else {
if (isset($_SESSION['limitClause'])) {
$limitClause = $_SESSION['limitClause'];
}
}
Oh! And another piece. Remove this line (which appears above the commented out var_dump($_POST) call)
if (isset($_POST['advrows'])) {
Then remove the last } as shown below
} //remove this one!
?>
<table>
<tr>
<td>
<div style="position:relative; width:100%; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:12px; margin-top:10px;">Return to <a href="#Top">TOP</a></div>
<div style="position:relative;float:left;height:50px;width:200px;margin-top:20px;margin-left:-2px;"><a href="http://www.checksafetyfirst.com" target="_blank"><img src="images/csf_logo.jpg" border="0"></a></div>
</td>
<tr>
</table>
</body>
</html>
Monring cpradio,
Thank you very much, that works perfectly, and its also very good that it remembers your search if you leave the page and come back too.
Thank you again…
I got a few things to finish this off, but you be happy to know you will be free of me today 
Thanks again though, its a lot to take in, but I think when I keep going through it and trying things out, it will sink in, but its very high end stuff, and I appreciate your help.