Adding a Message Of the Day or Quote of the Day to your system

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

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.

Enjoy 🙂

This entry was posted in Uncategorized. Bookmark the permalink.