UTF8 all over the place

22 Jul 2011

,


For all web applications, I have to make sure I’m using UTF8. It’s not just for customers who want the occasional page in Japanese or Korean; it’s for perfectly standard English pages which use text such as Ætna or the non-Ascii pound sign £

Now, to get this right, I have to make sure the database is setup to handle UTF8 AND the web server is setup to handle UTF8 AND the browser is setup to handle UTF8…

Read More

Table names in MySQL

13 Apr 2007


To quote from the manual

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in most varieties of Unix

I always get bitten by this and am now making a blog entry in the hope that I’ll remember.

I have one client where their ecommerce system was setup using SquirrelCart. I came along later and built an admin module so they could manage the rest of the site. The squirrelcart has tablenames in mixed-case, but I always use lower-case for all table and field names.

Occasionally I’ll write some code to query squirrelcart tables. (E.g. squirrelcart lets people use discount codes but it doesn’t report on how often the discount codes are used, so that’s an add-on) My code always works when I test it on the development machine, but flips when I upload it. And it’s because I type the squirrelcart table names in lowercase.

Client does not support authentication protocol requested by server

1 May 2006


I’ve been bitten by this one when trying to maintain different sites with different versions of php / MySQL.

Basically, user authentication for the later versions of MySQL server (versions 4.1 and above) has been improved and it now uses a default password hashing algorithm that is incompatible with older clients, including the standard PHP mysql extension.

The work-around is to reset the MySQL password to pre-4.1 style for all the accounts which connect via php

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

However, the mysqli extension (short for “mysql, improved”) which comes with Php5 is compatible with the improved password hashing and no special steps need be taken.

It should be a simple query

3 Mar 2006


Get a list of users and the most recent training course they have attended. This should be a very simple query with a sub-select, but I’m using MySQL 3.23 and sub-selects are not available, so queries have to be rewritten using joins. (Sub-selects are available from MySQL 3.24 onwards). Took me a while:

select
   t1.*
from
   usertraining as t1
   left join usertraining as t2
   on t1.user_id = t2.user_id and t1.training_date<t2.training_date
where
   t2.training_date is null

This matches up the training courses. When the date is at its maximum (ie. the most recent) there are no matching dates in the joined table which are greater than that – so the t2 entries for that row are Null – and we use the where clause to pick out exactly those rows.
Another way to do it:

select
   t1.*
from
   usertraining as t1
   left join usertraining as t2
   on t1.user_id = t2.user_id
group by
   t1.training_date having t1.training_date = MAX(t2.training_date)
where
   t2.training_date is null

And of course, to get the user details is then very simple:

select
    users.*, usertraining.*
from
   users left join
   usertraining as t1 ON users.user_id = t1.user_id
   left join usertraining as t2
   on t1.user_id = t2.user_id and t1.training_date
where
   t2.training_date is null