Pagination with PHP / MsSQl not MySQL!

chadu

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. :mad:

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. :rolleyes: give me opensource any day. 🆒

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

kinglouie

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

chadu

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.

mattr

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/section6.2.html#6.2.12

kinglouie

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

mattr

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

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.

Stripe-man

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!!!

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.. 🙁

Please help

bretticus

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 @
EXEC (@SQL)
GO

bretticus

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.

interface

Stripe-man wrote:
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 ! (:

bretticus

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.

Stripe-man

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!!!!

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:

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.

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..

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:

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

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...

Stripe-man

Thanks to all who put into this... also posted here:
http://www.brotherstrust.com/forums/viewtopic.php?p=2915#2915

bretticus

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 @ to make sure only @ 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!

Stripe-man

Thanks Bretticus... I am at work now .. and i hope I have time today to discuss this stored proceedure with our db guru... Thanks much for your time and input on this.. Great stuff.. its nice to know that someone out tehre still is willing to help!

and I will play with the sorting.. currently my brain is telling me that I must specify the sort on both queries, mostly because (i think mssql sorts asc by default.. but what if i want to sort DESC ? So i think i need it in there.. the sorting capabilities I am giving my users are very entailed. 10 or 12 columns with each one having the asc and desc capability/ not to mention expanding or filtering the results by, country, date, article groups and other stuff... you should see my query and the php behind it ! LOL

bretticus

Hey, thanks again, I tried ORDER BY idx DESC on my query and sub query. Works great.

Here's my current stored procedure...

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

Stripe-man

Thanks for posting bretticus... but is this MSsql... or for another db type?

I am getting syntax errors while checking the syntax... 🙁

bretticus

Are you familiar with with stored procedures on Microsoft SQL servers? More Info Here

Stripe-man

wow.. good find Bretticus! LOL .. I will read up on that tomorrow...THans for the link.. I have already consolidated the content (all pages) and converted it to a doc file!

I am working on a function for the paging (client side) as well.