Pagination with PHP / MsSQl not MySQL!
Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Pagination with PHP / MsSQl not MySQL!

  1. #1
    Member
    Join Date
    Jun 2003
    Posts
    34

    Pagination with PHP / MsSQl not MySQL!

    I have searched through the forums and various other sites for tutorials and info for pagination with PHP. All of them deal exclusively with PHP / MySQL...

    That doesn't help me here. I have a PHP based site with some MySQL stuff on it, but there is also some info that must be searched that lives in a big ole Microsoft SQL server database... ah the perials of developing outside of a fishbowl.

    The nice thing with MySQL is that you have the nice "LIMIT" command that can go into a SELECT statement... we all know that the LIMIT accepts two parameters with one being the offset and the second being the actual number of rows returned...

    MsSQL has no such function.

    Does anyone have any info on how to paginate MsSQL results? I have seen that there is a function in PHP named "mssql_data_seek" but I dont know to apply it here...

    Any tutorials, code examples, etc out there to help me? Any help is most appreciated!

    editorial:
    I really was surpirsed to find out that such a simple task is not so simple in what is supposed to be such a 'cough cough crap' superior database. give me opensource any day.

    Sorry to sound bitter, but dang this project is just giving me nightmares.

  2. #2
    Member
    Join Date
    May 2002
    Location
    Hamburg, Germany
    Posts
    44
    Hi,

    just fire the query, fetch the number of offset-records without displaying them, then process the ones to display and then free the queryhandle.
    That's the way I'm doing it with firebird which has no LIMIT clause, too. Works fine.
    Best regards,


    King Louie

  3. #3
    Member
    Join Date
    Jun 2003
    Posts
    34
    kinglouie... thanks for your input... are you saying I need to fetch all the preceeding rows (i.e. 1-10) in addition to the desired ones, if I really only want to display rows 11-20?

    just trying to clarify here. thanks.

  4. #4
    Senior Member
    Join Date
    Jun 2002
    Posts
    140
    Sybase ASE uses many of the same T-SQL extensions that MS SQL does, so this page may be useful:
    http://www.isug.com/Sybase_FAQ/ASE/s....2.html#6.2.12

  5. #5
    Member
    Join Date
    May 2002
    Location
    Hamburg, Germany
    Posts
    44
    I'm afraid you need to fetch the preceding records... i know no other way to make the db-cursor moving forward or skipping directly the record no ##.
    Best regards,

    King Louie

  6. #6
    Senior Member
    Join Date
    Jun 2002
    Posts
    140
    I authored the stored procedure sp_get_posts and it works really, really fast.

    Another method, if you don't want to use a stored procedure, is to do something like:
    SELECT LIMIT start_at, number

    Code:
    SELECT TOP number
      FROM table
     WHERE id NOT IN ( SELECT TOP start_at
                         FROM table
                        ORDER BY id ASC )
     ORDER BY id ASC
    There are other ways, experiment with TOP n if you want.

  7. #7
    Junior Member
    Join Date
    Feb 2003
    Posts
    11
    MATTR,

    I was wondering... Can you copy and paste that stored proceedure? If you still have it? or do you have a function for this paging (MSSQL)??

    Seems that SO many people are looking for this paging with MSSQL and MS is doing nothing about it.

    I could really use seeing an example...

    Thanks much for your time!!!

  8. #8
    Junior Member
    Join Date
    Feb 2003
    Posts
    11
    Curious though.. this seems to be something that a lot of people are looking for.. why isnt there solutions posted throughout the web? This is not the first post I have made about this.. but seems many choose to ignore this issue..

    Please help

  9. #9
    Super Geek bretticus's Avatar
    Join Date
    Apr 2002
    Location
    UT USA
    Posts
    1,576
    I wrote this long ago (rather wishing that MsSQL implemented LIMIT.) This was my fix. Seems like it was based off an example on the Net somewhere (however, it has been like 3 years! Can't remember how much of it was my ingenuity.) The downside was that it didn't seem to lend itself well to displaying order records (in this case) ordered by time descending. Then again, the presentation tier was done in VBstupid ;-)

    CREATE PROCEDURE sProc_Get_Orders

    (
    @limit [int],
    @total [varchar](50)
    )
    AS
    DECLARE
    @SQL nvarchar(1000)
    set @SQL = 'SELECT idx, b_firstname, b_lastname, modified, created,funds_pending FROM orders WHERE idx IN (SELECT TOP ' + @total + ' idx FROM orders WHERE (deleted IS NULL) ORDER BY idx DESC) ORDER BY idx'

    --PRINT @SQL
    SET ROWCOUNT @limit
    EXEC (@SQL)
    GO
    Last edited by bretticus; 10-19-2005 at 02:14 AM.

  10. #10
    Super Geek bretticus's Avatar
    Join Date
    Apr 2002
    Location
    UT USA
    Posts
    1,576
    Man, this is the first time I ever posted ASP on a PHP forum (hope this isn't a party foul!)

    I thought it would be helpful to show you how I implemented this stored procedure. You can translate to PHP (if not I'm sure I can.)

    Dim arrRows, i
    Dim strPage, intPages, strRecTotal, strLimit
    Dim strStatus, strFundsPending

    Response.Expires = 0

    'Return only 10 rows per page
    strLimit = 10

    strFundsPending = "1"
    If Trim(Request.QueryString("type")) <> Empty AND IsNumeric(Request.QueryString("type")) Then
    strFundsPending = Request.QueryString("type")
    End If

    sql = "SELECT COUNT(*) FROM orders WHERE deleted IS NULL"
    ' WHERE funds_pending = " & strFundsPending
    Set rs = conn.execute(sql)
    If not rs.EOF Then
    strRecTotal = rs(0)
    End If
    Set rs = nothing

    'Response.write "<BR>QUERY COUNT:" & strRecTotal

    intPages = strRecTotal\strLimit
    strPage = Server.HTMLEncode(Request.QueryString("pg"))
    If strPage <> Empty Then
    strPage = cInt(strPage)
    strRecTotal = strRecTotal - (strLimit * strPage)
    If strRecTotal < 0 Then strRecTotal = 0
    Else
    strPage = 0
    End If

    sql = "sProc_Get_Orders " & strLimit & ", " & strRecTotal & ", " & strFundsPending
    P.S. Pay no attention to the funds pending parts.

  11. #11
    Nevermind ...
    Join Date
    Sep 2005
    Location
    the Netherlands
    Posts
    25
    Quote Originally Posted by Stripe-man
    Curious though.. this seems to be something that a lot of people are looking for.. why isnt there solutions posted throughout the web? This is not the first post I have made about this.. but seems many choose to ignore this issue.. :(
    True, I have the same problem with MS Access. Seems nobody really knows how to do this. Bummer ! (:

  12. #12
    Super Geek bretticus's Avatar
    Join Date
    Apr 2002
    Location
    UT USA
    Posts
    1,576
    True, I have the same problem with MS Access. Seems nobody really knows how to do this. Bummer ! (:
    Yeah, a stored procedure isn't very compatible with Access (then again, you should use SQLite instead.) However, the stored procedure example is a fast solution as ignored records are not returned to the client. I remember I looked and looked, so my response may be the "answer" afterall. This is just a case of providing some extra data-layer logic as I'll assume you can call stored procedures in PHP. I mean you specify a start and length with MySQL LIMIT also.

  13. #13
    Junior Member
    Join Date
    Feb 2003
    Posts
    11

    resolved

    But in looking at everyone’s examples.. I now have an understanding in how this has to work (least without the stored procedure) I will also discuss this stored procedure with our DB Guru...

    BUT... This is what I have and understand...

    THIS WORKS!!!!

    Code:
    SELECT TOP 10 * FROM main
    	WHERE published = 1 and id NOT IN ( SELECT TOP 0 id
                         FROM main  ORDER BY id ASC )
     ORDER BY id ASC
    Explanation:
    Code:
    SELECT TOP 10 * FROM main
    The first part of this script will specify how many records to return This will always remain the same (course you can offer to the user how many records per page he wants with this.. Just substitute the "10" with a $var.

    Code:
    and id NOT IN ( SELECT TOP 0 id
    This means its making sure that the records fetched in the first select are NOT selected in the second select. so .. with specifying '0' or zero.. this could be your first page. The '0' or zero would have to be replaced with a $var to specify how many records you want to skip.

    So.. if I change the '0' to 10 for example.. Then the query would look like this..

    Code:
    SELECT TOP 10 * FROM main
    	WHERE published = 1 and id NOT IN ( SELECT TOP 10 id
                         FROM main  ORDER BY id ASC )
     ORDER BY id ASC
    This means select the first 10 records that are NOT in the first 10 records so it would then select the next 10 records. If I change it to 20 then it would then skip the first 20 records and so on...
    IE:
    Code:
    SELECT TOP 10 * FROM main
    	WHERE published = 1 and id NOT IN ( SELECT TOP 20 id
                         FROM main  ORDER BY id ASC )
     ORDER BY id ASC
    of course the sorting need to be identical.

    Example with $var:
    $records_per_page = 10; //can use $_GET from URL
    $next_page = $next_id; //can use $_GET from URL

    Code:
    SELECT TOP $records_per_page * FROM main
    	WHERE published = 1 and id NOT IN ( SELECT TOP $next_page id
                         FROM main  ORDER BY id ASC )
     ORDER BY id ASC
    Does this help anyone?
    I may build a function for this...
    Last edited by Stripe-man; 10-20-2005 at 07:21 AM.

  14. #14
    Junior Member
    Join Date
    Feb 2003
    Posts
    11
    Thanks to all who put into this... also posted here:
    http://www.brotherstrust.com/forums/...hp?p=2915#2915

  15. #15
    Super Geek bretticus's Avatar
    Join Date
    Apr 2002
    Location
    UT USA
    Posts
    1,576
    Wow, like I said I implemented my stored procedure approach a few years back. I'm not sure with the NOT IN and the ASC order make the difference (I'll have to read your post again better or digest for myself), however this way works much better as exactly the right ecords are returned.

    My way simply used SET ROWCOUNT @limit to make sure only @limit records are returned to the client cursor. It was efficient in that respect but not like this one on the server end of things (and this way is much less confusing to use.)

    Thanks for some enlightenment mattr and Stripe-man!

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
  •