I'm trying to write a script that will pull a link at random from a database of links. The primary key is sequential (i.e. 1,2,3 etc). But here is the kicker, I want to randomize it by week. So the same link will be displayed for a whole week, then next week, another link is picked randomly to be displayed for the week.

Is it possible to pick a row from a database at random, based on a week/date seed?

You'll need to setup some means of storing the 'random key' that you create and hanging on to it for a week. There is no way of generating a consistent random seed based on week/date (it wouldn't be random otherwise).

I'd just generate the random key, hold it for 7 days, then make a new one and repeat.

Chris

Your table would look like :

CREATE TABLE `rand_links` (
`id` int(11) NOT NULL auto_increment,
`link` varchar(255) default NULL,
`active` set('y','n') default 'n',
`expires` DATE,
PRIMARY KEY (`id`)
) TYPE=MyISAM;


Then, you simply do this (psuedo-code) :

1. Check if there is an active link
If there isn't any active link (active = 'y'), jump to 5
2. Get that that link's expiry date
3. If the expiry date is past your threshold (one week)
4. Set that link's active to 'n', expiry to NULL
5. Choose a new random link (get its id using ORDER BY RAND() LIMIT 1)
6. Set its active = 'y' and expiry to NOW() + whatever your threshold is. If your threshold is one week, expiry would be NOW() + INTERVAL 7 DAY.