CODEWITHSUNDEEP
phpmysql
Nathaniel
Nathaniel

Reputation: 267

Random Shuffle without getting same results again

id    |  photo title     |  created_date

33 |  my family       |  2009 8 04
44  |  friends group   |  2009 4 05
55  |  beautiful place |  2009 3 06
66  |  working late    |  2009 11 07

I randomized my results and limited by 1. Say I have the id 55. How would I get the next row without getting the results I got again?

This is the query for the homepage

SELECT * FROM data ORDER BY rand() LIMIT 1

For the next pages I might get the same results again

SELECT * FROM data WHERE id > $n ORDER BY rand() LIMIT 1

Upvotes: 0

Views: 356

Answers (3)

Lix
Lix

Reputation: 47966

Well, one option would be to provide the results you have already selected in the where clause:

SELECT * FROM data WHERE `id` NOT IN ('the', 'ids', 'you', 'already', 'selected', ...) ORDER BY rand() LIMIT 1

However this approach will quite rapidly become inefficient as more and more "used ids" get added to the NOT IN list. This will always be a problem when dealing with random results.

You might want to consider storing the used id values in a separate table and using the contents of that table (possibly indexed by user_id) for specifying the already used results.

Upvotes: 0

mzedeler
mzedeler

Reputation: 4369

Reuse a seed that you generate in PHP.

Pseudo code:

$seed = rand()
$stmt = $mysqli->prepare("SELECT * FROM data ORDER BY RAND(?) LIMIT 1");
$stmt->bind_param($seed);
$stmt->execute()
...

And then when you need the rest of the rows:

$stmt = $mysqli->prepare("SELECT * FROM data ORDER BY RAND(?) LIMIT 2,1000");
$stmt->bind_param($seed);
$stmt->execute()
...

See more about seeding RAND() in the mysql manual.

The advantage of this approach is that you can display any number of pages without having to keep track of anything but the initially generated seed (and of course which page you're on).

Upvotes: 3

Robert
Robert

Reputation: 20286

You didn't write what engine you use. Assuming it's pdo you can use fetchAll() on the results and pass the result of fetchAll() to session via $_SESSION;

$sth = $dbh->prepare("SELECT * FROM data ORDER BY rand() ");
$sth->execute();
$_SESSION['rnd_res'] = $sth->fetchAll();

If you write LIMIT 1 there will be no other records just one. You can also remember the last id or groups of id and write

SELECT * FROM data WHERE id<>$last_id ORDER BY rand()

this will exclude the last id you used, this id may be stored in $_SESSION as well.

Last option for group of results may be NOT IN() in query;

SELECT * FROM data WHERE id NOT IN($array_of_last_ids) ORDER BY rand()

Upvotes: 0

Related Questions