Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    Penang, Malaysia
    Posts
    9

    Unanswered: Paging with PHP using MSSQL database

    Anyone with the code to do paging using MSSQL database. There are a lot of examples using LIMIT clause in MySQL but MSSQL do not support it. I need some sample code which using paging with sample as below:


    Page 10 of 11 [ 1 2 3 4 5 6 7 8 9 10 Next >> ]
    ------------------------------------------------
    ...
    ...
    ... data
    ...

    ------------------------------------------------
    [ Home | Previous | Next | End ]

  2. #2
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    If MSSQL doesn't support the LIMIT clause, the only solution i see is fetching and discarding a number of rows equal to number of page * rows per page, then fetching the correct rows and showing them.

    In other words, you should fetch and discard all the rows which belong to pages before the one you want to display.

    By the way, are you sure no LIMIT clause is supported in MSSQL? It does sound strange to me...
    The only failure is not trying to do it.

  3. #3
    Join Date
    Feb 2004
    Posts
    29
    yinsw, you can use
    Code:
     SELECT TOP 5 * FROM Users
    this query, this will select top 5 rows from users table .

  4. #4
    Join Date
    Feb 2004
    Posts
    29
    yinsw, I have not worked with php vs ms sql database, however I will give you idea for paging without limit usage.

  5. #5
    Join Date
    Feb 2004
    Posts
    29
    function skip()
    {
    global $result; //stores records
    global $skip; //records to skip
    //echo $skip;

    // this loop will skip the records according to pages
    for($a=1;$a<=$skip;$a++)
    $disp=mysql_fetch_array($result);

    //this loop prints the records
    for($i=1;$i<=5;$i++){
    echo "<tr>";
    $disp=mysql_fetch_array($result);
    ?>
    <td width="10%"><?echo $disp['id'];?></td>
    <td width="10%"><?echo $disp['name'];?></td>
    </tr>
    <?
    } //end of loop
    } //end of function



    $show=5;
    $skip=$_GET['skip'];
    echo $skip;
    if(empty($skip))$skip=0;
    switch($skip)
    {

    case 0:
    {
    $skip=0;
    break;
    }
    case 1:
    {
    $skip=5;
    break;
    }
    case 2:
    {
    $skip=10;
    brea;
    }
    case 3:
    {
    $skip=15;
    break;
    }
    case 4:
    {
    $skip=20;
    break;
    }
    }


    $sql="select * from songs"; //song table has two field "id" and "name"
    $result=mysql_query($sql);
    $total=mysql_num_rows($result); //total records
    $displayonpage=5; //records on per page
    $pages=ceil($total/$displayonpage); //total pages
    // echo $pages;
    ?>

    <!-- here goes table headings-->
    <table width="50%" bgcolor="ffddff" border="1" cellpadding="0" align="center">
    <tr><td width="10%"><th>id</th></td><td width="10%"><th align="center">name</th</td></tr></table>
    <!--table for data display-->
    <table width="50%" bgcolor="00ddff" border="1" cellpadding="0" align="center">
    <?
    skip(); //calling skip funciton to display records
    ?>
    </table>
    <?

    //displays First 1 2 3 Last pages

    for($a=0;$a<$pages;$a++)
    {
    $i=$a+1;
    //here is value is passed to skip variable to skip records, you can change it as you want.
    if($a==0) echo "<a href=$PHP_SELF?skip=$a>First</a>\t";
    if((($a+1)!==$pages)&&($a>0))echo "<a href=$PHP_SELF?skip=$a>$i</a>\t";
    if(($a+1)==$pages) echo"<a href=$PHP_SELF?skip=$a>Last</a>\t";
    }
    ?>

  6. #6
    Join Date
    Oct 2003
    Posts
    11
    // this loop will skip the records according to pages
    for($a=1;$a<=$skip;$a++)
    $disp=mysql_fetch_array($result);


    this is not right
    if you have millions of records you will get all of them from server in order to display just 10 or 20

  7. #7
    Join Date
    Feb 2004
    Posts
    29
    poppy_123 wrote
    I will give you idea for paging without limit usage.
    You can manage it according to your requirement.

  8. #8
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Quote Originally Posted by rovshan
    // this loop will skip the records according to pages
    for($a=1;$a<=$skip;$a++)
    $disp=mysql_fetch_array($result);


    this is not right
    if you have millions of records you will get all of them from server in order to display just 10 or 20
    So what's the solution you propose?
    The only failure is not trying to do it.

  9. #9
    Join Date
    Jul 2004
    Posts
    1

    This kinda works...

    you can use something like this in MSSql...

    select top 10 colName from table1 where colName not in (select top 40 colName from table1 order by colName) order by colName

    hope this helps.

    MySQL doesnt let you use the TOP statement in a nested statement, but MSSql does

  10. #10
    Join Date
    Feb 2007
    Posts
    1
    <?

    #5 Report Bad Post
    Old 06-16-04, 07:49
    poppy_123 poppy_123 is offline
    Registered User

    Join Date: Feb 2004
    Posts: 25
    Send a message via Yahoo to poppy_123
    function skip()
    {
    global $result; //stores records
    global $skip; //records to skip
    //echo $skip;

    // this loop will skip the records according to pages
    for($a=1;$a<=$skip;$a++)
    $disp=mysql_fetch_array($result);

    //this loop prints the records
    for($i=1;$i<=5;$i++){
    echo "<tr>";
    $disp=mysql_fetch_array($result);
    ?>
    <td width="10%"><?echo $disp['id'];?></td>
    <td width="10%"><?echo $disp['name'];?></td>
    </tr>
    <?
    } //end of loop
    } //end of function



    $show=5;
    $skip=$_GET['skip'];
    echo $skip;
    if(empty($skip))$skip=0;
    switch($skip)
    {

    case 0:
    {
    $skip=0;
    break;
    }
    case 1:
    {
    $skip=5;
    break;
    }
    case 2:
    {
    $skip=10;
    brea;
    }
    case 3:
    {
    $skip=15;
    break;
    }
    case 4:
    {
    $skip=20;
    break;
    }
    }


    $sql="select * from songs"; //song table has two field "id" and "name"
    $result=mysql_query($sql);
    $total=mysql_num_rows($result); //total records
    $displayonpage=5; //records on per page
    $pages=ceil($total/$displayonpage); //total pages
    // echo $pages;
    ?>

    <!-- here goes table headings-->
    <table width="50%" bgcolor="ffddff" border="1" cellpadding="0" align="center">
    <tr><td width="10%"><th>id</th></td><td width="10%"><th align="center">name</th</td></tr></table>
    <!--table for data display-->
    <table width="50%" bgcolor="00ddff" border="1" cellpadding="0" align="center">
    <?
    skip(); //calling skip funciton to display records
    ?>
    </table>
    <?

    //displays First 1 2 3 Last pages

    for($a=0;$a<$pages;$a++)
    {
    $i=$a+1;
    //here is value is passed to skip variable to skip records, you can change it as you want.
    if($a==0) echo "<a href=$PHP_SELF?skip=$a>First</a>\t";
    if((($a+1)!==$pages)&&($a>0))echo "<a href=$PHP_SELF?skip=$a>$i</a>\t";
    if(($a+1)==$pages) echo"<a href=$PHP_SELF?skip=$a>Last</a>\t";
    }
    ?>

    I found out this script is useful and i go indept and i learn that the switch statement is kind of tedious or limited if you have thousand of database.
    So, i find the another solution for that
    change the place of (swithc($skipt)){} to just one line like
    $skip = $skip * $show;

    hope this one will help u guys..

    reagard
    aka

  11. #11
    Join Date
    Apr 2007
    Posts
    6

    Talking need help on paging by php & mssql

    helllo..

    can any one help me in paging through php & mssql coz i m having probz with LIMIT functions...can any one help me with accurate coding ...

    thanking you

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The following SQL does this :
    Inner SQL : Takes top 200 rows from your table. Orders them by column1 in descending order (so you have 200->1).
    Outer SQL : Takes top 10 OF your top 200 rows (which have been ordered in reverse), and thus gives you 200->190, which you then reorder to give you 190->200.

    Code:
    SELECT TOP 10 column1
      (SELECT TOP 200
       column1 
       FROM <tablename>
       ORDER BY column1 DESC)
    ORDER BY column1 ASC

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Do NOT do this work in PHP else your site will die a death...
    Also, please use the "[ CODE ]" tags for writing PHP or SQL code as it will space things nicely for you.

Posting Permissions

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