Tag Archives: Scripts

Link Archiver

(Pardon my verbification.)

Here’s an idea for any website, though it could be particularly applicable to weblogs. I’m a reading junkie, I can’t get enough. When I come across a blog I like I often go back in its archives, which is a great way to get a feel for a site. It’s fascinating to see how some blogs have evolved over the years, how posting styles evolve, and to see what people were thinking around the time of important events.

There is one common thread in every archive I browse, I constantly run across dead links. Long-dead links. Dead permalinks even. I have read that the average life-span of a web page is 100 days—I think that may be generous. What good is the wonderful archiving of modern weblog software if those archives become irrelevant less than a year after they’re written?

I think the answer lies in some kind of automatic archiving of all linked content. When you publish a new post an intelligent spider tied to your blog engine could go and grab the content of the page you link to and store it locally. Once a week the spider checks all links on your weblog and if the resource no longer exists it updates the link in the entry to point to the locally archived version. The local archive would have a disclaimer and a link to the original location of the resource, much like Google’s cache. The link in the entry could also be modified in some way, perhaps with a different CSS class or rel value than normal links. The engine could also alert you so you could be sure to be wary of that website or publisher in the future.

How hard would this be? I know there are copyright issues that I’m ignoring, but I don’t forsee that being something that would hold this back. I doubt copyright holders who can’t keep their URIs cool are going to devote many resources to tracking down blogs violating their missing content. Besides, this could be covered.

I could see this done as a centralized service, something like Technorati meets Furl, but that would really defeat the purpose. Decentralization is the path of the future.

Sortable Tables and PDAs

For the Houston Palm Users Group meeting today I wanted to put together a comparison of currently available PalmOS PDAs for people shopping for the holidays. The idea from the beginning was to present the information as accessibly as possible, and after toying with doing a slide-based presentation, but talking about them all, or some sort of giant table I settled on the table, mostly because members could check it on the website when they got home and use it as a reference. Of course, giant tables are generally as unfriendly as you can get online, so I started thinking about ways I could boil down the information into just a few values, objective and subjective, and how to present in an effective manner.

The architecture of the table ended up being simpler than I anticipated with just 7 columns: model name, street price, weight, internal memory, total pixels, screen dimensions, and the completely subjective MattRating. I chose the values based on what people seem to care about most at meetings, and with a number of assumptions. Internal memory is important, but less so now that nearly every unit supports external memory (memory stick or SD) transparently. Total pixels was a compromise to present the screen dimensions as a sortable value. “MattRating” is a subjective rating of how I think each unit rates as a gift, taking into account all its features, expandability, the unit in comparison with what else is available, and price. It’s the secret sauce that balances out the values included with everything else about a PDA that couldn’t be usefully quantified or there wasn’t space for.

Finally to make the table as useful as possible I was determined to make it sortable by the table headers. I search high and low and found nothing better than the unobtrusive DOM sortable table code from kryogenix.org. It functions exactly how I think great javascript (ECMAscript, whatever) should. My only problem has been it seems to sort things oddly when you first click on a header, but corrects itself if you click on that header again. I’m trying to track down what could be causing this, but haven’t had any luck so far. Still, even with that one flaw, it’s better by far than the other sortable table implementations I found.

The result of these labors can be seen on the HPUG website, Holiday PDA Comparison.

Now what would be cool is a way to do it with alternating row background colors

Calculate Age in MySQL

I just got an email from docs@mysql.com saying the following:

The user comment system in the MySQL manual is not the place to request features. You can do so using our bug-tracking system at http://bugs.mysql.com/. Thanks. (Actually, your comment is not a feature request, but it relates to another comment that is. The example you’re giving is nice, but this is a reference manual, so we have to restrict it to _a few_ useful examples.)

My original comment was:

You bring up some important issues, but dealing with ages really isn’t that hard. For example you could do something like this:

mysql> SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS age FROM people;

Where ‘dob’ is obviously their date of birth. It’ll also work with pre and post-epoch dates. Please excuse the funky formatting as the
comment stem seems to insist on inserting line breaks into the code block. I ran into this problem while working on some genealogical things over at Mullenweg.com, a family site. I hope this helps!

Looking back, it’s funny that the comment is still around, I wrote it over two years ago. The date and time functions is the MySQL page I use most, so in some sense it was always nice to have my mark on there. For google and posterity I’ve preserved the comment here.

I’m glad they’re cleaning up the comments, as they are really bad in places and have atrocious formatting, especially when compared to say, the PHP manual. However there is a later comment (which is still up) that offers perhaps a better method. From Kirill Novitchenko:

The method posted by Mathew Mullenweg is good, but leap years goof it up on birthdays. (Try it. Use the current date and subtract exactly 5 years ago.)

Hopefully this will be the last ‘find age’ function. There is a simple premise to it:

  1. Subtract the current year from the birth year to get the age.
  2. If the current month and date is less than the birth month and date, subtract 1 from step 1.

Therefore, this should work with everyone who wasn’t born in the future.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

where dob is date of birth.

I’ve never run into any problems with my function but I see nothing wrong with the way this one works, so I may update my code to use it.

Why not just use unix timestamps and avoid all the funkiness? When I first started writing everything I actually did, but then one day I got a call from my lovely sister saying that it was showing everyone’s birthday as January 8th, 1901 (or something like that). I had reached the negative limit of a 32-bit integer, the upper limit being sometime in 2038. Moving all the date functions into the SQL is probably bad from a programming point of view but it works great for the application. Of course I have no clue how it deals with the 10 days Pope Gregory removed from the calendar in 1582. Hopefully that won’t come up. 🙂