Re: random records no duplicates
Either keep a list of questions already asked and query again if it tries to give you one you've had before or keep a a copy of the query for each user and remove the row after you ask each question. On 3/29/12 1:21 AM, Akos Fortagh wrote: hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated. ~| 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:350580 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: random records no duplicates
Another way is to leave the already asked questions out of your new query each time: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions WHERE questions.questionid NOT IN (#listOfPreviouslyAskedQuestions#) /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput On 3/29/12 1:58 AM, .jonah wrote: Either keep a list of questions already asked and query again if it tries to give you one you've had before or keep a a copy of the query for each user and remove the row after you ask each question. On 3/29/12 1:21 AM, Akos Fortagh wrote: hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated. ~| 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:350582 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: random records no duplicates
There are more efficient ways of getting a single random record from the database. You are grabbing them all every time when you only need 1. SQL Server: SElECT TOP 1 q.questionid, q.questiontext FROM questions q ORDER BY NEWID() MySQL SELECT q.questionid, q.questiontext FROM questions q ORDER BY RAND() LIMIT 0,1 As for not showing a user the same question twice, you could store the questionIDs that they have seen in their session (or the questionIds that they havent seen... your call) and then filter those from your possible results. 1: Get a random record they havent seen SELECT TOP 1 q.questionid, q.questiontext FROM questions q WHERE q.questionId NOT IN cfqueryparam cfsqltype=cf_sql_integer value=#session.listOfUsedQuestionIds# list=true /) ORDER BY NEWID() 2: Add it to the list of questions they have seen listappend(session.listofusedquestions, thisQuestionQuery.questionId) 3: Display the question. .:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Akos Fortagh [mailto:akos.fort...@yahoo.com] Sent: Thursday, March 29, 2012 4:22 AM To: cf-talk Subject: random records no duplicates hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated. ~| 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:350583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: random records no duplicates
Agreed. Simple to store a list of already asked questions in the session scope. andy -Original Message- From: .jonah [mailto:jonah@creori.com] Sent: Thursday, March 29, 2012 4:12 AM To: cf-talk Subject: Re: random records no duplicates Another way is to leave the already asked questions out of your new query each time: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions WHERE questions.questionid NOT IN (#listOfPreviouslyAskedQuestions#) /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput On 3/29/12 1:58 AM, .jonah wrote: Either keep a list of questions already asked and query again if it tries to give you one you've had before or keep a a copy of the query for each user and remove the row after you ask each question. On 3/29/12 1:21 AM, Akos Fortagh wrote: hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: cfquery name=getquestion datasource=#request.DSN# SELECT questions.questionid, questions.questiontext FROM questions /cfquery cfset displayRow = RandRange(1,getquestion.recordcount) cfoutput query=getquestion maxrows=1 startrow=#displayRow##questiontext#/cfoutput What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated. ~| 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:350584 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: random records no duplicates
Massive thanks to everybody who helped especially to Bobby for an exact example, all works great. thanks again ~| 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:350585 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
