Re: Paging a SQL result...

This WebDNA talk-list message is from

2003

It keeps the original formatting. numero = 53243
interpreted = N
texte = Thanks, that is why I ask why not. :) I am not SQL guru, hell I am not aguru at all!----- Original Message ----- From: "John Hill" To: "WebDNA Talk" Sent: Thursday, October 02, 2003 12:48 PMSubject: Re: Paging a SQL result...> I don't think LIMIT is a standard SQL clause (my guess is that PHP uses itto decide which rows to fetch from the cursor). In any event, I don't thinkit will work with SQL Server. However, I think you can write something thatwill fetch just the rows you want by declaring your own cursor - I'm justnot sure if it'll work directly from WebCat or if you'd have to do it in astored procedure. I thought there was a simple clause you could add to yourSQL with SQL Server to return just those rows you want but I don't rememberwhat it is - if it exists. In any event, doing this would certainly muck upthe [numfound] variable. Unless your results can have hundreds or thousandsof 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 rowsyou> >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 - thereyou> >> >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 (asI> >> >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 madeyour> >> >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/-------------------------------------------------------------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)

Thanks, that is why I ask why not. :) I am not SQL guru, hell I am not aguru at all!----- Original Message ----- From: "John Hill" To: "WebDNA Talk" Sent: Thursday, October 02, 2003 12:48 PMSubject: Re: Paging a SQL result...> I don't think LIMIT is a standard SQL clause (my guess is that PHP uses itto decide which rows to fetch from the cursor). In any event, I don't thinkit will work with SQL Server. However, I think you can write something thatwill fetch just the rows you want by declaring your own cursor - I'm justnot sure if it'll work directly from WebCat or if you'd have to do it in astored procedure. I thought there was a simple clause you could add to yourSQL with SQL Server to return just those rows you want but I don't rememberwhat it is - if it exists. In any event, doing this would certainly muck upthe [numfound] variable. Unless your results can have hundreds or thousandsof 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 rowsyou> >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 - thereyou> >> >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 (asI> >> >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 madeyour> >> >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/-------------------------------------------------------------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/ "WebDna @"

DOWNLOAD WEBDNA NOW!

Re: Paging a SQL result...

2003

Top Articles:

Related Readings: