CODEWITHSUNDEEP
phpmysqlsqlrandom
superkayrad
superkayrad

Reputation: 178

php mysql return variable amounts

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.

Upvotes: 2

Views: 71

Answers (2)

Srdjan
Srdjan

Reputation: 108

@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.

Upvotes: 0

JBA
JBA

Reputation: 2909

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";

Upvotes: 1

Related Questions