Okay, I see what is going on. You need two queries.
You currently have this
PHP Code:
$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.
PHP Code:
$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
PHP Code:
$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
PHP Code:
$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
echo $query;
$stmt = sqlsrv_query($conn, $query);
Becomes
PHP Code:
$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
PHP Code:
while ($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
With
PHP Code:
while ($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
Reply With Quote
Originally Posted by multichild
Bookmarks