PHP + MSSQL + PAGINATION + EFFICIENCY = IMPOSSIBLE?

[shamilton]

Hello all!

 

I am wondering if anyone has a decent method for pagination with PHP and MSSQL?

 

In MySQL the LIMIT clause takes care of the hard part.

But there is not such thing in MSSQL.

 

Here is my current method:

 

<?php

/* Get the page number or set default value. */
$page = (isset($_GET["page"])) ? 
            (is_numeric($_GET["page"]) && $_GET["page"] > 0) ? $_GET["page"] : 1 : 1;

/* Define the number of results per page. */
$max_results = 10;

/* Figure out the limit for the query based on the current page number. */
$from = (($page * $max_results) - $max_results);

/* Set allowances for sorting the data. */
$allowed = array('ID','Name','Address','City','State','Zip','Phone');
$sortby = (isset($_GET["sortby"])) ? (in_array($_GET["sortby"],$allowed)) ? $_GET["sortby"] : 'ID' : 'ID';

/* Depth into table. */
$depth = $page * $max_results;

$sort = (isset($_GET["sort"])) ? $_GET["sort"] : "ASC" ;
$altsort = ($sort == "ASC") ? "DESC" : "ASC" ;

/* See if a search was performed. */
$SearchString = ( isset ( $_REQUEST["searchString"] ) || isset ( $_POST["searchString"] ) ) ? $_REQUEST["searchString"] : NULL;
$SqlQuery = "SELECT * FROM (SELECT TOP $max_results * FROM (SELECT TOP $depth * FROM Customer WHERE NAME LIKE '%$SearchString%' ORDER BY $sortby $sort) AS tempTable1 ORDER BY $sortby $altsort) AS tempTable2 ORDER BY $sortby $sort";

/* Build and run the sql query. */
$SQL_query = ( $SearchString != NULL ) ? $SqlQuery : "SELECT * FROM (SELECT TOP $max_results * FROM (SELECT TOP $depth * FROM Customer ORDER BY $sortby $sort) AS tempTable1 ORDER BY $sortby $altsort) AS tempTable2 ORDER BY $sortby $sort";

$RecordSet = $DBConnection->Execute($SQL_query);

/* Figure out the total number of rows in table. */
$SQL_query = ( $SearchString != NULL ) ? "SELECT * FROM Customer WHERE NAME LIKE '%$SearchString%' ORDER By Name" : "SELECT * FROM Customer";
$RecordSetCount = $DBConnection->Execute($SQL_query);
for($total_results=0; $RecordSetCount->EOF != 1; $total_results++) {
$RecordSetCount->MoveNext();
} // End For

/* Figure out the total number of pages we will have. */
$total_pages = ceil($total_results / $max_results);

/* Figure out the current page result numbers. */
$fr = $from + 1;
$to = $from + $max_results;

?>

 

You can easily imagine how the whole thing is done from there...

 

Problem with this method...is I have literally 50,000 records in one table...and that is my test database...the clients in reality have much larger databases.

 

You'll notice that logically...the further you go into the pagination (scrolling through the pages) - the bigger the burden.

 

So anyone got a solution - or am I stuck with this?

 

It works fine for me right now...load time takes a few seconds...and it doesn't really bog down...but I am not trying to do this from out of the country...

[Barand]

I know, it's a real pain with M$SQL.

 

AFAIK you have to do something like this to get, say, the third page where 20 recs to a page

 

SELECT TOP 20 * FROM tablename
WHERE id NOT IN 
(SELECT TOP 40 id FROM tablename)

[shamilton]

Yeah...that's more or less what I am doing...

Unfortunatly that method means that if you goto the 200th page...you end up getting 200 x 20 and then selecting the top of that...and so forth.

So if you go to the last page...you get ALL rows...then grab the top of that stack...which is lame...

[Barand]

I guess the lame-brains at Richedmond never considered people might want to page results. You get what you pay for

[shamilton]

There's something out there called rc4php that may be the answer...but I couldn't get it to work.

I could get it to list all my databases and tables - but when it came to fields it just crapped out on me.

Which of course...doesn't help at all!