Re: Paging a SQL result...

This WebDNA talk-list message is from

2003

It keeps the original formatting. numero = 53240
interpreted = N
texte = I don't think LIMIT is a standard SQL clause (my guess is that PHP uses it to decide which rows to fetch from the cursor). In any event, I don't think it will work with SQL Server. However, I think you can write something that will fetch just the rows you want by declaring your own cursor - I'm just not sure if it'll work directly from WebCat or if you'd have to do it in a stored procedure. I thought there was a simple clause you could add to your SQL with SQL Server to return just those rows you want but I don't remember what it is - if it exists. In any event, doing this would certainly muck up the [numfound] variable. Unless your results can have hundreds or thousands of results I'd stick with showing and hiding.>Why not just use the the LIMIT and offset?>>>[!] -----------------> SET THE MAX # TO BE FOUND> ------------------ [/!]>[mathshow=f]maxfound=5[/math]>>[!] ------------------> CHECK TO SEE IF FIRST TIME> VIEWING PAGE> ------------------- [/!]>[showif [offset]=[raw][offset][/raw]]>[text]offset=0[/text]>[math show=f]prev=-[maxfound][/math]>[math show=f]next=+[maxfound][/math]>[/showif]>[showif [offset]!0]>[math show=f]prev=[offset]-[maxfound][/math]>[math show=f]next=[offset]+[maxfound][/math]>[/showif]>>>[!] --------------------------> THEN SET SELECT STATEMENT> -------------------------- [/!]>select * from FIELD ORDER BY 1 DESC LIMIT [offset],[maxfound]>>[!]---------------------------> NOW YOU CREATE YOUR PREV AND NEXT BUTTONS> --------------------------- [/!]> Previous> Next>>Grated this is how I do it in PHP. I have simply modified the creation of>the variables to be webcat. If WebCat does not allow the LIMIT in the SQL>query I do not know. I do not use SQL w/webcat.>>>>>----- Original Message ----- >From: "John Hill" >To: "WebDNA Talk" >Sent: Thursday, October 02, 2003 10:40 AM>Subject: Re: Paging a SQL result...>>>> The quick solution is to have [showif] or [if] statements in your>[founditems] that show which rows you want. However, this means that huge>result sets are still fully returned. This works just fine for smaller (a>few hundred or less?) results.>>>> A cleaner solution would be to get SQL server to only return the rows you>want. I don't believe WebCat can fetch just the specified rows, but you>could probably write your own SQL or even stored procedure to do this for>you.>>>> Contact me off list for some more specific ideas.>>>> John.>>>> >We run Webcatalog 4.5 and use MS SQL Server 2000 for the database - and>it>> >runs ok.>> >>> >But I have a problem i wish to devide a search result into pages... This>is>> >pretty easy when you use the Webcatalog flat files databases - there you>> >could use the startat property.>> >>> >But when you use a SQL database things are not so simple...>> >>> >If you use ASP.NET, ASP or PHP there are ways to show the result in>pages ->> >but when you use webcatalog you have to do it in the SQL-statement (as I>> >understand)>> >>> >I'm just curious - surely some of you guys can't manage with the>"primitive">> >flat-file-db-system Webcatalog have to offer. So how have you made your>> >solution to show result in pages in webdna from at sql database?>> >>> >Hope someone have a good solution....>> >>> >>> >Best regards>> >Morten>> >>> >>> >>> >------------------------------------------------------------->> >This message is sent to you because you are subscribed to>> > the mailing list .>> >To unsubscribe, E-mail to: >> >To switch to the DIGEST mode, E-mail to>>> >Web Archive of this list is at: http://webdna.smithmicro.com/>>>> -- >>>> --------------------------------->> John A. Hill>> Oak Hill Software>> Website Development/Consulting>> john@oakhillsoftware.com>>>> ------------------------------------------------------------->> This message is sent to you because you are subscribed to>> the mailing list .>> To unsubscribe, E-mail to: >> To switch to the DIGEST mode, E-mail to>>> Web Archive of this list is at: http://webdna.smithmicro.com/>>>------------------------------------------------------------->This message is sent to you because you are subscribed to> the mailing list .>To unsubscribe, E-mail to: >To switch to the DIGEST mode, E-mail to >Web Archive of this list is at: http://webdna.smithmicro.com/-- --------------------------------- John A. Hill Oak Hill SoftwareWebsite Development/Consulting john@oakhillsoftware.com-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Associated Messages, from the most recent to the oldest:

Re: Paging a SQL result... ( "WebDna @" 2003)
Re: Paging a SQL result... ( John Hill 2003)
Re: Paging a SQL result... ( "WebDna @" 2003)
Re: Paging a SQL result... ( John Hill 2003)
Paging a SQL result... ( "Morten Madsen" 2003)

