Jump to content

Archived

This topic is now archived and is closed to further replies.

Stripe-man

Paging with MSSQL and PHP5

Recommended Posts

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

 

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
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]

 

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

I'll find my code when I get home from work ... I made this in a project once ...

I did it in a somewhat other fasion than Yours, but Stay tuned .. :)

Share this post


Link to post
Share on other sites
I'll find my code when I get home from work ... I made this in a project once ...

I did it in a somewhat other fasion than Yours, but Stay tuned .. :)

309120[/snapback]

 

 

Stripe-man awaits patiently!

Share this post


Link to post
Share on other sites

Yeah, I found some, but I had converted it to MySQL :/

And I must admit, I forgot all about it ...

 

Im having a look at it now ...

Share this post


Link to post
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 :)

Share this post


Link to post
Share on other sites

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 :)

 

Share this post


Link to post
Share on other sites

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 :)

 

 

 

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

×

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.