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:
Multiple cart additions (1997)
learning PHP (2007)
Secure Server (1997)
Entry pages (was: WebCatalog MAJOR drawback) (1998)
Hideif / Showif (1998)
Text data with spaces in them... (1997)
Listfiles not listing mounted volume (2002)
Non-technical messages ... (1997)
Merry Christmas! :) (1998)
Locking up with WebCatalog... (1997)
Re1000001: Setting up shop (1997)
OT: FTP problems (2003)
Bug? (1997)
[WebDNA] Calendar script (2009)
Using [detete] (1998)
[WebDNA] Query re: autonumber (2010)
PCS Frames (1997)
OT - Homesite Tags (2004)
Summing fields (1997)
Sendmail and links (2001)