Jump to content

Paging with MSSQL and PHP5


Stripe-man

Recommended Posts

Thanks for the reply Munchen...

 

well.. um.. pagination ? IE:

 

Page |< << 1 >>[2][3][4][5][6] |>

 

Something like that

There are so many MySQL scripts.. but MSsql does NOT support the Limit condition.

 

I think TOP has to be used .. but im not sur ehow.. and I was hoping that someone had a script a function or something that I could use or at least get some idears from!!!

 

Thanks again for your reply!

Link to comment
Share on other sites

Ahh, so true, MS SQL does not have such a funtion. I made it once (PHP wise) and I will try to find it for You :)

 

 

Thanks for the reply Munchen...

 

well.. um.. pagination ?  IE:

 

Page |< << 1 >>[2][3][4][5][6] |>

 

Something like that

There are so many MySQL scripts.. but MSsql does NOT support the Limit condition. 

 

I think TOP has to be used .. but im not sur ehow.. and I was hoping that someone had a script a function or something that I could use or at least get some idears from!!!

 

Thanks again for your reply!

307852[/snapback]

 

Link to comment
Share on other sites

Hey there Munchen...

 

I have this... if you'd like to see it..

 

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

 

 

 

 

http://www.phpbuilder.com/board/showthread...64#post10668964

 

But I still havnt got the php done up yet.. i meean if you still have that and can find it! Id like it very much to see it!

Link to comment
Share on other sites

Ok, here is something i just made ... It does the trick ...

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

