MySQL randomly selected questions

Question

I can't find what I need. I trying to create at quizzer that takes questions from the database. Currently, it takes all the questions in the database and what I need it to take it randomly 10 questions. I am just starting to learn how to code.

Here are my getting questions code with answers:

<?php
    //Set question number
    $number = (int) $_GET['n'];

    /*
    *   Get total questions
    */
    $query = "SELECT * FROM `klausimai`";
    //Get result
    $results = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $total = $results->num_rows;

    /*
    *   Get Question
    */
    $query = "SELECT * FROM `klausimai`
                WHERE question_number = $number";
    //Get result
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);

    $question = $result->fetch_assoc();

    /*
    *   Get Choices
    */
    $query = "SELECT * FROM `atsakymai`
                WHERE question_number = $number";
    //Get results
    $choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

`SELECT * FROM tbl ORDER BY RAND() LIMIT 10;` you can try query like this — Pankaj Makwana, May 08 '18 at 06:16
Put the condition whatever questions required, Above query will give you random records with 10 questions — Pankaj Makwana, May 08 '18 at 06:22
above query will give you 10 random records as you mentioned in your question. change the table name with your — Pankaj Makwana, May 08 '18 at 06:28
I did change it to: $query = "SELECT * FROM `klausimai` WHERE question_number = $number ORDER BY RAND() LIMIT 10"; — Lukas Šliužas, May 08 '18 at 06:36
Does the `question_number` column have same number question or it is an unique number? — Pankaj Makwana, May 08 '18 at 06:46
question_number is used to link question to their correct answer. So if I delete quesion nr.1 then spot nr.1 will be open for another question. — Lukas Šliužas, May 08 '18 at 06:53
What fields from the tables "klausimai" and "atsakymai" actually get used? How many questions are and what's the most questions that there will ever be? — SpacePhoenix, May 08 '18 at 06:59
klausimai has two field question_number and text. "atsakymai" has four field id, question_number, text and is_correct. I managed to get questions randomly. There will be about 100 question: 50 from common 25 from loading 25 from work safety. Max questions in one quiz is 10. Right now I am trying figure out how to do questions correct order. If question 25 is picked it would say question 1 and etc — Lukas Šliužas, May 08 '18 at 07:12

score 1 · Accepted Answer · answered May 08 '18 at 06:39

As mentioned above, limit the klausimai query results to 10 by using the SQL LIMIT statement ($query = "SELECT * FROM klausimai LIMIT 10";):

<?php
//Set question number
$number = (int) $_GET['n'];

/*
*   Get total questions
*/
$query = "SELECT * FROM `klausimai`  LIMIT 10";
//Get result
$results = $mysqli->query($query) or die($mysqli->error.__LINE__);
$total = $results->num_rows;

/*
*   Get Question
*/
$query = "SELECT * FROM `klausimai`
            WHERE question_number = $number";
//Get result
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

$question = $result->fetch_assoc();

/*
*   Get Choices
*/
$query = "SELECT * FROM `atsakymai`
            WHERE question_number = $number";
//Get results
$choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

I did limited but it will take first 10 question. And I need to be completely random. — Lukas Šliužas, May 08 '18 at 06:47
To get random records one needs to know the structure of the klausmai table. Check this topic: https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast — Alexandar Penkin, May 08 '18 at 12:26

hotheadhacker · Answer 2 · 2018-05-08T08:27:51.977

1

Your Code good to go, but to print 10 random question fetched directly from your mysql database, Execute the following query :

 $query = SELECT * FROM `klausimai`
ORDER BY RAND()
 LIMIT 10;
while(row=mysql_fetch_array($query) {

echo $row['question_feild'];
//Replace 'question_feild' it with your question_body column of your table 

//assuming you have 2 choices for a particular question 

echo $row['choice1']; // Same replace it with your choice/answer columns
echo $row['choice2']; // Same replace it with your choice/answer columns 

}

edited May 08 '18 at 08:27

answered May 08 '18 at 06:42

hotheadhacker

182
10

you want to print 10 random questions from your database from a particular key i.e, "$number"? – hotheadhacker May 08 '18 at 06:56
So I managed to fix that but now question order isn't correct. – Lukas Šliužas May 08 '18 at 06:58
Yes it is random but anwser aren't tied to question. Question will be 26 but answers will be from first question – Lukas Šliužas May 08 '18 at 07:30
it is because you are running the fetch answer query sepratly! – hotheadhacker May 08 '18 at 07:59
I have modified your code have a look see if it works. I guess it will work now! as i have printed both questions and choices simultaneously! – hotheadhacker May 08 '18 at 08:13

Sohrab Yousefi · Answer 3 · 2018-05-08T07:55:28.757

0

Try this :

/*
 *  Get Questions
*/
$query = "SELECT * FROM `klausimai` ORDER BY RAND() LIMIT 10";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_assoc($result)){
    echo "Question number " . $row["question_number"] . "<br/>";
    echo "Choices of question are:<br/>";
    /*
     *  Get Choices
    */
    $query = "SELECT * FROM `atsakymai` WHERE `question_number` = " . $row["question_number"];
    $result = mysqli_query($conn, $query);
    $i = 1;
    while($row2 = mysqli_fetch_assoc($result)){
        echo $i.") ".$row2["choice_text"]."<br/>";
        $i++;
    }
}

edited May 08 '18 at 07:55

answered May 08 '18 at 06:45

Sohrab Yousefi

116
5

What are you looking for exactly ? 10 random questions with their answers ? If that's the case, so what is the variable `$number` for ? – Sohrab Yousefi May 08 '18 at 07:11
Yes I am looking for 10 random questions with their answers. $number is used get correct questions answers – Lukas Šliužas May 08 '18 at 07:32

MySQL randomly selected questions

3 Answers3