www.webdeveloper.com
Results 1 to 3 of 3

Thread: Pagination with PHP and MSSQL (how-to)

Hybrid View

  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Pagination with PHP and MSSQL (how-to)

    I looked everywhere on the web when i was trying to figure this out and never got a single place where i got all the info at once. So i figured i would sign up here, and make this post. I got some of the pagination info from a mysql how-to from about.com for this how-to so i wont take credit for that.

    PHP Code:
    <?php 
    //Connects your database
    $dbhost 'localhost';
    $dbuser 'username';
    $dbpass 'password';
    $db 'database';
    $connect_db mssql_connect $dbhost$dbuser$dbpass ) or die(mssql_error());
    mssql_select_db $db$connect_db ) or die(mssql_error());

    //This checks to see if there is a page number, that the number is not 0, and that the number is actually a number. If not, it will set it to page number to 1.
    if ((!isset($_GET['pagenum'])) || (!is_numeric($_GET['pagenum'])) || ($_GET['pagenum'] < 1)) { $pagenum 1; }
    else { 
    $pagenum $_GET['pagenum']; }

    //Now you can use this query to see how many rows you are dealing with
    //Edit $result as your query
    $result mssql_query ("SELECT name FROM table_name") or die(mssql_error());
    $rows mssql_num_rows($result);

    //This is the number of results displayed per page 
    $page_rows 4

    //This tells us the page number of our last page 
    $last ceil($rows/$page_rows); 

    //Seeing if the current page we are on is the last
    if (($pagenum $last) && ($last 0)) { $pagenum $last; }

    //This sets the range to display in our query 
    $max = ($pagenum 1) * $page_rows;

    //This is your query again, just spiced up a bit
    //mssql doesnt have that nice limit ability like mysql... so we use this to make it work...
    //the way the table is designed is, "id" is the unique id, and "name" is just a list of names i have in there.
    $result2 mssql_query("select top $page_rows name from table_name where id not in (select top $max id from table_name order by id asc) order by id asc") or die(mssql_error()); 

    //This is where you show your results
    while($info mssql_fetch_array$result2 )) 

    print 
    $info['name']; 
    echo 
    "<br>";

    echo 
    "<p>";

    // This shows the page they are on, and the total number of pages
    echo " --Page $pagenum of $last-- <p>";

    // First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the 

    first page, and to the previous page.
    if (
    $pagenum == 1) { } 
    else 
    {
    echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
    echo 
    " ";
    $previous $pagenum-1;
    echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";


    //just a spacer
    echo " ---- ";

    //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
    if ($pagenum == $last
    {

    else {
    $next $pagenum+1;
    echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
    echo 
    " ";
    echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";

    ?>
    Okay thats a basic paged out version of a mssql query... there is nothing special about it... but it may give some newer php'ers and mssql'ers a good starting point in making exactally what they want.

    My main reason for posting this here is hoping the search engine's bots (google, yahoo, ect) will find this post ... that way people can hopefully learn a little from it.


    Cya around!


    Luke

  2. #2
    Join Date
    Jul 2007
    Posts
    357
    More pagination (The term of doing "page 1 2 3) tutorials:
    http://www.phpfreaks.com/tutorials/43/0.php
    http://php.about.com/od/phpwithmysql...pagination.htm
    http://www.webpronews.com/expertarti...ion-with-mysql
    http://www.bradyvercher.com/php-pagination-class.html

    There are ton of them out there. I'm in favor of the ones that have << <1 2 3 4.... 100 > >> --- ones that shift like PHPBB and such..

    And for articles - I like the drop down menu, next page previous link, Pages 1 2 3 ... 10, and a list of page titles below..

  3. #3
    Join Date
    Feb 2013
    Posts
    1

    almost there!

    Hi Luke2kz71

    Thanks so much for this tutorial on MSSQL pagination.. I almost have it working but I am stuck at the last hurdle please help! : )

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles