Display random SQL record every week using php

Here are some possible solutions:

Seed your rand with the week number

In php, do

$seed = intval(date('oW')); # o will return the "Week year", which is the year the current ISO week mostly corresponds to. W will return the ISO week number

And use the sql statement

SELECT * FROM catalogue order by RAND(?) LIMIT 5

And bind this value to $seed, resulting in something like

SELECT * FROM catalogue order by RAND(201751) LIMIT 5

This will seed your rand, meaning all RAND() calls with the same seed (which is your week number and week-year) will be the same.

Seed your rand with a week number entirely in SQL

SELECT * FROM catalogue ORDER BY RAND(WEEK(NOW())) LIMIT 5

Cache your data in a static file for a week

function getDataOncePerWeek(
    $static_cache_file = 'your/cache/dir/your_cache_file.json';
    $start_of_week = strtotime('this week 00:00:00');

    if (!file_exists($static_cache_file) ||  filemtime($static_cache_file) < $start_of_week) {
        touch($static_cache_file); // Just update the last changed time, so we only try to re-cache the file once
        $tmpFileName = tempnam(pathinfo($static_cache_file, PATHINFO_DIRNAME), 'tmp_');
        $data_from_your_sql = queryAndFetchYourSqlData(); //implement this yourself for your query
        file_put_contents($tmpFileName, json_encode($data_from_your_sql), LOCK_EX); // Save your new cache to a temporary file
        rename($tmpFileName, $static_cache_file);
    }
    return json_decode(file_get_contents($static_cache_file), true);
}

This solution can also be used to generate html from you sql data, and save this to the cache instead. That is, if your html block doesn't need to change during this week.

Cache your data in a database

Set up a caching table which has the same definition as catalogue, but also has a timestamp column which you can look at instead of filemtime() to determine if you need to generate your cache.

You could store the week number in another table in the database together with the record, and then every time it runs the file, it makes sure that Date(W) is not the same as whats in database, if it is, then it just gets the data in there already, if it is not, then it updates with the new data from the other function

You can do this using only SQL.

Get the current week number (using WEEK(CURRENT_TIMESTAMP())) and use it as a seed to your random function

ORDER BY RAND(WEEK(CURRENT_TIMESTAMP()))

Documentation: WEEK, CURRENT_TIMESTAMP(), RAND