Re: Newbie question regarding MSSQL queries w/ CF8
Thank you all, I used CF first per the first response and got repeats among the iterations and then tried the SQL version using TOP X and newid() and was able to make that work with no repeats! Also, thanks to everyone for your speedy responses and friendliness, I've had experiences with other forums that were terrible so it was a relief to find these forums for CF. I'll be sure to post with my next issue which is likely to be creating a secure login, unless there is already a thread on that here that someone might point me to? Great advice, keep it up folks. :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353856 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
As a thought experiment (at least), you could eliminate repeats using CF as follows: Download http://cflib.org/udf/ListRandomElements and then do this: cfset thelist = cfloop index=i from=1 to=#rs.recordcount# cfset thelist = listappend(thelist, i) /cfloop cfloop list=#ListRandomElements(thelist, rows)# index=displayRow br /h3#displayRow# #rs.first[displayRow]# #rs.last[displayRow]# #rs.flag[displayRow]#/h3br / /cfloop On Mon, Jan 14, 2013 at 10:59 AM, B Griffith br.griff...@yahoo.com wrote: Thank you all, I used CF first per the first response and got repeats among the iterations and then tried the SQL version using TOP X and newid() and was able to make that work with no repeats! Also, thanks to everyone for your speedy responses and friendliness, I've had experiences with other forums that were terrible so it was a relief to find these forums for CF. I'll be sure to post with my next issue which is likely to be creating a secure login, unless there is already a thread on that here that someone might point me to? Great advice, keep it up folks. :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353857 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
Thank you, John! In fact, I will try that as my humble site is in the early-development stage and although I'm learning CF for work (I'm actually a Network Admin, not a Web Dev), I'm also learning as much as I can about it for my own enrichment and to 'round myself out.' I suppose it would have helped if I had mentioned that no repeats was desirable in the original post. :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353860 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
It's because that's the way cfoutput works. Try something like this: cfoutput cfloop index=i from=1 to=#rows# cfset displayRow = randrange(1, rs.recordcount) br /h3#displayRow# #rs.first[displayRow]# #rs.last[displayRow]# #rs.flag[displayRow]#/h3br / /cfloop /cfoutput On Fri, Jan 11, 2013 at 4:17 PM, B Griffith br.griff...@yahoo.com wrote: Hello all! I am new to ColdFusion and web development in general, and have only had cursory training in programming (Python). I have a need for a web page that will accept either an absolute number or percentile from the end-user (I'm still having trouble w/ the 'form' functionality of HTML), and then set the variable (or parameter?) rows to the number chosen, then choose THAT number of records at random from the database (MSSQL). Then I would like to display them (in no particular order) in plain-old HTML, CSS and other stylization is not important at this point, solely the functionality of the page. Here is my code: cfquery name=rs datasource=#application.dsn# SELECT * FROM DONOR /cfquery cfset displayRow = randRange(1,rs.recordcount) cfparam name = rows default = 5 cfoutput query=rs startrow=#displayRow# maxrows=#rows# br /h3#displayRow# #first# #last# #flag#/h3br / /cfoutput The table name is 'DONOR', and the fields I want to display from it are 'first', 'last', and 'flag'. I'm wondering at this point why when I run the page, the output indeed picks a random record to start with, but then displays the next four sequentially instead of choosing the other four at random. I am more than happy to post a screencap of my browser output if need be. Your help is much appreciated as I am quite stumped and I hope you all can point me in the right direction. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353833 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
Because that is what you ate telling it to do. Start from row x, and then output the next 4 rows. If you want to get 5 random rows then you need to create a loop from 1 to 5, inside that loop generate your random number, then get that row. To get a specific row from a query use Queryname[row].column Or it might be Queryname[column name][row] I forget the exact syntax. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 11, 2013 10:17 PM, B Griffith br.griff...@yahoo.com wrote: Hello all! I am new to ColdFusion and web development in general, and have only had cursory training in programming (Python). I have a need for a web page that will accept either an absolute number or percentile from the end-user (I'm still having trouble w/ the 'form' functionality of HTML), and then set the variable (or parameter?) rows to the number chosen, then choose THAT number of records at random from the database (MSSQL). Then I would like to display them (in no particular order) in plain-old HTML, CSS and other stylization is not important at this point, solely the functionality of the page. Here is my code: cfquery name=rs datasource=#application.dsn# SELECT * FROM DONOR /cfquery cfset displayRow = randRange(1,rs.recordcount) cfparam name = rows default = 5 cfoutput query=rs startrow=#displayRow# maxrows=#rows# br /h3#displayRow# #first# #last# #flag#/h3br / /cfoutput The table name is 'DONOR', and the fields I want to display from it are 'first', 'last', and 'flag'. I'm wondering at this point why when I run the page, the output indeed picks a random record to start with, but then displays the next four sequentially instead of choosing the other four at random. I am more than happy to post a screencap of my browser output if need be. Your help is much appreciated as I am quite stumped and I hope you all can point me in the right direction. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353834 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
If you're wanting rows to be sorted randomly, you should be doing this at the database level. That's one of the items RDBMS's are really good at. For SQL Server, check out the post below. http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table On Fri, Jan 11, 2013 at 4:17 PM, B Griffith br.griff...@yahoo.com wrote: Hello all! I am new to ColdFusion and web development in general, and have only had cursory training in programming (Python). I have a need for a web page that will accept either an absolute number or percentile from the end-user (I'm still having trouble w/ the 'form' functionality of HTML), and then set the variable (or parameter?) rows to the number chosen, then choose THAT number of records at random from the database (MSSQL). Then I would like to display them (in no particular order) in plain-old HTML, CSS and other stylization is not important at this point, solely the functionality of the page. Here is my code: cfquery name=rs datasource=#application.dsn# SELECT * FROM DONOR /cfquery cfset displayRow = randRange(1,rs.recordcount) cfparam name = rows default = 5 cfoutput query=rs startrow=#displayRow# maxrows=#rows# br /h3#displayRow# #first# #last# #flag#/h3br / /cfoutput The table name is 'DONOR', and the fields I want to display from it are 'first', 'last', and 'flag'. I'm wondering at this point why when I run the page, the output indeed picks a random record to start with, but then displays the next four sequentially instead of choosing the other four at random. I am more than happy to post a screencap of my browser output if need be. Your help is much appreciated as I am quite stumped and I hope you all can point me in the right direction. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353835 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
Here's an excellent resource for random row generation using SQL. http://www.petefreitag.com/item/466.cfm ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353837 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Newbie question regarding MSSQL queries w/ CF8
I was assuming he was just doing a learn CF exercise. If not, agreed he should do it in SQL Server. On Jan 11, 2013 4:52 PM, Chester Austin chesteraus...@gmail.com wrote: Here's an excellent resource for random row generation using SQL. http://www.petefreitag.com/item/466.cfm ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353838 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
