How to select question from mysql tables randomly or shuffle it?

Question

JosephHanh 0 Newbie Poster

10 Years Ago

I am working on a quiz app

image 1 shows the index.php page
image 2 shows the first question
image 3 shows the second question
image 4 shows the third question
image 5 shows the result after completing the quiz
image 6 shows the database 'quizzer' and its tables
image 7 shows the 'questions' table
image 8 shows the 'choices' table

THIS LINK CONTAIN ALL THE CODE (and images) I HAVE DONE SO FAR

https://www.mediafire.com/folder/g5ao7f5q0fe6y/quiz

1.Now my question is how to select the question RANDOMLY from 'questions' table along with 'choices' (by adding code to the existing file or create a new one).

2.If user refresh/reload the page before starting ('Start Quiz') or click 'Take Again' after finishing the quiz, the question should appear randomly.

3.Basically I want to change the order of question appearing in the browser each time I refresh.

4.My work so far is mentioned above.........Please help me with this "RANDOM" problem !!

P.S - Will it be possible, by creating a random function in PHP which will check for repeat questions in a session and check for the 'id' of the question and if it is new display it on the page.

If so what should I do and if no then how to do?

mysql php

Edited 10 Years Ago by JosephHanh

2 Contributors
9 Replies
4K Views
2 Days Discussion Span
Latest Post 10 Years Ago Latest Post by JosephHanh

gabrielcastillo 40 Web Developer

10 Years Ago

//Get the total count of question you have in the database
$sql = "SELECT count(id) FROM questions_table";

$max = mysqli_query($connection, $sql);

//Create a random number from zero to the total count of question.
//So if you have 10 question in your DB, than the random number will be anywhere
//From 0-10
$random = rand(0, $max);


//Get question from table where id is created randomly.
$sql = "SELECT * FROM questions_table WHERE id = '$random'";

$results = mysqli_query($connection, $sql);

gabrielcastillo 40 Web Developer

10 Years Ago

I don't know, its your program.. I'm just showing you how I would go about handling the problem with a solution.

Looks like it would fit in your questions.php file. I didn't look at the code until now, so this might be a good place.

gabrielcastillo 40 Web Developer

10 Years Ago

Try this in your questions.php file.

<?php

    //Get the total count of question you have in the database
    $sql = "SELECT count(id) FROM questions_table";
    $max = mysqli_query($connection, $sql);

    //Create a random number from zero to the total count of question.
    //So if you have 10 question in your DB, than the random number will be anywhere
    //From 0-10
    $random = rand(0, $max);

    //Get question from table where id is created randomly.
    $sql = "SELECT * FROM questions_table WHERE id = '$random'";

    //get result
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $question = $result->fetch_assoc();

    //get choices
    $query = "SELECT * FROM `choices`
    WHERE question_number = $number";

    //get results
    $choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

JosephHanh 0 Newbie Poster · Answer 1 · 2014-10-17T11:31:38+00:00

@gabrielcastillo
thanks for the reply...
where should i put this...in questions.php or index.php

JosephHanh 0 Newbie Poster · Answer 2 · 2014-10-18T10:13:45+00:00

@gabrielcastillo
Would you mind looking at the question.php code or the complete folder https://www.mediafire.com/folder/g5ao7f5q0fe6y/quiz

Need help with this badly....

<?php include 'database.php'; ?>

<?php session_start(); ?>

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

    //get total questions
    $query = "SELECT * FROM `questions`";

    //get result
    $results = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $total = $results->num_rows;

    //get question
    $query = "SELECT * FROM `questions`
              WHERE question_number = $number";

    //get result
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $question = $result->fetch_assoc();

    //get choices
    $query = "SELECT * FROM `choices`
              WHERE question_number = $number";

    //get results
    $choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

<html>
    <head>
    <meta charset="utf-8" />
    <title>PHP Quizzer</title>
    <link rel="stylesheet" href="css/style.css" type="text/css" />
</head>
<body>
    <header>
        <div class="container">
            <h1>PHP Quizzer</h1>
        </div>
    </header>
    <main>
        <div class="container">
            <div class="current">Question <?php echo $question['question_number']; ?> of <?php echo $total; ?></div>
            <p class="question">
                <?php echo $question['text']; ?>
            </p>
            <form method="post" action="process.php">
                <ul class="choices">
                    <?php while($row = $choices->fetch_assoc()) : ?>
                    <li><input name="choice" type="radio" value="<?php echo $row['id']; ?>" /><?php echo $row['text']; ?></li>
                    <?php endwhile; ?>
                </ul>
                <input type="submit" value="Submit" />
                <input type="hidden"  name="number" value="<?php echo $number; ?>" />
            </form>
        </div>
    </main>
    <footer>
        <div class="container">
            Copyright &copy; Joe 2014
        </div>
    </footer>
</body>
</html>

JosephHanh 0 Newbie Poster · Answer 3 · 2014-10-18T18:52:09+00:00

this is what i did....the new code are in bold...but i am getting an error "rand() expects parameter 2 to be long....in line...$random = rand(0, $max);"

ok...after doing little research i used
$random = rand(1,count($max) - 1 );

this time no error....but the questions are not selected randomly?

<?php session_start(); ?>

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

    //get total questions
    $query = "SELECT * FROM `questions`";

    //get result
    $results = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $total = $results->num_rows;

    //get question
    $query = "SELECT * FROM `questions`
              WHERE question_number = $number";

    **//Get the total count of question you have in the database
    $sql = "SELECT count(question_number) FROM `questions`";
    $max = $mysqli->query($sql) or die($mysqli->error.__LINE__);**

    **//Create a random number from zero to the total count of question.
    $random = rand(0, $max);**

    **//Get question from table where id is created randomly.
    $sql = "SELECT * FROM `questions` WHERE question_number = '$random'";**

    //get result
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $question = $result->fetch_assoc();

    //get choices
    $query = "SELECT * FROM `choices`
              WHERE question_number = $number";

    //get results
    $choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

