MySQL

warning: Creating default object from empty value in /home/skyontech/www/www/blog/modules/taxonomy/taxonomy.pages.inc on line 34.

Using the SQL MOD function in a select statement

Each photo on my photo-blog is a record in a MySQL database, but not every day of the year has a photo. I’ve been adding photos on and off since August, 2004, and each day I’d like to automatically provide links to the older photos I’ve posted on that day in the past (ie Jan 11 2010, Jan 11 2009, Jan 11 2008 and so on, but ONLY if they exist). I could have my PHP code loop through each of the possible dates and see if there is a record, but I’d rather have a single SQL command that would return just the records I was looking for. Somehow the MOD command seemed like the right place to start, but how?

Escaping wildcard characters % and _ in SQL

It seems like an obvious question, but when someone on an IBM developerWorks forum asked how to use wildcards as literals in SQL I realized I didn't know the answer off hand! How DO you find all the records with a part number that contains an underscore, say? SELECT * FROM partno WHERE part LIKE '%_%' doesn't work as we'd like!

How the Great Fire of 1904 fire ruined my MySQL DAYOFYEAR() calculations

I had what I thought was a simple requirement, sort a set of MySQL 5 rows in order by the day of the year, regardless of the year. April 22nd before April 21st, etc... In particular I wanted to show photos from 'this day in history' going back over a hundred years! My DAYOFYEAR() code worked perfectly, until the Great Toronto fire of 1904...

Creating a MySQL View directly in phpMyAdmin

Icon for creating a view directly in phpMyAdminI use phpMyAdmin to administer my remote MySQL databases and find the GUI good enough for most tasks, but I found myself wondering "How do I create a view in phpMyAdmin?" A careful review of the GUI turned up nothing, but this seems like such a common task I was surprised there was no way to create a view other than directly via SQL. A few web searches later and I realized there was a way, it was just not obvious until you figure it out. So here's my quick tutorial on creating a MySQL view directly in phpMyAdmin.

Google Earth KML file for the photographs on my photoblog

A quick tutorial on how I put together a PHP script to serve a Google Earth KML file updated live with all the geo-tagged photos from my photo-blog, stored in MySQL. Rather than having people store the whole, static data file on their local machine, I've created a small, second XML file that uses the element to have Google Earth re-load the data file every hour, so that newly geotagged photos are included as well.

If you have Google Earth installed you should just be able to load the KML data file on this page to see a live version of the links.

( This is a link to an older technology based blog entry I wrote. )

Syndicate content