posts

Mysql, PHP: Internationalization

2006.07.10 – Oh, man. This was a toughie. What follows is an account of how to deal intelligently with foreign characters and data entry in PHP/MySQL. So I’m working on a site, and realize that I need to handle foreign names within content. No fun. So I change my MySQL table to use utf8_general_ci, instead of ye ‘ol latin1_swedish_ci (which does funny things like equivolating umlaut-u to “y”), and, of course, convert all of the columns as well. Now, this looks like it’s working fine until I take my DAO and start trying to edit content, at which point it freaks out and shows foreign characters all wrong. I make sure the html document doctype is set properly, only to discover that the PHP MySQL client (i.e., Mr. mysql_query() function) is actuallystill using latin1_swedish! I hack a bit of code into my DAO, so that I can override this on a per-table basis, by having it run the following once as a normal query immediately after connecting: ** ** SET NAMES utf8; At this point, everything works great. Except for one little catch – RLIKE and REGEXP, the core of my tokenized search engine, work based on latin1_swedish again, so not only is ‘Ç’ != ‘C’, but I can’t even search within that foreign text for native characters. Which messes everything up. Anybody got any good ideas?

Read This

MySQL: The Having Clause

2006.06.22 – This one has been driving me crazy for years helpful site. Let’s say that you have a MySQL database with a bunch of users in it, and you want to see only those users that have duplicate email addresses. You’d think this would work, but it doesn’t: SELECT COUNT(*) AS count, * FROM users WHERE count > 1 GROUP BY email; The problem is that WHERE is applied before the GROUP BY, so you can’t use aggregation functions (COUNT, MAX, etc) with a WHERE. The solution is to use HAVING, which is applied after the GROUP BY: SELECT COUNT(*) AS count, * FROM users GROUP BY email HAVING count > 1; This will give the expected result.

Read This

OS X: Force mounting a drive partition

2005.06.27 – For once, it’s not my laptop that’s broken; it’s my external hard drive. For future reference, in OS X, if you encounter a drive partition that is recognized by the Disc Utility, but refuses to mount, try running these commands in the Terminal:

mkdir /Volumes/VolumeName
sudo mount -t hfs /dev/diskXsX /Volumes/VolumeName
Where diskXsX is the disk as specified in the Info window of Disk Utility. If that fails, open up Console, and check system.log. Or you can run this:
tail /var/log/system.log
You might see something like this:
Jun 27 23:01:02 localhost kernel: jnl: replay_journal: bad block list header @ 0x50a600 (checksum 0xeb7a03c6 != 0xc62acb43)
Jun 27 23:01:02 localhost kernel: jnl: journal_open: Error replaying the journal!
Jun 27 23:01:02 localhost kernel: hfs: early jnl init: failed to open/create the journal (retval 0).
If this is the case, then the journaling on that partition is screwed up really bad. I don’t know of a way to fix this, so I just mount the drive without journaling by running this command:
sudo /System/Library/Filesystems/hfs.fs/hfs.util -MU diskXsX /Volumes/MyVolume fixed readonly nosuid nodev
This should allow you to access the drive from the Terminal and copy the data off of it. Oh, and FYI, you can use regular expressions with most command line utilities, including cp:
cp -r ./iTunes Music/[0-9]* /Volumes/MyVolume/iTunes Music/
This is really useful when a copy has failed, and you want to start from a certain point.
cp -r project management team./iTunes Music/[m-zM-Z]* /Volumes/MyVolume/iTunes Music/
I really hate computers. [Edit: As it turns out, disconnecting the drive in question, rebooting, and then re-connecting the drive caused everything to start working again. Miraculously.]

Read This