Re: mySQL numfound

This WebDNA talk-list message is from

2006


It keeps the original formatting.
numero = 66669
interpreted = N
texte = Hi Brian below is the paging code for WebDNA and MySQL. I substituted my tables and columns with 'TableName' and 'ColumnName' respectively. [!]General Paging Script with MYSQL and WEBDNA[/!] [!]Make connection to mySQL db[/!] [SQLConnect dbType=MySQL...][/SQLconnect] [!]Set Max Number of Records Per Page[/!] [math show=F]LIMIT=100[/math] [!]Get Total Records[/!] [SQLExecute conn_ref=connMT&result_var=rs1] SELECT ColumnName FROM TableName; [/SQLExecute] Total Records: [MATH]totalRecords=[SQLResult result_ref=rs1][numfound][/SQLResult][/MATH]
[!]Calculate Total Pages[/!] Total Pages: [MATH]totalpages=CEIL([totalrecords]/[limit])[/MATH]
[!]Get current page # from URL[/!] [math show=F]currentpage=[pageno][/math] [!]Error-Proof code for paging[/!] [if ("[currentpage]"="[RAW][currentpage][/RAW]")|("[currentpage]"="")|([currentpage]<1)|([currentpage]>[totalpages])] [then] [math show=F]currentpage=1[/math] [/then] [/if] Current Page: [currentpage]
[!]Calculate the Offset - offset starts with 0 in MySQL[/!] OFFSET: [math]OFFSET=([currentpage]-1)*[limit][/math]
[!]Build the SQL Query[/!] [text]QS=SELECT * FROM ColumnName ORDER BY ColumnName DESC LIMIT [OFFSET],[LIMIT];[/text] [SQLExecute conn_ref=connMT&result_var=rs2] [QS] [/SQLExecute] [SQLResult result_ref=rs2] [math][OFFSET]+1[/math] to [math][OFFSET]+[Numfound][/math] of [totalrecords] records found
[FoundItems] [/FoundItems]
[!]Create Page Links[/!] Page: [loop start=1&end=[totalpages]] [showif [currentpage]=[index]][index][/showif][hideif [currentpage]=[index]][index][/hideif] [/loop]
[!]Create Previous link[/!] [hideif [currentPage]=1] Previous [/hideif] [!]Create Next link[/!] [hideif [currentPage]=[totalpages]] Next [/hideif]
[math][index]+[limit]*([currentpage]-1)[/math][PostDate][PostTime][salary]
[/SQLResult] [!]Close the db connection[/!] [SQLDisconnect conn_ref=connMT] brian@orderbywire.com wrote: > Thanks adam. > I think I will go with the double search. I would be interested to see how > you put together that paging function though. > > thanks > > > Brian Boegershausen > Endeavotech Advanced Business Solutions LLC. > http://www.endeavotech.com > brian@endeavotech.com > > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of Adam > O'Connor > Sent: Saturday, April 01, 2006 11:48 AM > To: WebDNA Talk > Subject: Re: mySQL numfound > > Brian, > > Sorry was out of town, there are a couple of methods to retrieve the > numfound via MySQL - however they both require more than one search - that > is if you are limiting the main recordset. 1) You can either do an > un-restricted search and return all rows thus get a numfound value or 2) Use > an alias within your SQL syntax using MySQL's count(*) function. > > If you need more assistance, I can send you a paging function that resembles > webcats. My best advice is to study PHP when developing with MySQL & > webcat. Whenever you get held up from webcat's limited SQL functionality, > just look at how to accomplish the task via PHP, then model this function > via webcat. Hope this helps. > > > > > > > Donovan Brooke wrote: > >> brian@orderbywire.com wrote: >> >> >>> Has anyone come across a solutions to display the [numfound] in a >>> mysql search result? >>> >>> The [numfound] tag only shows the LIMIT in the mysql search content. >>> If you >>> put a numfound tag in a WebDNA search, it shows the number found in >>> the DB, not the MAX in the search context. >>> >>> To properly set up my PREVIOUS & NEXT buttons, I need the know the >>> total number of records returned in the search. >>> The only thing I can think to do is write the search twice on the >>> page and not set the LIMIT. >>> >>> Any thoughts would be appreciated. >>> >>> Thanks >>> Brian >>> >> Brian, >> could you post your code. If you are putting a limit on your select >> statement, yea, numfound would return your limit. I'm a newb at SQL >> but I would think you could execute an SQL native "numfound" >> and then reference that. Off the top of my head, Isn't it something >> like foundcount? >> >> I'm sure somone will have better info for you but it would help us if >> you posted your code. >> >> Donovan >> >> >> >> > > > > ------------------------------------------------------------- > 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/ > ------------------------------------------------------------- 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:

    
  1. Re: mySQL numfound ( Alex McCombie 2006)
  2. Re: mySQL numfound ( Adam O'Connor 2006)
  3. Re: mySQL numfound ( Alex McCombie 2006)
  4. Re: mySQL numfound ( 2006)
  5. Re: mySQL numfound ( Adam O'Connor 2006)
  6. Re: mySQL numfound ( nitai@computeroil.com 2006)
  7. Re: mySQL numfound ( Donovan Brooke 2006)
  8. Re: mySQL numfound ( Kenneth Grome 2006)
  9. mySQL numfound ( 2006)
Hi Brian below is the paging code for WebDNA and MySQL. I substituted my tables and columns with 'TableName' and 'ColumnName' respectively. [!]General Paging Script with MYSQL and WEBDNA[/!] [!]Make connection to mySQL db[/!] [SQLConnect dbType=MySQL...][/SQLconnect] [!]Set Max Number of Records Per Page[/!] [math show=F]LIMIT=100[/math] [!]Get Total Records[/!] [SQLExecute conn_ref=connMT&result_var=rs1] SELECT ColumnName FROM TableName; [/SQLExecute] Total Records: [math]totalRecords=[SQLResult result_ref=rs1][numfound][/SQLResult][/MATH]
[!]Calculate Total Pages[/!] Total Pages: [math]totalpages=CEIL([totalrecords]/[limit])[/MATH]
[!]Get current page # from URL[/!] [math show=F]currentpage=[pageno][/math] [!]Error-Proof code for paging[/!] [if ("[currentpage]"="[raw][currentpage][/RAW]")|("[currentpage]"="")|([currentpage]<1)|([currentpage]>[totalpages])] [then] [math show=F]currentpage=1[/math] [/then] [/if] Current Page: [currentpage]
[!]Calculate the Offset - offset starts with 0 in MySQL[/!] OFFSET: [math]OFFSET=([currentpage]-1)*[limit][/math]
[!]Build the SQL Query[/!] [text]QS=SELECT * FROM ColumnName ORDER BY ColumnName DESC LIMIT [OFFSET],[LIMIT];[/text] [SQLExecute conn_ref=connMT&result_var=rs2] [QS] [/SQLExecute] [SQLResult result_ref=rs2] [math][OFFSET]+1[/math] to [math][OFFSET]+[Numfound][/math] of [totalrecords] records found
[founditems] [/FoundItems]
[!]Create Page Links[/!] Page: [loop start=1&end=[totalpages]] [showif [currentpage]=[index]][index][/showif][hideif [currentpage]=[index]][thisurl]?[formvariables][hideif [name]=template][hideif [name]=pageno][name]=[url][value][/url]&[/hideif][/hideif][/Formvariables]pageno=[index]">[index][/hideif] [/loop]
[!]Create Previous link[/!] [hideif [currentPage]=1] [thisurl]?[formvariables][hideif [name]=template][hideif [name]=pageno][name]=[url][value][/url]&[/hideif][/hideif][/Formvariables]pageno=[math][currentpage]-1[/math]">Previous [/hideif] [!]Create Next link[/!] [hideif [currentPage]=[totalpages]] [thisurl]?[formvariables][hideif [name]=template][hideif [name]=pageno][name]=[url][value][/url]&[/hideif][/hideif][/Formvariables]pageno=[math][currentpage]+1[/math]">Next [/hideif]
[math][index]+[limit]*([currentpage]-1)[/math][PostDate][PostTime][salary]
[/SQLResult] [!]Close the db connection[/!] [SQLDisconnect conn_ref=connMT] brian@orderbywire.com wrote: > Thanks adam. > I think I will go with the double search. I would be interested to see how > you put together that paging function though. > > thanks > > > Brian Boegershausen > Endeavotech Advanced Business Solutions LLC. > http://www.endeavotech.com > brian@endeavotech.com > > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of Adam > O'Connor > Sent: Saturday, April 01, 2006 11:48 AM > To: WebDNA Talk > Subject: Re: mySQL numfound > > Brian, > > Sorry was out of town, there are a couple of methods to retrieve the > numfound via MySQL - however they both require more than one search - that > is if you are limiting the main recordset. 1) You can either do an > un-restricted search and return all rows thus get a numfound value or 2) Use > an alias within your SQL syntax using MySQL's count(*) function. > > If you need more assistance, I can send you a paging function that resembles > webcats. My best advice is to study PHP when developing with MySQL & > webcat. Whenever you get held up from webcat's limited SQL functionality, > just look at how to accomplish the task via PHP, then model this function > via webcat. Hope this helps. > > > > > > > Donovan Brooke wrote: > >> brian@orderbywire.com wrote: >> >> >>> Has anyone come across a solutions to display the [numfound] in a >>> mysql search result? >>> >>> The [numfound] tag only shows the LIMIT in the mysql search content. >>> If you >>> put a numfound tag in a WebDNA search, it shows the number found in >>> the DB, not the MAX in the search context. >>> >>> To properly set up my PREVIOUS & NEXT buttons, I need the know the >>> total number of records returned in the search. >>> The only thing I can think to do is write the search twice on the >>> page and not set the LIMIT. >>> >>> Any thoughts would be appreciated. >>> >>> Thanks >>> Brian >>> >> Brian, >> could you post your code. If you are putting a limit on your select >> statement, yea, numfound would return your limit. I'm a newb at SQL >> but I would think you could execute an SQL native "numfound" >> and then reference that. Off the top of my head, Isn't it something >> like foundcount? >> >> I'm sure somone will have better info for you but it would help us if >> you posted your code. >> >> Donovan >> >> >> >> > > > > ------------------------------------------------------------- > 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/ > ------------------------------------------------------------- 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/ Adam O'Connor

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:

Signal Raised (1997) PSC recommends what date format yr 2000??? (1997) Templates on Unix & CGI on Mac? (1997) RE: Upload (1998) I think WebDNA-Talk died ... :( (1997) need e-commerce recommendation (2004) Re:No Data (1997) OT: Limit on # of Pulldown entries (1997) Re[2]: Weird [blank] interpretation (1999) Emailer setup (1997) form crasehes server (1997) [ot] iBook vs. 12" PB (2004) [CreateDate] tag? (1998) Access file on a remote server (2003) Sorting nested search (2003) normal users.db calls ... (1998) t or f (1997) [WebDNA] Limited Time Discount on WebDNA Server licenses (2013) hard carriage returns and sendmail (1998) Doesn't change (WebCat 4.0) (2000)