PHP MySQL: GET new way to query SQL

Question

I have a question game quiz in my SQL query as

question 1 to 10
I use sql random query to select question
use array push to keep previous question
use sql query [query from array push] to select new question (no more previous question show up)

FIRST QUESTION START

1: SELECT * FROM questions WHERE catid="9" ORDER BY RAND() LIMIT 0 , 1
2: SQL query get id=2 so array push = (2)

SECOND QUESTION START (if answer correct)

1: SELECT * FROM questions WHERE catid="9" AND ( id <> "2" ) ORDER BY RAND() LIMIT 0 , 1
2: SQL query get id=5 so array push = (2,5)

THIRD QUESTION START (if answer correct)

1: SELECT * FROM questions WHERE catid="9" AND ( id <> "2" AND id <> "5" ) ORDER BY RAND() LIMIT 0 , 1
2: SQL query get id=1 so array push = (2,5,1)

... QUESTION START (if answer correct)
1: ....
2: ....

So I just want to know if I have 1000 questions so my SQL query going to:

SELECT * FROM questions WHERE catid="9" AND ( id <> "2" AND id <> "5" AND id <> "1" AND .... AND id <> "999" ) ORDER BY RAND() LIMIT 0 , 1

This should be slow for page process or not?.

If slow, should we have another way to process this query? thanks....

I don't think it is slowing the page that much... I thought you have a problem and not just asking for opinion. My bad. — peaceman
– peaceman, Commented Mar 24, 2016 at 8:43
First of all, change this: id <> 2 and id <> 5 and... to this: id NOT IN (2,5,1...) and you can store them as an array and check against it. — sagi
– sagi, Commented Mar 24, 2016 at 8:47
1000 is really not that much in database terms. 100 000, still not that much. Maybe a bigger concern would be getting query size limits errors with this approach. However doing something akin to this is probably unavoidable based on your requirements (unless you're willing to create a temporary table for each user that keeps track on unasked questions only). — apokryfos
– apokryfos, Commented Mar 24, 2016 at 8:55

gavgrif · Accepted Answer · 2016-03-24 09:00:12Z

1

What about getting the entire array of questions at the start, and choosing a random question from that (using a random number times the length of the array) and after asking it - either just remove it from the array or move it to a different array - maybe"questionsAsked" or something?that way you do not need to continually keep adding to the list of exclusions - you simply select from the remaining questions? and the questionsAsked array keeps the list of the asked questions.

answered Mar 24, 2016 at 9:00

gavgrif

15.5k2 gold badges30 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlie Carlaw · Accepted Answer · 2016-03-24 10:02:08Z

0

Push the id of the previous question to an array.

$array[] = $previous_id;

Select a random question with id not in $array.

$ids = join(',',$array);

$sql = "SELECT * FROM questions WHERE catid="9" AND id NOT IN ($ids) ORDER BY RAND() LIMIT 1";

Repeat this process while the answer is correct.

edit : Also, you should properly sanitize the inputs to prevent SQL injection

edited Mar 24, 2016 at 10:02

Charlie Carlaw

153 bronze badges

answered Mar 24, 2016 at 9:35

Adersh

5981 gold badge9 silver badges22 bronze badges

1 Comment

verhie Over a year ago

NOT IN ($array) should be NOT IN ($ids)

Zamrony P. Juhara · Accepted Answer · 2016-03-24 09:07:13Z

0

I have not tried it but you can use DISTINCT to query different values.

SELECT DISTINCT id, catid, ... FROM questions WHERE catid="9" ORDER BY RAND() LIMIT 10

answered Mar 24, 2016 at 9:07

Zamrony P. Juhara

5,2822 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

PHP MySQL: GET new way to query SQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related