I don't think LIMIT is a standard SQL clause (my guess is that PHP uses it to decide which rows to fetch from the cursor). In any event, I don't think it will work with SQL Server. However, I think you can write something that will fetch just the rows you want by declaring your own cursor - I'm just not sure if it'll work directly from WebCat or if you'd have to do it in a stored procedure. I thought there was a simple clause you could add to your SQL with SQL Server to return just those rows you want but I don't remember what it is - if it exists. In any event, doing this would certainly muck up the [numfound] variable. Unless your results can have hundreds or thousands of results I'd stick with showing and hiding.>Why not just use the the LIMIT and offset?>>>[!] -----------------> SET THE MAX # TO BE FOUND> ------------------ [/!]>[mathshow=f]maxfound=5[/math]>>[!] ------------------> CHECK TO SEE IF FIRST TIME> VIEWING PAGE> ------------------- [/!]>[showif [offset]=[raw][offset][/raw]]>[text]offset=0[/text]>[math show=f]prev=-[maxfound][/math]>[math show=f]next=+[maxfound][/math]>[/showif]>[showif [offset]!0]>[math show=f]prev=[offset]-[maxfound][/math]>[math show=f]next=[offset]+[maxfound][/math]>[/showif]>>>[!] --------------------------> THEN SET SELECT STATEMENT> -------------------------- [/!]>select * from FIELD ORDER BY 1 DESC LIMIT [offset],[maxfound]>>[!]---------------------------> NOW YOU CREATE YOUR PREV AND NEXT BUTTONS> --------------------------- [/!]>[thisurl]?offset=[prev]> Previous>[thisurl]?offset=[next]> Next>>Grated this is how I do it in PHP. I have simply modified the creation of>the variables to be webcat. If WebCat does not allow the LIMIT in the SQL>query I do not know. I do not use SQL w/webcat.>>>>>----- Original Message ----- >From: "John Hill" >To: "WebDNA Talk" >Sent: Thursday, October 02, 2003 10:40 AM>Subject: Re: Paging a SQL result...>>>> The quick solution is to have [showif] or [if] statements in your>[founditems] that show which rows you want. However, this means that huge>result sets are still fully returned. This works just fine for smaller (a>few hundred or less?) results.>>>> A cleaner solution would be to get SQL server to only return the rows you>want. I don't believe WebCat can fetch just the specified rows, but you>could probably write your own SQL or even stored procedure to do this for>you.>>>> Contact me off list for some more specific ideas.>>>> John.>>>> >We run Webcatalog 4.5 and use MS SQL Server 2000 for the database - and>it>> >runs ok.>> >>> >But I have a problem i wish to devide a search result into pages... This>is>> >pretty easy when you use the Webcatalog flat files databases - there you>> >could use the startat property.>> >>> >But when you use a SQL database things are not so simple...>> >>> >If you use ASP.NET, ASP or PHP there are ways to show the result in>pages ->> >but when you use webcatalog you have to do it in the SQL-statement (as I>> >understand)>> >>> >I'm just curious - surely some of you guys can't manage with the>"primitive">> >flat-file-db-system Webcatalog have to offer. So how have you made your>> >solution to show result in pages in webdna from at sql database?>> >>> >Hope someone have a good solution....>> >>> >>> >Best regards>> >Morten>> >>> >>> >>> >------------------------------------------------------------->> >This message is sent to you because you are subscribed to>> > the mailing list .>> >To unsubscribe, E-mail to: >> >To switch to the DIGEST mode, E-mail to>>> >Web Archive of this list is at: http://webdna.smithmicro.com/>>>> -- >>>> --------------------------------->> John A. Hill>> Oak Hill Software>> Website Development/Consulting>> john@oakhillsoftware.com>>>> ------------------------------------------------------------->> This message is sent to you because you are subscribed to>> the mailing list .>> To unsubscribe, E-mail to: >> To switch to the DIGEST mode, E-mail to>>> Web Archive of this list is at: http://webdna.smithmicro.com/>>>------------------------------------------------------------->This message is sent to you because you are subscribed to> the mailing list .>To unsubscribe, E-mail to: >To switch to the DIGEST mode, E-mail to >Web Archive of this list is at: http://webdna.smithmicro.com/-- --------------------------------- John A. Hill Oak Hill SoftwareWebsite Development/Consulting john@oakhillsoftware.com-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ John Hill

DOWNLOAD WEBDNA NOW!

Re: Paging a SQL result...

2003

Top Articles:

Related Readings: