Displaying a random "database row" daily. i.e Word of the Day

[eluser]tochie[/eluser]
Hello Brethren,

I'm sorry this is off point but i dont know where else to turn to.

I have this problem that i cant really figure out an approach to. I'm making a language dictionary website with CI and i want to display Word of the Day on the home page. I want only one word definition displayed for that day and then the next day, another one displays and so on.

The problem here is that i cant figure out how to "tell" my script to set a database row as the row to display for that day. I dont want to manually select a row. Another way to look at the problem is say..a mailing list where you set a mail to go out on a particular day. I cant figure out how the script(javascript or whatever) checks the date and know when to send the mail.

Please i need someone to show me an approach then i can develop the codes myself.

thanks ppl.

[eluser]rogierb[/eluser]
Hi,

you can do this several ways:
1: count al rows and then with php get a random id.
2: Mysql: "SELECT column FROM table ORDER BY RAND()LIMIT 1"

[eluser]Dam1an[/eluser]
The getting a random row part is easy
All you need is a select with a random ordering and a limit of 1 (see here)

As for making it the same word for everyone all day, you'd probably have to write it to a temporary table in your database
The easiest way to update this at the start of every day, would be to run a cron job at midnight which picks a random row, and replaces the ID in the word_of_the_day table (you could of course copy the whole row if you want, but it would break 3NF)

[eluser]tochie[/eluser]
Thanks Rogierb,

This is what I mean, lets say there are 3 rows with values(john, pol, you). Now i want that on monday(or any day) if it randomly picks 'pol', then throughout that day, it should display only 'pol'. The next day, it should pick any other word and display only it througout that day. Now i want it to pick it automatically without me going online to click a button to run the script.

I hope i explained it enough.

[eluser]xwero[/eluser]

SELECT column FROM table ORDER BY RAND() LIMIT 1

Store this value for a day an do a cron to select another word. To prevent words showing up twice before the dictionary has been run through totally you can save the already shown words and use a NOT IN where condition.

edit : i'm getting old

[eluser]Dam1an[/eluser]
[quote author="rogierb" date="1242230247"]1: count al rows and then with php get a random id.[/quote]

I wouldn't recommend doing it this way, incase you delete one or more rows

[eluser]tochie[/eluser]
Exactly Dam1an....that is what im talking about...running a "cron job" at midnight to select the row for the day. how exactly am i to ask the script to run itself by 12am daily? That is my problem.

[eluser]Dam1an[/eluser]
I'm assuming you're on a UNIX server
If you go to /etc/cron.daily, you can put a script in there to update the WOTD
This normally gets executed in the middle of the night (on my server it defaults to 02:04)

If you need exact control of the time, you can add an entry to /etc/crontab (google to find out the format)

Btw, if you go the route of putting a script in cron.daily, make sure it executable

[eluser]n0xie[/eluser]
Another solution would be to cache the output where the word of the day is shown and let it expire after 24 hours. Next time someone visits the page, a new cache will be build generating a new cache file for that day with another 'word' randomly chosen from the database. Use one of the options mentioned earlier to get a random word from the database. Saves you a roundtrip to the database on each request.

[eluser]Evil Wizard[/eluser]
create a database table

Code:

create table
    `word_ot_day`
(
    `id` int(10) unsigned not null autoincrement
    `word_id` int(10) unsigned not null default '1' comment 'the word id for the day',
    `date` datetime not null default '0000-00-00 00:00:00' comment 'the date the word is chosen for'
    primary key (`id`)
    key `word_id` (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='hold word of the day word ids'

Then in the controller, check the date, then see if the word of the day has been set, i.e.

Code:

$strDate = date('Y-m-d');
$strSql = "SELECT `word_id` FROM `word_ot_day` where BETWEEN '{$strDate} 00:00:00' AND '{$strDate} 23:59:59' LIMIT 1;";
$objQuery = $this->db->query($strSql);
if(objQuery) {
    $objResult = $objQuery->result();
    $word_id = $objResult->word_id;
    // go off and display the word of the day
} else {
    // no word has been chosen, randomly choose one and insert into the word_ot_day table the word for today
}