Got handed a nice SQL file a few years back, one which I slip into most of my applications and one that generates quotes of the day, (or Message of the day – MOTD) there are 1462 records or about 4 years worth of quotes on there if you generate one per day. The SQL Create file for the table is zipped here => qotd
To load the quotes, simply unzip and run the query on your SQL system, this will generate the qotd table with 2 fields in it (don’t worry, nothing too rude or controversial and its only a 100KB or so)
There are several ways to use it, here is the simplest:
SELECT quote FROM qotd WHERE uid = ( TO_DAYS(NOW()) MOD (SELECT COUNT(*) FROM qotd) )
Tested only on MySQL which is sometimes a bit quirky, but Hopefully your chosen SQL system supports the above, for explanation,
- The MOD function is simply the remainder when you divide the first parameter by the second, eg 7 mod 5 = 2. (same as % in C)
- The TO_DAYS(date) function returns the day as an integer:
ie SELECT TO_DAYS(NOW()) returned 736255 on the 19 October 2015
- (SELECT COUNT(*) FROM qotd) is a sub-query which returns the number of quotes in the table (the number of records) and returns 1462
So the where function simply returns 736255 % 1462 and selects the quote where uid = 869, once the date gets to quote 1462 it will go back to quote 1 and keep on going. If you add more quotes it will also automatically include them.
The downside of the simple query is we miss days that people do not work, at that point you will need to add another field to say the date the quote was used and update it in your software once used.
Anyway Enjoy 🙂