$sqlserver [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\".\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$sqluser [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"test\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$sqlpass [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"test\"[/span][span style=\"color:#007700\"];

 

[/span][span style=\"color:#0000BB\"]$connection [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mssql_connect[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sqlserver [/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$sqluser [/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$sqlpass [/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]$max_on_page [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]5[/span][span style=\"color:#007700\"];

 

function [/span][span style=\"color:#0000BB\"]myLimit[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#DD0000\"]\"\"[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$mycount[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#DD0000\"]\"\"[/span][span style=\"color:#007700\"]){

                global [/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$max_on_page[/span][span style=\"color:#007700\"];

                if(!empty([/span][span style=\"color:#0000BB\"]$mycount[/span][span style=\"color:#007700\"])){

                        [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT COUNT(*) FROM news\"[/span][span style=\"color:#007700\"];

                        [/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mssql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]mssql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]));

                        [/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]];

                }

                else{

                        [/span][span style=\"color:#FF8000\"]// The pagination.

                        [/span][span style=\"color:#007700\"]if([/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"]>[/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"])

                        {

                            [/span][span style=\"color:#0000BB\"]$cond[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#DD0000\"]\"max\"[/span][span style=\"color:#007700\"];

                            [/span][span style=\"color:#0000BB\"]$internal [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"];

                        }

                        else

                        {

                            [/span][span style=\"color:#0000BB\"]$cond[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#DD0000\"]\"min\"[/span][span style=\"color:#007700\"];

                            [/span][span style=\"color:#0000BB\"]$internal [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"]*[/span][span style=\"color:#0000BB\"]$max_on_page[/span][span style=\"color:#007700\"];

                        }

 

                        [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"select $cond (t.id) from (select top $internal id from news ORDER BY id) t\"[/span][span style=\"color:#007700\"];

                        [/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mssql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]);

                        [/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mssql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]);

                        [/span][span style=\"color:#0000BB\"]$maxID [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]]; [/span][span style=\"color:#FF8000\"]// max id

                        [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT TOP \" [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]$max_on_page [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#DD0000\"]\" * FROM news WHERE id > \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$maxID[/span][span style=\"color:#007700\"].[/span][span style=\"color:#DD0000\"]\"\'  ORDER BY id\"[/span][span style=\"color:#007700\"];

                        [/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mssql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]);

                }

                return [/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"];

}

 

[/span][span style=\"color:#0000BB\"]$posts [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]myLimit[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'1\'[/span][span style=\"color:#007700\"]);

 

[/span][span style=\"color:#0000BB\"]mssql_close[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]?>[/span]

 

  <table width=\"400\" border=\"0\">

   <tr>

    <td width=\"400\" valign=\"top\">

     <h1><font style=\"color:#000000\"><strong> Nyheder & Opdateringer</strong></font></h1>

    </td>

   </tr>

  </table>

[span style=\"color:#0000BB\"]<?

$posts [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]myLimit[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'1\'[/span][span style=\"color:#007700\"]);

 

if(!isset([/span][span style=\"color:#0000BB\"]$news_limit[/span][span style=\"color:#007700\"]))

{

  [/span][span style=\"color:#0000BB\"]$news_limit[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]5[/span][span style=\"color:#007700\"];

}

 

if (empty([/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"])) {

  [/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]; [/span][span style=\"color:#FF8000\"]// Where to start in the list, if nothing\'s specified. Default should be \'0\'

[/span][span style=\"color:#007700\"]}

 

[/span][span style=\"color:#0000BB\"]$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT * FROM nyheder ORDER BY id DESC limit $offset,$news_limit\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$sql_result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]myLimit[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'\'[/span][span style=\"color:#007700\"]);

 

        [/span][span style=\"color:#0000BB\"]$pages[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]intval[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$posts[/span][span style=\"color:#007700\"]/[/span][span style=\"color:#0000BB\"]$news_limit[/span][span style=\"color:#007700\"]);

 

        [/span][span style=\"color:#FF8000\"]// $pages now contains int of pages needed unless there is a remainder from division

        [/span][span style=\"color:#007700\"]if ([/span][span style=\"color:#0000BB\"]$posts[/span][span style=\"color:#007700\"]%[/span][span style=\"color:#0000BB\"]$news_limit[/span][span style=\"color:#007700\"]) {

            [/span][span style=\"color:#0000BB\"]$pages[/span][span style=\"color:#007700\"]++;

        }

 

while([/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]mssql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql_result[/span][span style=\"color:#007700\"]))

{

[/span][span style=\"color:#0000BB\"]?>

[/span]  <table width=\"400\" border=\"0\">

   <tr>

    <td width=\"400\" valign=\"top\">

     <table class=1 cellspacing=0 width=100%>

      <tr>

       <td class=tmain>

        <font style=\"color:#999999\">[span style=\"color:#0000BB\"]<? [/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'add_time\'[/span][span style=\"color:#007700\"]]; [/span][span style=\"color:#0000BB\"]?>[/span]</font><br>

        [span style=\"color:#0000BB\"]<? [/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'news\'[/span][span style=\"color:#007700\"]]; [/span][span style=\"color:#0000BB\"]?>

[/span]       </td>

      </tr>

     </table>

    </td>

   </tr>

  </table>

  <br>

[span style=\"color:#0000BB\"]<?

[/span][span style=\"color:#007700\"]}

if([/span][span style=\"color:#0000BB\"]$pages [/span][span style=\"color:#007700\"]> [/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"])

{

 

[/span][span style=\"color:#0000BB\"]?>

[/span]        <center>

[span style=\"color:#0000BB\"]<?

[/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#DD0000\"]\"Side [\"[/span][span style=\"color:#007700\"];

for ([/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]<=[/span][span style=\"color:#0000BB\"]$pages[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]++) {

  [/span][span style=\"color:#0000BB\"]$newoffset[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]$news_limit[/span][span style=\"color:#007700\"]*([/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]-[/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]);

  if ([/span][span style=\"color:#0000BB\"]$offset[/span][span style=\"color:#007700\"]==[/span][span style=\"color:#0000BB\"]$newoffset[/span][span style=\"color:#007700\"]) {

   print ([/span][span style=\"color:#DD0000\"]\" <b>$i</b> \"[/span][span style=\"color:#007700\"]);

  }

  else {

   print [/span][span style=\"color:#DD0000\"]\"<a href=\\"[/span][span style=\"color:#0000BB\"]$PHP_SELF[/span][span style=\"color:#007700\"]?[/span][span style=\"color:#0000BB\"]offset[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]$newoffset[/span][span style=\"color:#007700\"]&[/span][span style=\"color:#FF8000\"]#092;\">$i</a>\n\";

  [/span][span style=\"color:#007700\"]}

  }

  echo [/span][span style=\"color:#DD0000\"]\"]\n\"[/span][span style=\"color:#007700\"];

 

[/span][span style=\"color:#0000BB\"]?>

[/span]        </center>

[span style=\"color:#0000BB\"]<?

[/span][span style=\"color:#007700\"]}

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Oh, and here is the test table I made to demonstrate it:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[news]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[news]
GO

CREATE TABLE [dbo].[news] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[add_time] [datetime] NULL ,
[news] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[news] WITH NOCHECK ADD 
CONSTRAINT [DF_news_add_time] DEFAULT (getdate()) FOR [add_time]
GO

 

Let me know if it works for You :)

Link to comment
Share on other sites

  • 4 weeks later...

man.. I came back to thei topic to give it a bump.. i didnt realize that you had already posted your solution.. I will give this a try!!!!!!

Thanks so much...

 

Will try to let you know in a few days...

 

THis looks great.. cant wait to try it!

 

 

[!--quoteo(post=310536:date=Oct 25 2005, 10:37 AM:name=Munchen)--][div class=\'quotetop\']QUOTE(Munchen @ Oct 25 2005, 10:37 AM) 310536[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Ok, here is something i just made ... It does the trick ...

 

<!--PHP-Head--><div class='phptop'>PHP</div><div class='phpmain'><!--PHP-EHead-->

<span style="color:#0000BB"><?php

$sqlserver </span><span style="color:#007700">= </span><span style="color:#DD0000">"."</span><span style="color:#007700">;

</span><span style="color:#0000BB">$sqluser </span><span style="color:#007700">= </span><span style="color:#DD0000">"test"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$sqlpass </span><span style="color:#007700">= </span><span style="color:#DD0000">"test"</span><span style="color:#007700">;

 

</span><span style="color:#0000BB">$connection </span><span style="color:#007700">= </span><span style="color:#0000BB">mssql_connect</span><span style="color:#007700">(</span><span style="color:#0000BB">$sqlserver </span><span style="color:#007700">, </span><span style="color:#0000BB">$sqluser </span><span style="color:#007700">, </span><span style="color:#0000BB">$sqlpass </span><span style="color:#007700">);

</span><span style="color:#0000BB">$max_on_page </span><span style="color:#007700">= </span><span style="color:#0000BB">5</span><span style="color:#007700">;

 

function </span><span style="color:#0000BB">myLimit</span><span style="color:#007700">(</span><span style="color:#0000BB">$offset</span><span style="color:#007700">=</span><span style="color:#DD0000">""</span><span style="color:#007700">, </span><span style="color:#0000BB">$mycount</span><span style="color:#007700">=</span><span style="color:#DD0000">""</span><span style="color:#007700">){

global </span><span style="color:#0000BB">$connection</span><span style="color:#007700">, </span><span style="color:#0000BB">$max_on_page</span><span style="color:#007700">;

if(!empty(</span><span style="color:#0000BB">$mycount</span><span style="color:#007700">)){

</span><span style="color:#0000BB">$query </span><span style="color:#007700">= </span><span style="color:#DD0000">"SELECT COUNT(*) FROM news"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$result </span><span style="color:#007700">= </span><span style="color:#0000BB">mssql_fetch_array</span><span style="color:#007700">(</span><span style="color:#0000BB">mssql_query</span><span style="color:#007700">(</span><span style="color:#0000BB">$query </span><span style="color:#007700">, </span><span style="color:#0000BB">$connection</span><span style="color:#007700">));

</span><span style="color:#0000BB">$result </span><span style="color:#007700">= </span><span style="color:#0000BB">$result</span><span style="color:#007700">[</span><span style="color:#0000BB">0</span><span style="color:#007700">];

}

else{

</span><span style="color:#FF8000">// The pagination.

</span><span style="color:#007700">if(</span><span style="color:#0000BB">$offset</span><span style="color:#007700">></span><span style="color:#0000BB">1</span><span style="color:#007700">)

{

</span><span style="color:#0000BB">$cond</span><span style="color:#007700">=</span><span style="color:#DD0000">"max"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$internal </span><span style="color:#007700">= </span><span style="color:#0000BB">$offset</span><span style="color:#007700">;

}

else

{

</span><span style="color:#0000BB">$cond</span><span style="color:#007700">=</span><span style="color:#DD0000">"min"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$internal </span><span style="color:#007700">= </span><span style="color:#0000BB">$offset</span><span style="color:#007700">*</span><span style="color:#0000BB">$max_on_page</span><span style="color:#007700">;

}

 

</span><span style="color:#0000BB">$query </span><span style="color:#007700">= </span><span style="color:#DD0000">"select $cond (t.id) from (select top $internal id from news ORDER BY id) t"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$result </span><span style="color:#007700">= </span><span style="color:#0000BB">mssql_query</span><span style="color:#007700">(</span><span style="color:#0000BB">$query</span><span style="color:#007700">, </span><span style="color:#0000BB">$connection</span><span style="color:#007700">);

</span><span style="color:#0000BB">$row </span><span style="color:#007700">= </span><span style="color:#0000BB">mssql_fetch_array</span><span style="color:#007700">(</span><span style="color:#0000BB">$result</span><span style="color:#007700">);

</span><span style="color:#0000BB">$maxID </span><span style="color:#007700">= </span><span style="color:#0000BB">$row</span><span style="color:#007700">[</span><span style="color:#0000BB">0</span><span style="color:#007700">]; </span><span style="color:#FF8000">// max id

</span><span style="color:#0000BB">$query </span><span style="color:#007700">= </span><span style="color:#DD0000">"SELECT TOP " </span><span style="color:#007700">. </span><span style="color:#0000BB">$max_on_page </span><span style="color:#007700">. </span><span style="color:#DD0000">" * FROM news WHERE id > '"</span><span style="color:#007700">.</span><span style="color:#0000BB">$maxID</span><span style="color:#007700">.</span><span style="color:#DD0000">"' ORDER BY id"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$result </span><span style="color:#007700">= </span><span style="color:#0000BB">mssql_query</span><span style="color:#007700">(</span><span style="color:#0000BB">$query </span><span style="color:#007700">, </span><span style="color:#0000BB">$connection</span><span style="color:#007700">);

}

return </span><span style="color:#0000BB">$result</span><span style="color:#007700">;

}

 

</span><span style="color:#0000BB">$posts </span><span style="color:#007700">= </span><span style="color:#0000BB">myLimit</span><span style="color:#007700">(</span><span style="color:#DD0000">''</span><span style="color:#007700">, </span><span style="color:#DD0000">'1'</span><span style="color:#007700">);

 

</span><span style="color:#0000BB">mssql_close</span><span style="color:#007700">(</span><span style="color:#0000BB">$connection</span><span style="color:#007700">);

</span><span style="color:#0000BB">?></span>

 

<table width="400" border="0">

 <tr>

<td width="400" valign="top">

 <h1><font style="color:#000000"><strong> Nyheder & Opdateringer</strong></font></h1>

</td>

 </tr>

</table>

<span style="color:#0000BB"><?

$posts </span><span style="color:#007700">= </span><span style="color:#0000BB">myLimit</span><span style="color:#007700">(</span><span style="color:#DD0000">''</span><span style="color:#007700">, </span><span style="color:#DD0000">'1'</span><span style="color:#007700">);

 

if(!isset(</span><span style="color:#0000BB">$news_limit</span><span style="color:#007700">))

{

</span><span style="color:#0000BB">$news_limit</span><span style="color:#007700">=</span><span style="color:#0000BB">5</span><span style="color:#007700">;

}

 

if (empty(</span><span style="color:#0000BB">$offset</span><span style="color:#007700">)) {

</span><span style="color:#0000BB">$offset</span><span style="color:#007700">=</span><span style="color:#0000BB">0</span><span style="color:#007700">; </span><span style="color:#FF8000">// Where to start in the list, if nothing's specified. Default should be '0'

</span><span style="color:#007700">}

 

</span><span style="color:#0000BB">$sql </span><span style="color:#007700">= </span><span style="color:#DD0000">"SELECT * FROM nyheder ORDER BY id DESC limit $offset,$news_limit"</span><span style="color:#007700">;

</span><span style="color:#0000BB">$sql_result </span><span style="color:#007700">= </span><span style="color:#0000BB">myLimit</span><span style="color:#007700">(</span><span style="color:#0000BB">$offset</span><span style="color:#007700">, </span><span style="color:#DD0000">''</span><span style="color:#007700">);

 

</span><span style="color:#0000BB">$pages</span><span style="color:#007700">=</span><span style="color:#0000BB">intval</span><span style="color:#007700">(</span><span style="color:#0000BB">$posts</span><span style="color:#007700">/</span><span style="color:#0000BB">$news_limit</span><span style="color:#007700">);

 

</span><span style="color:#FF8000">// $pages now contains int of pages needed unless there is a remainder from division

</span><span style="color:#007700">if (</span><span style="color:#0000BB">$posts</span><span style="color:#007700">%</span><span style="color:#0000BB">$news_limit</span><span style="color:#007700">) {

</span><span style="color:#0000BB">$pages</span><span style="color:#007700">++;

}

 

while(</span><span style="color:#0000BB">$row</span><span style="color:#007700">=</span><span style="color:#0000BB">mssql_fetch_array</span><span style="color:#007700">(</span><span style="color:#0000BB">$sql_result</span><span style="color:#007700">))

{

</span><span style="color:#0000BB">?>

</span> <table width="400" border="0">

 <tr>

<td width="400" valign="top">

 <table class=1 cellspacing=0 width=100%>

<tr>

 <td class=tmain>

<font style="color:#999999"><span style="color:#0000BB"><? </span><span style="color:#007700">echo </span><span style="color:#0000BB">$row</span><span style="color:#007700">[</span><span style="color:#DD0000">'add_time'</span><span style="color:#007700">]; </span><span style="color:#0000BB">?></span></font><br>

<span style="color:#0000BB"><? </span><span style="color:#007700">echo </span><span style="color:#0000BB">$row</span><span style="color:#007700">[</span><span style="color:#DD0000">'news'</span><span style="color:#007700">]; </span><span style="color:#0000BB">?>

</span>  </td>

</tr>

 </table>

</td>

 </tr>

</table>

<br>

<span style="color:#0000BB"><?

</span><span style="color:#007700">}

if(</span><span style="color:#0000BB">$pages </span><span style="color:#007700">> </span><span style="color:#0000BB">1</span><span style="color:#007700">)

{

 

</span><span style="color:#0000BB">?>

</span> <center>

<span style="color:#0000BB"><?

</span><span style="color:#007700">echo </span><span style="color:#DD0000">"Side ["</span><span style=color:#007700">;

for (</span><span style="color:#0000BB">$i</span><span style="color:#007700">=</span><span style="color:#0000BB">1</span><span style="color:#007700">;</span><span style="color:#0000BB">$i</span><span style="color:#007700"><=</span><span style="color:#0000BB">$pages</span><span style="color:#007700">;</span><span style="color:#0000BB">$i</span><span style="color:#007700">++) {

</span><span style="color:#0000BB">$newoffset</span><span style="color:#007700">=</span><span style="color:#0000BB">$news_limit</span><span style="color:#007700">*(</span><span style="color:#0000BB">$i</span><span style="color:#007700">-</span><span style="color:#0000BB">1</span><span style="color:#007700">);

if (</span><span style="color:#0000BB">$offset</span><span style="color:#007700">==</span><span style="color:#0000BB">$newoffset</span><span style="color:#007700">) {

 print (</span><span style="color:#DD0000">" <b>$i</b> "</span><span style="color:#007700">);

}

else {

 print </span><span style="color:#DD0000">"<a href=\"</span><span style="color:#0000BB">$PHP_SELF</span><span style="color:#007700">?</span><span style="color:#0000BB">offset</span><span style="color:#007700">=</span><span style="color:#0000BB">$newoffset</span><span style="color:#007700">&</span><span style="color:#FF8000">#092;">$i</a>\n";

</span><span style="color:#007700">}

}

echo </span><span style="color:#DD0000"&gt]\n"</span><span style="color:#007700">;

 

</span><span style="color:#0000BB">?>

</span> </center>

<span style="color:#0000BB"><?

</span><span style="color:#007700">}

</span><span style="color:#0000BB">?></span>

</span><!--PHP-Foot--></div><!--PHP-EFoot-->

 

Oh, and here is the test table I made to demonstrate it:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[news]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[news]
GO

CREATE TABLE [dbo].[news] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [add_time] [datetime] NULL ,
    [news] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[news] WITH NOCHECK ADD 
    CONSTRAINT [DF_news_add_time] DEFAULT (getdate()) FOR [add_time]
GO

 

Let me know if it works for You :)

 

Link to comment
Share on other sites

  • 2 weeks later...

I was recently searching for an approach to do paging with MSSQL and PHP5.

 

There are many methods posted on the internet e.g. using recordsets, temp-table, cursors etc. There is a pretty simple Asc-Desc method which uses default ordering in a subquery and then applies the reverse ordering to emulate MySQL Limit functionality in MSSQL.

 

MySQL:

SELECT * FROM TABLE LIMIT 20,10

 

MSSQL:

SELECT *

FROM (SELECT TOP 10 *

FROM (SELECT TOP 30 *

FROM TABLE

ORDER BY PK)

ORDER BY PK DESC)

ORDER BY PK

 

It was pretty cool but later I realised that it had flaws in that it requires the sorting column to be unique for it work. In most cases sorting columns (besides the PK) won't be unique! Complications comes in as well if you have complicated queries involving more than one tables. The other methods seems to involve a lot of work such as creating stored procedures, slamming temp tables etc.

 

And then an article triggers my thoughts -> MSSQL - XML paging:

 

I felt that this could be the direction and I did some research and experimentation. I was happy that the approach worked and the best part is - no modification of queries (already damn complicated) and it's simple and neat.

 

Here's what I did:

 

Step 1:

Use PHP DOM function to stream MSSQL query results to a XML file. The created XML file is then parsed using PHP SimpleXML functions.

 

$query = "<my complex query ...>";

$result = mssql_query($query) or die();

 

if ($totRec > 0) {

$doc = new DomDocument('1.0', 'UTF-8');

 

$root = $doc->createElement('root');

$root = $doc->appendChild($root);

 

while($row = mssql_fetch_assoc($result)) {

$occ = $doc->createElement('Products');

$occ = $root->appendChild($occ);

 

foreach ($row as $fieldname => $fieldvalue) {

$child = $doc->createElement($fieldname);

$child = $occ->appendChild($child);

$value = $doc->createTextNode($fieldvalue);

$value = $child->appendChild($value);

}

}

 

// get completed xml document

$xmldoc = $doc->saveXML();

$xml = simplexml_load_string($xmldoc);

 

Step 2:

Compute the start row and end row for the selected page.

Start Row = (Selected Page - 1) * (No. of Recs Per Page)

End Row = (Start Row + No. of Recs Per Page - 1)

 

Step 3:

Use a FOR loop to display the desired nodes (record sets) in the created XML file.

for ($i = $rowStart; $i <= $rowEnd; $i++) {

...

...

$xml->Child[$i]->SubChild;

...

...

}

 

Hope that this helps :)

 

 

 

 

 

Link to comment
Share on other sites

  • 2 months later...

[!--quoteo(post=307170:date=Oct 16 2005, 10:36 PM:name=Stripe-man)--][div class=\'quotetop\']QUOTE(Stripe-man @ Oct 16 2005, 10:36 PM) 307170[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I have been looking and looking for a good function or script for paging with MICROSOFTSQL but have had NO luck... can someone assist ?

 

Yesss!!! Just take a look at [a href=\"http://ionescuraul.users.phpclasses.org/browse/package/2832.html\" target=\"_blank\"]http://ionescuraul.users.phpclasses.org/br...ckage/2832.html[/a]

Link to comment
Share on other sites

[!--quoteo(post=348965:date=Feb 24 2006, 09:54 AM:name=raul)--][div class=\'quotetop\']QUOTE(raul @ Feb 24 2006, 09:54 AM) 348965[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Yesss!!! Just take a look at [a href=\"http://ionescuraul.users.phpclasses.org/browse/package/2832.html\" target=\"_blank\"]http://ionescuraul.users.phpclasses.org/br...ckage/2832.html[/a]

 

 

yes and it works great!!!!!

 

example...

 

[a href=\"http://stripe-man.dyndns.org/dev/rc4php-2006-02-20/index.php\" target=\"_blank\"]http://stripe-man.dyndns.org/dev/rc4php-2006-02-20/index.php[/a]

Link to comment
Share on other sites

  • 2 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.