php mysql return variable amounts

Question

Let me start off first by saying I code php as a hobby and don't know a whole lot. That be said I'm having trouble with coming up with a clever way to perform a particular type of query.

Say I have dynamic quiz application that uses a database of test questions of three different subjects; 200 algebra, 100 history, 50 science. The user can select which and how many subjects they wish to have in their quiz and also choose how many questions that will be in the quiz.

Below is the working code I'm using now...

$sql = "SELECT *
        FROM quiz
        WHERE keyword LIKE '$algebra'
        OR keyword LIKE '$history'
        OR keyword LIKE '$science'        
        ORDER BY RAND() LIMIT $amount";

However the only issue with this code is that if I select 10 questions on my test I will have mostly algebra questions, a few history and no science questions. I'd like it so each subject selected is represented equally.

I think I might have bitten off more than I can chew with this project.

JBA · Accepted Answer · 2015-02-21 23:17:54Z

1

Just a guess:

$equal = $amount/3; // do your best to round this...
$sql = "SELECT * FROM quiz WHERE keyword LIKE '$algebra' ORDER BY RAND() LIMIT $equal
UNION
SELECT * FROM quiz WHERE keyword LIKE '$history' ORDER BY RAND() LIMIT $equal
UNION
SELECT * FROM quiz WHERE keyword LIKE '$science' ORDER BY RAND() LIMIT $equal";

answered Feb 21, 2015 at 23:17

JBA

2,9091 gold badge24 silver badges40 bronze badges

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

2 Comments

Virendra Over a year ago

This will work only if the number of questions selected are less than 150. If the user selects more than 150 questions, then this query will not return the expected number of results, since there are only 50 science questions.

JBA Over a year ago

The maximum number to retrieve is another problem you could manage before the sql query by other means like knowing what keyword has the less records then calculate your number to retrieve according to this

Thomas C. · Accepted Answer · 2015-02-21 23:26:24Z

0

@poozolax gave you a precise solution. You could get another one trying to compensate distribution by doing something like:

$sql = "SELECT * FROM
        (SELECT *,
        case 
             when keyword like '$algebra' then 350/200
             when keyword like '$history' then 350/100
             when keyword like '$science' then 350/50
        end * RAND() as ORD
        FROM quiz
        WHERE keyword LIKE '$algebra'
        OR keyword LIKE '$history'
        OR keyword LIKE '$science'        
        ) AS X ORDER BY ORD DESC LIMIT $amount";

I have no idea if default RAND() gives it's best to distribute evenly, however a tuning coefficient as 350/50 may give better chance to science.

edited Feb 21, 2015 at 23:26

Thomas C.

3,7065 gold badges38 silver badges42 bronze badges

answered Feb 21, 2015 at 23:22

Srdjan

1087 bronze badges

Collectives™ on Stack Overflow

php mysql return variable amounts

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related