I am working on creating a quiz website. I will have 500 questions (which in future will change in quantity) in a db table. The questions will be segregated into 5 categories Chemistry, physics, biology, maths and History. Here each category has 100 questions. My db table will have following fields as Q.no, questions, category and answer. When a user opens my site and starts a quiz test he will be presented with 5 questions. The five questions will be from the five categories. If the same user now closes my site and reopens it, or some one else opens the site then the questions which were displayed previously should not be displayed again instead I need to fetch another 5 random questions from db table. The above description is outline of what i need.. Here comes what i have tried: I have tried the following query to display questions of different category.Here is the query SELECT QC.QuestionCategoryId, QC.Category, Q.QuestionId, Q.Questiontext FROM QuestionCategories QC (NoLock) LEFT JOIN Questions Q (NoLock) ON Q.QuestionCategoryId = QC.QuestionCategoryId WHERE Q.QuestionId IN ( SELECT TOP 1 QuestionId FROM Questions Q (NoLock) WHERE Q.QuestionCategoryId = QC.QuestionCategoryId ORDER BY Q.QuestionId DESC ) ORDER BY QC.QuestionCategoryId, Q.QuestionId DESC When I run this query, questions from different categories are displayed. But if my user reloads/chooses to write the exam again, how could I pick up another different set of questions from the different categories and no questions should be repeated from already finished questions set. plz anyone help me to do this... Thanks in advance, illiana
here's an idea.. why not u store the id of the last question showed for each category.. then when u want to display questions, just refer first to these last answered id, and take the next question.. sounds simple isnt it?
If you do that, you'll quickly run out of questions if 100 users visit your site. Assuming you can identify each user as being different, then... You want to use a query that will take a random question from each category. You should then use another table and store the UserUniqueID (whatever that is), the categoryID, and the questionID when the questions are presented. When the person revisits, you would do the same thing, but you would also use a query that only chose random questions they hadn't been asked. By doing it this way you would deal with 4 problems that are likely to arise.. 1. What happens if you can't identify that it is a previous visitor who has come back - at least with the questions being random there is a chance they will get a different set of questions each tmie 2. Different users will get a list of random questions so you don't need to worry about what has been displayed to previous visitors as it's unlikely a different visitor will get the same list of 5 questions (but a 1 in 100 chance that one of the questions will be the same as the previous visitor). 3. By incrementing the questionID each time you will run out of questions by the 100th visitor 4. Adding more questions or categories at a later time won't affect how the system works so your system is more flexible. Finally, if it is MS SQL Server you are using, you really should be using stored procedures and views to carry out this task. It will be quicker, it can quickyl and easily be transacted and rolled back in the case of errors, and it will put less strain on the servers involved. Something else just occurred to me. You need to keep the table where the UniqueUserID, CategoryID, and QuestionID are stored a clean as you can or it will quickly become massive if there is a lot of visitors. You need to decide how to clean this table - on a date/time basis (don't forget to add a field for the data time), when a user no longer uses the system, or some other criteria that makes sense to how you want the system to work. If you let tables get filled with information that is no longer useful then you will slow the whole system down over time and it will be no fun to use.