For example, say if you wanted a random result every 10 minutes. Is there a way to achieve this with ORDER BY RAND()?
$fetch = mysqli_query($conn, "
SELECT *
FROM food
JOIN food_images ON food.size = food_images.size
ORDER BY RAND()
");
I also am using a JOIN and worried if this might affect the answers. Thank you!
I don't have a MySQL server in front of me so most of this is a guess, but you might try as follows:
You can generate a number that changes only once every ten minutes by taking the system time in seconds, dividing by the number of seconds in ten minutes, and then casting to an integer:
$seed = (int) (time() / 600);
Then pass this value to MySQL's RAND() function as a parameter to seed the RNG, and you should get a repeatable sequence that changes every ten minutes:
$stmt = mysqli_prepare($conn, 'SELECT ... ORDER BY RAND(?)');
mysqli_stmt_bind_param($stmt, 'i', $seed);
You can do it as:
SELECT *, rand(time_to_sec(current_time()) / 600) as ord
FROM food
JOIN food_images ON food.size = food_images.size
order by ord
The parameter of the RAND() function is the seed. The expression in it, changes only every 10 minutes.
You can use MySQL Event Scheduler and as described in the documentation:
you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time
And I guess you are using php so You can use PHP Cron jobs too , Managing Cron Jobs With PHP