Tags: collation

A daily quote with php/Mysql

By admin on May 29, 2008 | In Formula 1 - More advanced stuff | Send feedback »

In my other website Other Site I have a daily expression displayed (in German and Spanish). It's fairly easy to do this, although it obviously takes some time in populating the database table with the quotation data.
I have one field as a counter (datatype TINYINT if the value will never exceed 127 otherwise SMALLINT ), this field is called day_seq in my table. In my table I've entered in 31 entries (one for every potential day of the month), although you can obviously enter in more - perhaps 62 (for a unique entry every day over 2 months) or even 365 (for every day of the year). Although not necessary if you only want to access a quote in one category, I also have a field cat_id that determines which language expression I'm pulling back ('spanish' or 'german). So if the day was the fifth of the month and I was pulling back the Spanish expression, my code would be as follows:

Code:

mysql> select lingo, english, cat_id, day_seq
    -> from dichos
    -> where cat_id = 'spanish'
    -> and day_seq = 5;
+---------------------------+----------------------------+---------+---------+
| lingo                     | english                    | cat_id  | day_seq |
+---------------------------+----------------------------+---------+---------+
| Más vale maña que fuerza. | Brain is better than brawn | spanish |       5 |
+---------------------------+----------------------------+---------+---------+
1 row in set (0.01 sec)

Now, in order to pass the parameter (in the example above it's 5, but it changes each day) to the sql statement in php we need to do 2 things. The first is to use the php function getdate() which brings in the current date's details. We then extract the day number string from this date. Here's the code in php to get a new quotation in Spanish every day:

Code:

$today = getdate();
  $mday = $today['mday'];
 
$sql = "SELECT id, lingo, english, cat_id, day_seq  FROM dichos
        WHERE day_seq = " . $mday . "
        AND cat_id = 'spanish'
        LIMIT 1;";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);


Note that $today['month'] will bring you back the alpha month (eg 'May' or 'June') once you have populated $today with getdate(). If you want to have more than 1 month's expressions in your table, you can keep incrementing the day_seq value. So, when the month = 'January' you perform the standard join on day_seq = $mday; when the month = 'February you add 31 to $mday (day_seq values in your table of 32 to 59 for this month) and so on. Alternatively you can add a monthseq field to your table.

One further point - if recording foreign text with accents in your database, make sure the collation has been set to UTF8_unicode_ci; unfortunately many versions of Mysql default to a Latin1_Swedish_ci collation. All too easy to have the correct characters replaced by strange icons.

Tags: collation, dates, php