I am developing a quiz website, and I have a database which stores all of the questions. There are different types of quizzes, like math, science, history, etc. All of the questions are stored in one table.
My questions table looks like this:
questions ( qno(int) ,type(int), question, .... ,... )
qno
is the primary key, and type
is used to keep track of the quiz type.:
if type = 1 (math)
type = 2(science)
Now, I want to select some random questions for each type of test. For example, I may want to select some random 20 questions for only the math test.
Does MySQL have some way to select random rows?
You can use the rand
function in MySQL to order the rows, and then take the top 10 (or however many you want) with limit
.
select * from table order by rand() limit 10
If you want just the math questions:
select * from table where type = 1 order by rand() limit 10
Add a column to your table that will hold a UNIX timestamp.
Once a day or any timeframe which works for you, you run a query that updates that column.
In this case your query should run at midnight and look something like this.
UPDATE table SET rand_id = (UNIX_TIMESTAMP() + (RAND() * 86400));
Then to retrieve the rows, you use a query similar to this.
SELECT * FROM table WHERE rand_id > UNIX_TIMESTAMP() ORDER BY rand_id ASC LIMIT 20
Using a column with a pre-determined random value saves you needing to run a randomization function for every single row in the table for every single request to the page.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With