PHP fetch unique random value from database

Question

I've been working with PHP quiz program and I'm a bit lost on fetching unique random value from database. Currently here is my script.

<?php $num = 1;
    $sql = "SELECT * FROM questions ORDER BY RAND() LIMIT ".$num;
    $sql_exec = mysql_query($sql, $connection);
    if(isset($_POST['start']) || isset($_SESSION['xy'])){
        while($row = mysql_fetch_array($sql_exec)){
            if(!in_array($row['qid'], $_SESSION['xy'])){
                echo $row['qid']." -".$row['question']."<br />";
                if(isset($_POST['num'])){
                    $_SESSION['xy'][] .=  $row['qid'];
                }
            }else{
                // WHAT WILL I PUT HERE
            }
        }
    } ?>

<form action="<?php $_SERVER[PHP_SELF]; ?>" method="post">
    <input type="hidden" name="num" value="1" />
    <input type="submit" name="start" value="Start" <?php if(isset($_POST['start']) || isset($_SESSION['xy'])) echo "disabled"; ?> />
    <input type="submit" name="submit" value="Continue" />
    <input type="submit" name="destroy" value="Destroy" />
</form>

I put the id of each row in a session array so that I can records all the previous question and that I can compare if the new question is already ask using in_array.

The problem is if the new fetch data is already in array, the program stops because I still have no else value,

[idea-01] I'm thinking of putting a new select statement on else, but I know it's wrong because it's inside a loop and there's no assurance that the value will be unique.

[idea-02] With the array records of row ids I have in the session, im thinking of putting a where condition on the select above

WHERE qid != $_SESSION['xy']

The problem is I have to loop this session to compare each values to the statement. Also the questions is 20 items and it can be extended in the future.

Christian Smorra · Accepted Answer · 2011-07-04 23:58:03Z

1

I think what you are looking for is the in comparison function of mysql.

$mysql_query="SELECT * FROM questions WHERE question NOT IN (".implode(",",$_SESSION["anwsered_questions"]).") ORDER BY RAND();";

Make sure there are only numeric values stored in $_SESSION["anwsered_question"] otherwise this query would be vulnarable to MySQL injections

Also: Don't use ORDER BY RAND(), see http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ for more info.

answered Jul 4, 2011 at 23:58

Christian Smorra

1,75411 silver badges13 bronze badges

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

1 Comment

Jess Over a year ago

he could use array_map('mysql_real_escape_string', $_SESSION["anwsered_questions"]) for the injections, but that is a hard performance hit. I would just check to make sure that nothing can go in the session, except numeric values (check before you insert it into the session).

Collectives™ on Stack Overflow

PHP fetch unique random value from database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related