RAND() function… row selection without duplication

RAND() function… row selection without duplication

Hello, I am currently using the RAND() function to retrieve individual rows of data from a MySQL database. Even though there are more than 150 rows of data I am seeing certain rows appear more than once.

My goal is to randomly select a row of data without duplication until all rows have been displayed. Access to the site is anonymous with no retention of cookie data so each visit is a new start.

The code I am currently using is below. Is my requirement possible with RAND()?


//get question list
$qData = $_sql->selectToArray("*", $__tablename, "`level` = '".$qLevel."'", "`id` ASC", ""); $qDataLength = count($qData);

$currentQuestion = ($qRight+1);

//get random question id
$qNumber = rand(0, ($qDataLength-1));

//get question
$question = $qData[$qNumber];

Thanks.

 

you need to look at SELECT UNIQUE

 

For MySQL, it's actually DISTINCT.

 

You win nico

 

Thanks for the help gentlemen.

nico_swd, any suggestions on how to modify the code I displayed using the DISTINCT funciton?

I had someone else dev the code and this goes beyond my skill level.

 

The problem here isn't with your SQL or duplicate rows of data, it is with the PHP code that selects the random row. You aren't removing the rows you have previously selected, so they can get selected again.

You could keep track of questions that have already been displayed in an array, and then keep looping until you get one you haven't seen:

$displayedQuestions = array();

$qNumber = -1;
while($qNumber < 0 || in_array($qNumber, $displayedQuestions)
{
    $qNumber = rand(0, ($qDataLength-1));
}
$displayedQuestions[] = $qNumber;

PHP:

That assumes that you want to display all the 150 rows on the same page, all at once, in a random order. If that isn't the case then you'll need to preserve the $displayedQuestions array between page displays showhow, either using $_SESSION or some other method.

 

You can add a column to the table to save if the joke has been displayed or not.
Then select all the jokes not yet displayed, pull any of them and then change its status to displayed.
Continue this until you've displayed all the jokes and then reset the status.

Alternatively you really don't need to randomize anything, you could always just display the jokes sequentially, which would appear random to the ordinary user. And just save the previous column somewhere in a file or settings table.