CODEWITHSUNDEEP
phpmysqli
valek
valek

Reputation: 1376

MySQL - Selecting random row without repeating

I have a website that gets a random movie from database and then shows it to you. I want to select it without repeating(showing one movie few times while other movies aren't shown) and have it done in the fastest way.

This is what do I have:

$db = new mysqli($DBhost, $DBuser, $DBpassword, $DBdatabase);
$offset_result = " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM database";
$result = $db->query($offset_result);
$offset_row = $result->fetch_object();
$offset = $offset_row->offset;

$db = new mysqli($DBhost, $DBuser, $DBpassword, $DBdatabase);
$query = "SELECT id, title, year, front_image, category, watched, description, imdb_code, bg_image, scene_warning FROM database LIMIT $offset,1";
$stmt = $db->query($query);
list($id, $mtitle, $myear, $front_image, $category, $watched, $description, $imdb_code, $bg_image, $warning)=$stmt->fetch_row();

That is selecting but with repeating... Without repeating, I have this but it's slow.

$db = new mysqli($DBhost, $DBuser, $DBpassword, $DBdatabase);
$query = "SELECT id, title, year, front_image, category, watched, description, imdb_code, bg_image, scene_warning FROM a358_filmovi WHERE id NOT IN (".$movies_array.") ORDER BY rand() LIMIT 1";
$stmt = $db->query($query);
list($id, $mtitle, $myear, $front_image, $category, $watched, $description, $imdb_code, $bg_image, $warning)=$stmt->fetch_row();

$movies_array is imploded session array which contains movie IDs.

So, the question is, how to make a query same as first one (the fastest one) but with an array that contains IDs so movies won't repeat?

Upvotes: 0

Views: 1496

Answers (1)

user3756683
user3756683

Reputation:

Why not pull all your ids first into a session array, choose a random index, look it up, then delete that index from the session array?

Upvotes: 4

Related Questions