Skip navigation.

Harold's Home

XML version of this site

PHP Scripts

CLI fun
Mail on 404
HB-NS (NewsScript)


APOD to Desktop
Dreamweaver Extensions


Other stuff
Central Grinder

OOOk Default:

VJ stuff
VJ Tools
Sample Movies


All articles in MySQL

Regular Expressions makes my night
Meh, I couldn't sleep as I have a toothache. I knew I should have made a new appointment with the dentist when he cancelled my appointment in January but it's too late to regret that now. In any case, while lying in bed waiting for the Paracetamol to kick in I was pondering a SQL problem I had at work this week and suddenly it hit me: doesn't MySQL have a regular expression parser I could use to solve the problem I had with selecting eventdates entered in a VARCHAR field?

And lo and behold it does: Tech-recipes: Use regular expressions in MySQL SELECT statements contains the answer to my problems.

SELECT cursusdata, titel
FROM cursussen_nw
WHERE cursusdata
REGEXP '[[:<:]]4-3-2008[[:>:]]'

will match a date like the 4th of March in a field like this: '19-2-2008, 4-3-2008, 11-3-2008' but will not match a field like '7-3-2008, 14-3-2008, 21-3-2008'. My current code uses some array checking to make sure I filter out the dates that aren't wanted like the 14th and removing these checks will reduce my code by about 7 lines, which makes the code a lot more readable.

Good stuff™, and the toothache has gone down a bit so I'm good to go off to bed again.

And then my head exploded

so I was struggling with some character set issues today. Here's what I found:

  • a mysql server has a certain character set
  • a mysql database has a certain character set
  • a mysql database table has a certain character set
  • a connection between a client and the mysql server uses a certain character set
  • an html or php file is saved in a certain character set
  • an html or php file can declare it's contents to be written in a certain character set
  • a webserver can declare a served page to be served with a certain character set

All of the above can be the same, or, more likely, they can conflict. This can, and will, destroy your sanity and sap your will to live.
They fell, they screamed, they wept but they died
From hands of the abhorrer

iTunes database to MySQL pt. V
Once again we revisit that old favorite. The script to put iTunes data into MySQL.

See earlier articles:
Part I
Part II
Part III
Part IV

This time it is because I've been getting a lot of mail lately (well, three to be exact) about the fact that the script by Nikita Zhuk of MacZsoftware that I use to put iTunes data into a database isn't available anymore and that the website hosting it appears to have vanished into thin air.

So I've packaged my search script along with the needed files by MacZsoftware for your downloading pleasure.
Download the scripts.

Please note that these aren't the exact same files as originally distributed. I've lost the readme and provided my own. Also these scripts are meant to be run from the commandline and not from a browser, with the exception of intune.php, the search script. The original author did provide this functionality but I've commented most of that out as it takes forever with a library of any decent size. (Just schedule the commandline version on a regular basis and things will go much smoother and faster.)
I have also added workarounds for ampersands in track-data and added support for the Grouping metadata Apple provided in a release of iTunes later than the original script (though the search script does nothing with that info, hack that in yourself if you feel like it).

Only tested on Mac OS X 10.3 with iTunes 4 and later. Use at your own risk and for gods' sake read the disclaimer in the provided readme and don't email me for support on getting this running. Use the comment form on this post if you need it but I can't guarantee getting this running on your machine.

iTunes database to MySQL pt. IV
I have enhanced the search script (version 1.2, zipfile, 8 k) for phptunest generated MySQL iTunes Library databases.

For more background information see:
Part I
Part II
Part III

New in this version:
- Easily show the longest and shortest songs in your library (because we can): iTunes extremes.
- Better handling of ampersands in the search terms, at least on my setup.
- Provides a warning when the database is being updated and therefore only partly filled (see line 4).
- Small enhancements throughout.

Version 1.1 is still available here: Version 1.1.1 (zipfile, 4k).

See a live demo on my homemachine.

iTunes database to MySQL pt. III
I have enhanced the search script for phptunest somewhat.

New in this version:
- Pulldown list with all genres.
- Title attribute (shows as a tooltip on most browsers) containing the filesize on the musicfile link.


iTunes database to MySQL pt. II
Last week I linked to phptunest which can import an iTunes library XML file to a MySQL database.

Well, here is the search script I promised which is pretty nifty:
see the source or download a zipfile which includes the stylesheet.

Change a few values at the top of the script and you're good to go.

I encountered a few problems with the import which had to do with the XML parser of the importfunction choking on special entities with ampersands (like &) but I figured out a workaround which I've submitted to the author of phptunest.

You can also see a live version of the script running on my homemachine (which may or may not be running): Note that clicking on a trackname will bring up a password dialog as my music is in a protected area. Don't bother asking for the password.

iTunes database to MySQL
For a while now I've been meaning to create an interface so selected friends can search my iTunes Library.
The problem is that the iTunes database is actually a pretty dense piece of XML.
I figured the fastest solution lay in trying to somehow get this data into MySQL and to create a search interface.
The good thing is that the iTunes database is XML, so all I needed to do was somehow parse that.
The problem is that my iTunes Library contains somewhat over 5000 songs and the XML file is 7.3 MB large, so using XSL transformations is right out, what with my meagre skills in XSLT and performance issues.

I did some searching last night and came across the following:
phptunes is a PHP script that can import your iTunes Library to MySQL.

The script is somewhat kludgy, but after some timeout issues (after 3000 imported songs) I just ran the script from the commandline and hey presto: everything is imported nicely.
The script imports all kinds of weird stuff but the beauty of writing your own search interface is that you can just ignore all those fields and focus on the meaningful ones.

I still have some tweaking to do but when it's finished I'll share the script. In the meantime have fun importing your own database to MySQL.

MySQL Query
Query: does anyone know if MySQL has some sort of statement that looks in an array (a bit like the in_array function of php)?

Something like: SELECT * FROM table_name WHERE customer_number !IN_ARRAY('1','12',354') ORDER BY customer_number LIMIT 0,1 would fit me nicely for a couple of cheaters in the online statistics of the Virtual Pet Rock site.
Please mail me if it's possible.

<rant>Geez, what is it with some people anyway? They get a free virtual rock to take care of (presumably because they can't keep a goldfish alive and think a real rock is probably a bit involved) and then they cheat by hacking the statsfile.
What worries me most actually is the fact that they not only hack their stats to come out as the best caretaker, but also as the WORST caretaker. C'mon, just neglect the rock for four months, start the app and upload the stats, no need to cheat, just pure and simple neglect, an idiot could do it.</rant>

Show all items | Read all items | Show topics

About, copyright, privacy and accessibility | Mail