Now with MySQL!

Over the last few weeks - well, a few days of flurried activity a few weeks ago and a few more days of flurried activity this week - I have learned enough MySQL to bash together a working content management system for this website.

Up until now the site ran off a flat .csv file (page metadata) and a folder of flat text files (page content). Since the entire site is static unless I'm actually making changes to it directly, I had an offline site which generated pages automatically, and a special "create" script which generated all 600+ static pages of the live site at once. Then, I uploaded the 4MB or so of new pages manually.

This was an "I've got this to the point that it works adequately so I'm stopping now" kind of solution. The fact that making a single-character alteration to a single page required a complete regeneration of the entire site was irksome but I lacked the time to put anything better together.

So, a few weeks ago I got a book - O'Reilly's excellent Web Database Applications with PHP & MySQL, if you're curious - and finally started committing some rudimentary MySQL to memory. I got the shockingly useful phpmyadmin installed, imported my beloved comma-separated-value file, and eventually I had qntm.org actually running off the database. Finally, the live and offline versions of the site resembled one another somewhat! This was a good step forward. The eagle-eyed among you may have spotted this happening, when all the urls suddenly started redirecting from qntm.org/foo to qntm.org/?foo.

Anyway, this was a step forwards but I still had to use phpmyadmin to alter the database manually every time I wanted to update or alter any part of the site. So phase two of my plan, which is now essentially complete, was to figure out 1) cookies, 2) sessions, 3) how to import the page content into MySQL too, 4) data validation, 5) MySQL input sanitisation and a quadrillion other tiny things which have led to the point I'm at now, where I can just click "Edit" and edit a page instantly, live, much like the system I'm familiar with over at E2.

This will make absolutely no difference to the average user; not even cosmetically. However, it was highly educational for me, which is what counts. (This is also why I didn't use something like Wordpress to do it all automatically. Firstly, Wordpress' data-importing process balks at an accented character, secondly, you learn NOTHING by clicking buttons compared to typing the code.) Also, there's a lot of scope for new features, e.g. a commenting system. Possibly.

But that's for the future. This is a decent waypoint.

(Those of you who experienced the crazy downtime this time yesterday: I accidentally deleted the .htaccess file, which is the only file I have no backups of, so it took me a while to figure out how to recreate it. Thanks kthejoker for giving me a hand with that - couldn't have done it without him.)

If you spot any wandering MySQL errors, contact me.