CODEWITHSUNDEEP
phpsqlrandom
Izumi
Izumi

Reputation: 571

MySQL: Random pick that lasts for a specific period of time?

Okay... I'm lost (again). How can I make a custom seed for rand() that will keep the pick from the database for a specific period of time: 10 minutes, 1 hours, 1 day, etc.

I use this:

$query = 'SELECT * FROM table order by rand () LIMIT 1';

To get a random pick every time I refresh the page. I've been reading for days, but I just can't find an example that explains how to make a custom seed that will keep the selection for a specific period of time. Please help me... sighs

Upvotes: 1

Views: 788

Answers (7)

brozo
brozo

Reputation: 596

If you're working with PHP, you can make an algorithm, which generates a random number (using rand()) and then save this number somewhere in the database or file. Then check the current date and the date of generation of the random number. Calculate the difference and use an if statement to determine, whether it's time to generate random number again. Here's some pseudo-code (note that by date I mean a timestamp or Unix time):

$gendate = getGenerationDateFromDB();
$now = getCurrentDate()
if(getDifference($gendate, $now) > [time interval]) then
  $randnum = generateRandNumber();
  saveRandNumberInDB($randnum);
  saveGenDate();
else
  $randnum = getRandNumberFromDB();

Upvotes: 1

naugtur
naugtur

Reputation: 16915

As Dominic Rodger said - order by rand is horrible. But I find using cron too big a gun for the job. (especially that You don't know how)

You should rather generate a random integer number in php and store it and pass it to the mysql to something like that:

SELECT * FROM table WHERE id=(MOD('.$php_generated_random.', LAST_INSERT_ID())+1)

it gets a big random number and does modulo, so that it doesnt go over the table length.

This has a problem with LAST_INSERT_ID() though. Your mysql may return 0 there in many cases.

So the good'n'safe way to do that would be:

  1. check if it's time to generate new random
  2. if so, then get the max id from table (assuming that table length changes sometimes)
  3. generate a random number to be an id
  4. store the id and generation time

and do only select where id=$sth

Upvotes: 1

Yada
Yada

Reputation: 31225

"Create a custom seed"

I believe the question wants you to do something like this:

$query = 'SELECT * FROM table order by rand (' . get_seed() . ') LIMIT 1';

<?php
// return the current hour.  so the seed is the same each hour
function get_seed() {
  return date('H');
}
?>

The purpose of a 'seed' is to create the same random sequence for testing purposes.

RAND(N) If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

Upvotes: 0

Notinlist
Notinlist

Reputation: 16640

I have an idea for the concept, not a formal solution:

Use md5() on the down-rounded UNIX_TIMESTAMP()/60. You have a number of records in the table, let it be X. Calculate a number from the md5(), let it be Y. Calculate the Y % X, let it be Z. Use limit 1 offset Z at the end of the SQL.

Upvotes: 1

Tomalak
Tomalak

Reputation: 338208

You could create a temp table:

SELECT * INTO #temp FROM table ORDER BY rand() LIMIT 1

Then query the temp table to display your data. Drop/re-create the table after your timeout.

Upvotes: 1

Dominic Rodger
Dominic Rodger

Reputation: 99761

ORDER BY RAND() is a really bad idea - it kills database performance.

This is the sort of thing you should run on a cron job as often as you want the "random pick" to last, and store the ID of the row that is your random pick of the minute/hour/day. You'll need to watch out to make sure that row doesn't get deleted though.

Upvotes: 0

Sarfraz
Sarfraz

Reputation: 382696

Once you have selected it, you need to store that data into the session and session can be set to the time you specify. No need to query to the database every time. And secondly query has nothing to do with setting the time for an ad/whatever for specified time.

Upvotes: 0

Related Questions