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

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 @limit
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]

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 (http://phpbuilder.com/board/showthread.php?t=10310267) 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 @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!

[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 ( @limit 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 (http://www.4guysfromrolla.com/webtech/111499-1.shtml)

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

[ionescu.raul]

For all ones interested in PHP MSSQL pagination see one of the following links:
PHP Classes (http://ionescuraul.users.phpclasses.org/browse/package/2832.html)
SourceForge (http://sourceforge.net/projects/rc4php)
Tigris (http://rc4php.tigris.org/)
PHP Builder (http://www.phpbuilder.com/snippet/detail.php?type=snippet&id=1322) (as a ZIP archive)
Hot Scripts (http://www.hotscripts.com/Detailed/56264.html)
CodeWalkers (http://codewalkers.com/seecode/603.html)
Planet Source Code (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1908&lngWId=8)

[Roger Ramjet]

Hats-off, Stripe-man, hats-off. A very pretty solution. :cool:

This has always been a problem in ms-land, and it's nice to know how to do it should the need arise. I suppose the charitable answer as to why M$ insist on sticking with TOP instead of implementing LIMIT is because we mostly use their products for client/server and so send the whole resultset to the client for pagination there anyway.

[Stripe-man]

Raul!!! you found my post!!!! Roger Ramjet I must tell you.. Raul solution is the pick of all time.. I have already begun to intergrate it.. and Raul has already is also currently updating his script to make it even more flexible. Its a true enterprise level class. YOu must see it and try it.

[Roger Ramjet]

Can't seem to download this from phpbuilder, all I get is a file called download.php, and I'm just too fed up with having to register everywhere for anything to bother registering at the other site. I've had pagination nailed down for years, for my needs, but was interested to see how someone else tackles it. It was your slick query that I liked, interesting way around mickeysofts dumb sql.

[ionescu.raul]

Can't seem to download this from phpbuilder, all I get is a file called download.php.....
Well if you download the file from PHPbuilder and rename it with a zip extension, you should be able to extract it.
:)

[Roger Ramjet]

Ah, thanks Raul, I'll have a look some time.

But why don't they say it's a zip file on the page then??

[ionescu.raul]

But why don't they say it's a zip file on the page then??
Well, because I can't figure out how to change project description.... :o
...and you are welcome!!! :)

Anyway, I’ll wait for your comments!

[ionescu.raul]

Raul!!! you found my post!!!! Roger Ramjet I must tell you.. Raul solution is the pick of all time.. I have already begun to intergrate it.. and Raul has already is also currently updating his script to make it even more flexible. Its a true enterprise level class. YOu must see it and try it.

Thanks!!! :)

[vierco]

Hola, vi y en otras webs tb note que tienen gran problema con paginar resultados con sqlserver, me paso, hace unos años cuando hice una web para un servidor de Muonline tenia que paginar su ranking.. asi que aca les dejo el codigo
es algo obsoleto, porque bueno era un newbie cuando lo hice pero esper le sirva a alguien y pueda mejorarlo...al menos funciona con ir "Anterior | Siguiente" ^^

saludos


<?
$por_pagina="5";
$query = 'select Name,Class,cLevel,Resets from Character WHERE CtlCode=0 order by cLevel desc';
$result = mssql_query( $query );
echo '<table width="402" border="0" align="center" cellpadding="1" cellspacing="1">
<tr>
<td width="37" style="border:#999999 solid 1px;"><div align="center">Num</div></td>
<td width="148" style="border:#999999 solid 1px;"><div align="center">Nombre</div></td>
<td width="86" style="border:#999999 solid 1px;"><div align="center">Raza</div></td>
<td width="65" style="border:#999999 solid 1px;"><div align="center">Nivel</div></td>
<td width="50" style="border:#999999 solid 1px;"><div align="center">Resets</div></td>
</tr>';
$total = mssql_num_rows($result);
if(!$HTTP_GET_VARS['pag']){
$num_pag = 1;
$begin = 0;
$fin = $por_pagina;
}else{
if(is_numeric($_GET['pag'])){
$num_pag = $_GET['pag'];
$begin = $_GET['pag']*$por_pagina+1;
$fin = $begin + $por_pagina;
}else{
$num_pag = 1;
$begin = 0;
$fin = $por_pagina;
}
}
$aa = 0;
while($row = mssql_fetch_row($result)){
$aa++;
if($aa >= $begin && $aa <= $fin){
$cuenta = $row['5'];
echo' <tr>
<td style="border:#999999 solid 1px;">'.$aa.'</td>
<td style="border:#999999 solid 1px;">'.$row[0].'</td>
<td style="border:#999999 solid 1px;">'.$row[1].'</td>
<td style="border:#999999 solid 1px;">'.$row[2].'</td>
<td style="border:#999999 solid 1px;">'.$row[3].'</td>
</tr>';
}
}
echo '</table>';
$pagina=$_GET['pag'];
$new=$pagina+1;
$menos=$pagina-1;
if($pagina == 0) {
echo "<center><a href=?pag=$new>Siguiente ></a></center>";
}
elseif($pagina > 0){
echo "<center><a href=?pag=$menos>< Anterior</a> | <a href=?pag=$new>Siguiente ></a></center>";
}
?>