Struggling creating a pagination query in MSSQL The script is currently looping through the correct amount of pages so it is counting correctly, but the actual results on every page are the same, they don't change with the page. I think it's something to do with where I've used a LIMIT in MySQL, I don't really know what the alternative to LIMIT is in MSSQL. This was the MySQL working version: <?php $itemsPerPage = 5; $currentPageNumber = (int) $_GET['page']; if(empty($currentPageNumber)) $currentPageNumber = 1; $mpnQuery = "SELECT count(id) FROM publication_issue"; $result = mysql_query($mpnQuery) or die(mysql_error()); $data = mysql_fetch_array($result); $maxPageNumber = ceil($data[0]/$itemsPerPage); $start = ($currentPageNumber - 1) * $itemsPerPage; $query = "SELECT * FROM publication_issue LIMIT $start, $itemsPerPage"; $result = mysql_query($query) or die(mysql_error()); echo "<div id=\"btn\">"; while($row = mysql_fetch_array($result)){ $idi = $row[id]; $parent = $row[publication_id]; $thesmallimage = $row[smallimg]; $theissuenumber = $row[issue]; //Get Title echo "<div class=\"otherpub\"><a href=\"pubissue.php?id=$idi\" title=\"Read Issue $theissuenumber Now\"><img src=\"http://www.free2read.co.uk/uploader/$thesmallimage\" title=\"Issue $theissuenumber of $main_title\"></a></div>"; } echo "<div style='clear:both;'></div>"; if($currentPageNumber==1){ echo ''; } else{ echo "<div class=\"pubbtnleft\"><a href=\"?id=$idi&page=" .($currentPageNumber - 1). "\">Previous</a></div>"; } echo ' '; if($currentPageNumber==$maxPageNumber){ echo ''; } else{ echo "<div class=\"pubbtnright\"><a href=\"?id=$idi&page=" .($currentPageNumber + 1). "\">Next</a></div>"; } echo "</div>"; mysql_close(); ?> PHP: This is the one I'm struggling with. I'm quite new to MSSQL. <?php $itemsPerPage = 5; $currentPageNumber = (int) $_GET['page']; if(empty($currentPageNumber)) $currentPageNumber = 1; $mpnQuery = "SELECT count(id) FROM publication_issue"; $result = mssql_query($mpnQuery) or die(mssql_error()); $data = mssql_fetch_array($result); $maxPageNumber = ceil($data[0]/$itemsPerPage); $start = ($currentPageNumber - 1) * $itemsPerPage; $query = "SELECT TOP $itemsPerPage * FROM publication_issue"; $result = mssql_query($query) or die(mssql_error()); echo "<div id=\"btn\">"; while($row = mssql_fetch_array($result)){ $id = $row[id]; echo "<div class=\"otherpub\"><a href=\"pubissue.php?id=" . $row['id'] . "\" title=\"Read Issue " . $row['issue'] . " Now\"><img src=\"http://www.free2read.co.uk/uploader/" . $row['smallimg'] . "\" title=\"Issue " . $row['issue'] . " of " . $row['title'] . "\"></a></div>"; } echo "<div style='clear:both;'></div>"; if($currentPageNumber==1){ echo ''; } else{ echo "<div class=\"pubbtnleft\"><a href=\"?id=$id&page=" .($currentPageNumber - 1). "\">Previous</a></div>"; } echo ' '; if($currentPageNumber==$maxPageNumber){ echo ''; } else{ echo "<div class=\"pubbtnright\"><a href=\"?id=$id&page=" .($currentPageNumber + 1). "\">Next</a></div>"; } echo "</div>"; mssql_close(); ?> PHP:
Since MSSQL only takes 1 argument to LIMIT (and calls it TOP), you have to fake it (and choosing MSSQL as the database when you're paging is a bad decision). See http://vorg.ca/626-the-MS-SQL-equivalent-to-MySQLs-limit-command
Thanks for your input Rubkat. I don't understand the logic behind you saying paging in MSSQL is bad. In this project, MSSQL is more beneficial than using MySQL due to other areas of the project implementing Windows-based software. Anyhow, for those interested, here's the working code: <?php //Connect to Database $objConnect = mssql_connect("localhost","sa","********") or die("Error Connect to Database"); $objDB = mssql_select_db("db"); $strSQL = "SELECT * FROM publication_issue ORDER BY id ASC"; $objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]"); $Num_Rows = mssql_num_rows($objQuery); //Set limit per page $Per_Page = 2; // Per Page //Get the page number $Page = $_GET["Page"]; //Determine if it is the first page if(!$_GET["Page"]) { $Page=1; } //Declare previous/next page row guide $Prev_Page = $Page-1; $Next_Page = $Page+1; //Determine page start $Page_Start = (($Per_Page*$Page)-$Per_Page); if($Num_Rows<=$Per_Page) { $Num_Pages =1; } else if(($Num_Rows % $Per_Page)==0) { $Num_Pages =($Num_Rows/$Per_Page) ; } else { $Num_Pages =($Num_Rows/$Per_Page)+1; $Num_Pages = (int)$Num_Pages; } //Determine where the page will end $Page_End = $Per_Page * $Page; IF ($Page_End > $Num_Rows) { $Page_End = $Num_Rows; } ?> <? //Make it show next rows for($i=$Page_Start;$i<$Page_End;$i++) { ?> <div><?=mssql_result($objQuery,$i,"id");?></div> <div><?=mssql_result($objQuery,$i,"publication_id");?></div> <div><?=mssql_result($objQuery,$i,"smallimg");?></div> <? } ?> <br> Total <?= $Num_Rows;?> Record : <?=$Num_Pages;?> Page : <? //Previous page if($Prev_Page) { echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> "; } //Display total pages for($i=1; $i<=$Num_Pages; $i++){ if($i != $Page) { echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]"; } else { echo "<b> $i </b>"; } } //Create next page link if($Page!=$Num_Pages) { echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next>></a> "; } //Adios mssql_close($objConnect); ?> PHP:
The reason I say it is because in other databases (some, not all), you can do the entire pagination in your limit clause, you don't need external logic. There are also other reasons to not use MSSQL (such as scalability of the free version).
Having to use external logic for 1 query isn't a valid reason to stay away from MSSQL in my opinion. I'm also using the paid version of MSSQL which will be running on the server with ASP programs rather than on the domain, so in this instance using MSSQL is more beneficial for this project.
It gets tricky using nested subqueries. Not to mention you need to use dynamic sql strings which is not the best practice. So we push the selects into procedures and use CTE to do all the paging server side. Here is what the procedure looks like. CREATE PROCEDURE [dbo].[SP_Get_Assets]( @startRowIndex AS BIGINT =0, @maximumRows AS BIGINT =30 ) AS BEGIN --Calcuate the starting row. Since it is based on a zero based index, add 1 SET @startRow = @startRowIndex + 1 --Calculate the last row based on the starting index and the pagesize SET @endRow = @startRow + (@maximumRows - 1) /* The use of the following methodolgy is to allow server side paging. ------------------------------------------------------------------------ Select the results into two CTEs (common table entity). The first filters out non-distinct records. This is done seperately from the numbering CTE since the row numbers make the row distinct. The second CTE named numbered, adds the row_number for each record. This is neccessary so we can reference the row_number alias in the where clause as both aliases and window functions (like row_number) are not accssiable in the selecting where clause. */ ;WITH DISTINCTIVE AS ( --this cte is not neccessary unless you need to filter out duplicates SELECT * FROM SOMESTUFF ) ,NUMBERED AS ( SELECT --Select all our defined rows *, --Row number for paging RowNum = ROW_NUMBER() OVER(ORDER BY something_to_put_in_table_order DESC), TotalRows = COUNT(*) OVER() FROM DISTINCTIVE ) -- Select out all rows on the page, or all rows if the paging variables are not set up SELECT * FROM NUMBERED WHERE 1 = CASE WHEN @startRow IS NULL THEN 1 WHEN @startRow = -1 THEN 1 WHEN @endRow IS NULL THEN 1 WHEN @endRow = -1 THEN 1 WHEN @endRow = 0 THEN 1 WHEN RowNum BETWEEN @startRow AND @endRow THEN 1 ELSE 0 END ORDER BY RowNum END Code (markup):
This thread was bumped from a couple of months back, I managed to find a solution myself. I usually post if I find one but I must have forgotten. <?php $sth = $dbconn->prepare("SELECT id, title, tags FROM publication WHERE tags LIKE :keyword"); $params = array("keyword" => $_GET[id]); $sth->execute($params); $objQuery = $sth->fetch(); $Num_Rows = $sth->rowCount(); //Set limit per page $Per_Page = 9; // Per Page //Get the page number $Page = $_GET["Page"]; //Determine if it is the first page if(!$_GET["Page"]) { $Page=1; } //Declare previous/next page row guide $Prev_Page = $Page-1; $Next_Page = $Page+1; //Determine page start $Page_Start = (($Per_Page*$Page)-$Per_Page); if($Num_Rows<=$Per_Page) { $Num_Pages =1; } else if(($Num_Rows % $Per_Page)==0) { $Num_Pages =($Num_Rows/$Per_Page) ; } else { $Num_Pages =($Num_Rows/$Per_Page)+1; $Num_Pages = (int)$Num_Pages; } //Determine where the page will end $Page_End = $Per_Page * $Page; IF ($Page_End > $Num_Rows) { $Page_End = $Num_Rows; } ?> <ul style=""> <? //Make it show next rows for($i=$Page_Start;$i<$Page_End;$i++) { ?> <li><?=sth($objQuery,$i,"title");?></li> <? } echo ' </ul> '; //Previous page if($Prev_Page) { echo " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "; } //Display total pages for($i=1; $i<=$Num_Pages; $i++){ if($i != $Page) { echo "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a> "; } else { echo "<b> $i </b>"; } } //Create next page link if($Page!=$Num_Pages) { echo " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "; } //Adios $sth = null; ?> PHP:
Is that best practices for large result sets? Doesn't it push all the data to the client and then filter the results? With server side paging you get the advantages of a smaller result set, which can speed things up immensley. It's also easier to tweak the execution plan.
No it doesn't load all data first, it pulls the first 10 results and only loads the next set when ?page= changes.
Very interesting. I am coming off a Microsoft stack and have had a love hate thing going on with php. But I have to say, it's getting more and more love lately