Entries tagged “mysql”

If you have a list of ordered ids and you want to turn them into an ordered result set you can use FIELD() in mysql:

SELECT * FROM addons
ORDER BY FIELD(id, 3, 5, 9, 1);

This is a handy trick if you use an external search engine which gives you an ordered list of ids and you want to pull out entire row sets.

We do this in addons.mozilla.org using the Django ORM like so:

The code in action.

I wanted to find a list of Firefox addons that had smushed text in their title. E.g. FireBug or StumbleUpon. The normal porter stemming algorithm that Sphinx uses does not turn “StumbleUpon” into “stumbl upon” as it would with “Stumble Upon”. I was hoping for, and unfortunately could not find a method to do a regular expression search/replace using mysql. If I could, I could have Sphinx read “StumbleUpon” as “Stumble Upon” and all would be well (although in theory this would backfire).

I use mysql and macports on OSX and both were broken when I upgraded to Snow Leopard.

I have a mysql server for reviewsby.us that requires tunneling, and after establishing a tunnel like so:

So I’ve been going out of my mind trying to figure out why something like:

In August I took a break from reviewsby.us only to be plagued by spam. In September, I relinquished portions of the project planning to my wife. We haven’t released anything publicly, yet, but there’s a lot in development.

• I updated the development framework to symfony 1.0 alpha and took care of a whole slew of bugs.
• Katie and I came up with a wireframe that details some of the upcoming changes.
• I upgraded the user logic to take advantage of sfGuardUser, a user management plugin for symfony.

I’m in progress of writing location specific searches. I’m slow to implement. It seems that this month is far busier than I’d like, and I can rarely get in a block of enough time to just crank this out. The problem with geographic-specific searches is mySQL supports those types of queries, but it’s not as easy as I’d like. Zend Search Lucene with some support from PHP, however, may yield some promising results. As always, I’ll share my findings in a forthcoming tutorial.

Anyway, no visible updates on the actual site, since I didn’t want to put alpha software on the live site. I’m sure by next month symfony will be ready.