gabrielcastillo 40 Web Developer · Answer 4 · 2014-10-18T19:17:26+00:00

That means max is not returning a number. try SELECT count(*) FROM questions

JosephHanh 0 Newbie Poster · Answer 5 · 2014-10-19T08:52:07+00:00

JosephHanh 0 Newbie Poster

10 Years Ago

still not working

JosephHanh 0 Newbie Poster · Answer 6 · 2014-10-19T09:00:32+00:00

thanks for the help so far...but still got stuck..no error...but not working the way i expect...plz help me with this...

this is index.php

<?php include 'database.php'; ?>

<?php
//get total questions
$query = "SELECT * FROM `questions`";

//get result
$results = $mysqli->query($query) or die($mysqli->error.__LINE__);
$total = $results->num_rows;
?>

<html>
     <head>
     <meta charset="utf-8" />
     <title>PHP Quizzer</title>
     <link rel="stylesheet" href="css/style.css" type="text/css" />
     </head>
<body>
    <header>
        <div class="container">
            <h1>PHP Quizzer</h1>
        </div>
    </header>
    <main>
        <div class="container">
            <h2>Test Your PHP Knowledge</h2>
            <p>This is a MCQ to test your knowledge of PHP</p>
            <ul>
                <li><strong>Number of Questions: </strong><?php echo $total; ?></li>
                <li><strong>Type: </strong>MCQ</li>
                <li><strong>Estimated Time: </strong><?php echo $total * .5; ?> Minute(s)</li>
            </ul>
            <a href="question.php?n=1" class="start">Start Quiz</a>
        </div>
    </main>
    <footer>
        <div class="container">
            Copyright &copy; Joe 2014
        </div>
    </footer>
</body>
</html>

this is question.php

<?php include 'database.php'; ?>

<?php session_start(); ?>

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

    //get total questions
    $query = "SELECT * FROM `questions`";

    //get result
    $results = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $total = $results->num_rows;

    //get question
    $query = "SELECT * FROM `questions`
              WHERE question_number = $number";

    //Get the total count of question you have in the database
    $sql = "SELECT count(*) FROM `questions`";
    $max = $mysqli->query($sql) or die($mysqli->error.__LINE__);

    //Create a random number from zero to the total count of question.

    $random = rand(1,count($max)-1);

    //Get question from table where id is created randomly.
    $sql = "SELECT * FROM `questions` WHERE question_number = '$random'";

    //get result
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
    $question = $result->fetch_assoc();

    //get choices
    $query = "SELECT * FROM `choices`
              WHERE question_number = $number";

    //get results
    $choices = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>

<html>
    <head>
    <meta charset="utf-8" />
    <title>PHP Quizzer</title>
    <link rel="stylesheet" href="css/style.css" type="text/css" />
</head>
<body>
    <header>
        <div class="container">
            <h1>PHP Quizzer</h1>
        </div>
    </header>
    <main>
        <div class="container">
            <div class="current">Question <?php echo $question['question_number']; ?> of <?php echo $total; ?></div>
            <p class="question">
                <?php echo $question['text']; ?>
            </p>
            <form method="post" action="process.php">
                <ul class="choices">
                    <?php while($row = $choices->fetch_assoc()) : ?>
                    <li><input name="choice" type="radio" value="<?php echo $row['id']; ?>" /><?php echo $row['text']; ?></li>
                    <?php endwhile; ?>
                </ul>
                <input type="submit" value="Submit" />
                <input type="hidden"  name="number" value="<?php echo $number; ?>" />
            </form>
        </div>
    </main>
    <footer>
        <div class="container">
            Copyright &copy; Joe 2014
        </div>
    </footer>
</body>
</html>

this is process.php

<?php include 'database.php'; ?>

<?php session_start(); ?>

<?php
     //check to see if score is set
    if(!isset($_SESSION['score']))
    {
        $_SESSION['score'] = 0;
    }

    if($_POST)
    {
        $number = $_POST['number'];
        $selected_choice = $_POST['choice'];
        $next = $number+1;

        //get total questions
        $query = "SELECT * FROM `questions`";

        //get result
        $results = $mysqli->query($query) or die($mysqli->error.__LINE__);
        $total = $results->num_rows;

        //get correct choice
        $query = "SELECT * FROM `choices`
                  WHERE question_number = $number AND is_correct = 1";

        //get result
        $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
        //$total = $result->num_rows;

        //get row
        $row = $result->fetch_assoc();

        //set correct choice
        $correct_choice = $row['id'];

        //compare
        if($correct_choice == $selected_choice)
        {
        //answer is correct
        $_SESSION['score']++;
        }

        //check if last question
        if($number == $total)
        {
        header("Location: final.php");
        exit();
        } 
        else
        {
        header("Location: question.php?n=".$next);    
        }
    }
?>

this is final.php

<?php session_start(); ?>

<html>
    <head>
    <meta charset="utf-8" />
    <title>PHP Quizzer</title>
    <link rel="stylesheet" href="css/style.css" type="text/css" />
</head>
<body>
    <header>
        <div class="container">
            <h1>PHP Quizzer</h1>
        </div>
    </header>
    <main>
        <div class="container">
            <h2>Your're Done</h2>
            <p>Congrats! You've Complete the Quiz</p>
            <p>Final Score: <?php echo $_SESSION['score']; 
                                  session_destroy(); ?> </p>
            <a href="question.php?n=1" class="start">Take Again</a>
        </div>
    </main>
    <footer>
        <div class="container">
            Copyright &copy; Joe 2014
        </div>
    </footer>
</body>
</html>