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 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/
Associated Messages, from the most recent to the oldest:
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 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/
John Hill
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
Problem with summary on date / inconsequent webcat behaviour (1998)
WebCat2 - Getting to the browser's username/password data (1997)
PCS Emailer's role ? (1997)
Re:Using WebMerchant Only? (1998)
WebCatalog Technical Reference (1997)
Looking for a Webcat developer based in the Netherlands (2000)
OT : Virus-Macs mostly immune from worm (2000)
Caching [include] files ... (1997)
Merging databases (1997)
Running _every_ page through WebCat-error.html (1997)
PCS Frames-Default page is solution! (1997)
Sorting (1998)
Checkboxes (1998)
Quit revisited (1997)
Do not truncate a whole word (2004)
Help name our technology! (1997)
Shipping.db (1998)
RE: File Uploads: WebCat/SiteEdit (1998)
can webcat create directories on NT? (1997)
Searching on 3 different fields *CORRECTION* (2003)