Paginal conclusion from tables MSSQL with help PHP

At many forums on PHP saw the same question « How to draw a paginal conclusion from MSSQL? ». As the answer very much « All often serves very simply, use limit and all works! », but, those who gives such answer, probably never saw MSSQL close enough and do not know, that much to our regret, there the given function, as against MySQL, no. I in the practice of a spelling of scripts on php as basically used sheaf PHP + MySQL and questions did not arise. But it was necessary to proceed{pass} to a hosting with established MSSQL. After long searches of a script which would allow to draw a paginal conclusion from tables MSSQL about help PHP, without use of cursors and ADO, and not najdja anything, it was necessary to write function which just and is engaged in discussed routine business.

The search has been put in a basis of a paginal conclusion:

select top nuzhnoe_kolichestvo_dlja_vyvoda *

from (select top kolichestvo_zapisej_iz_kotorykh_delaetsja_vyvod *

      from the table

      order by tablica_id) as t1

           order by tablica_id desc

(tablica_id - a field of your table on which you will carry out sorting)

For job of the function necessary to us and correct job of the given search it is necessary to know some sizes:

1) The necessary quantity{amount} of lines for a conclusion

2) The quantity{amount} of recordings from which makes a choice

3) On what pages we are

4) How much all pages.

The turned out function looks so:

<?

if (! $ _GET ["page"]) {* if the variable page with number{room} still no, we create her 

            $page=1; * also it is appropriated{given} to her value 1

} else {$page = $ _ GET ["page"];} * differently, we appropriate{give} to her value of the pressed page

* We declare function with the name numberpage, having the following parameters:

* 1) quantity{amount} of deduced{removed} lines on each page,

* 2) the name of the table which it is deduced{removed} page by page,

* 3) additional parameters of a choice of search, for example: where pole_kakoeto1=chemuto

* and pole_kakoeto2=tomuto.

* Do not forget, that the given additional parameters should be and in the search, differently{otherwise}

* You receive incorrect values.

function numberpage ($len, $tablename, $dopquerysql) {

global $page;

            global $nump;

* We form a file for return of values of function

            $nump=array ("page" => "", numers " => "", len " => "0", "all" => "0", "top1" => "0", "top2" => "0");  

            * We receive URL pages from which have called function

$namepage = $ PATH_INFO;

* We calculate quantity{amount} of lines in search

            $nu = mssql_query (" select count (1) as ss from $tablename $dopquerysql "); $nrow = mssql_fetch_array ($nu);                             

            $all = $ nrow [' ss '];           

            $pages=ceil ($all / $ len)-1;

            if ($page == ($pages+1)) {$top1 = $ all-(($page-1) * $len); $top2 = $ all;

} else {$top1 = $ len; $top2 = $ page * $ len;}

            if ($len> = $all) {$top1 = $ all; $top2 = $ all;}

            $numers = ($ page-1) * $len;

$q = $ all / $ len;

* We prepare for a file with numbers{rooms} of pages

if ($q> 1) {

               for ($p=0; $p <= $pages; $p ++) {

                         $pag = $ p+1;

$nump [page] = " $nump [page] [<a href = ' $namepage? page = $ pag ". $dopnum. " '> $ pag </a>] ";

}

}

* We prepare a file with end results and it is deduced{removed} it

$nump ["page"] = $nump [page];

$nump ["numers"] = $numers;

$nump ["len"] = $len;

$nump ["all"] = $all;

$nump ["top1"] = $top1;

$nump ["top2"] = $top2;

return $nump;

}

?>

The given function can be arranged in a file with your configurations of all your project and to cause her  on each pages where you are going to to draw a paginal conclusion. For example: the file of a configuration at you is called myconfig.php, means, on each page is inserted <? require ("myconfig.php");?>

Now we pass directly to a conclusion of our table in a browser. At once we are determined, how much at us will be it is displayed lines of the table, or we do{make} the form for a choice of quantity{amount} of lines with the user. I shall not result the form here as it is beyond clause{article} and it is easy enough, that on it to stop.

Before a conclusion of the table we cause function (we shall deduce{remove} till 10 lines on page from the table nasha_tablica where pole2 contains word KAKOETOZNACHENIE and pole5 it is equal 12):

<? numberpage ("10", "nasha_tablica", " where pole2 like ' %KAKOETOZNACHENIE % ' and pole5=12 ");?>

Remember, that if you do not have any additional parameters of a choice, simply leave value empty, and you receive a choice from all fields of the table. For example: <? numberpage ("10", "nasha_tablica", " ");?>, accordingly having cleaned{removed} the given condition and at a conclusion of the table.

Then, in the place necessary to us it is deduced{removed} the table:

<?

$grn = mssql_query (" select * from (select top $nump [top1] * from (select top $nump [top2] * from nasha_tablica where pole2 like ' %KAKOETOZNACHENIE % ' and pole5=12 order by tablica_ID desc) as t1 order by tablica_ID) as t2 order by tablica_ID desc ", $link);

for ($gd=0; $gd <@mssql_num_rows ($grn); $gd ++) {   

$gr = mssql_fetch_array ($grn);

?>

Then we deduce{remove} transfer of pages: <? echo "<p.>" $nump [page.] "</p>";?>

In the given example, at you will be it is deduced recordings in return sequence, since last in the table on 10 to the first. If it is necessary to you, that recordings were deduced{removed} with the first and further the script should be modified:

select * from (select top $nump [top1] * from (select top $nump [top2] * from nasha_tablica order by tablica_ID) as t1 order by tablica_ID desc) as t2 order by tablica_ID

Basically and all.