Tower Of Power

I recently had fun with an all-to-common issue with SQL driven websites: hierarchical data. For those who don't like big words, think trees. Other people have already discussed storage methods, and I would actually highly suggest you read the writeup if you haven't already.

While it is fairly straightforward to deal with, in our case we use HTML_QuickForm to handle our forms and are using QuickForm's hierselect to select a category.

The issue starts showing its face in 2 distinct areas: (1) the client is not yet sure how deep they need their categories to go, and (2) the hierselect requires a very specific format of data to be passed in.

Continue reading ‘Hierarchical Data With PHP and MySQL’ »

Peter Zaitsev posted a very interesting test on how PHP and Apache handle runaway PHP scripts.

I'm sure all of us have had a long executing SQL script or at least a runaway script, and he points out even with ignore_user_abort set to FALSE and max execution times set both in PHP and Apache, a runaway SQL query can execute beyond the timeouts, even when aborted by the user (stop button on the browser).

Even using the function connection_aborted() to check for a user abort fails.

However, the script will cease on a user abort if it performs regular ob_flush(); flush(); commands. For example:

<?php
echo("Hello");
for($i=0;$i<10000;$i++)
{
	sleep(1);
	echo('.');
	ob_flush();
	flush();
}
?>

Just one more reason why I love the MySql Performance Blog.

I just rolled out some new automation tools for a few projects here at work and so far I've been extremely happy.

Much to my embarrassment, development has previously been outside of source control due to the fact that we develop sites, we don't deploy packaged applications, and we don't have a cohesive IT setup (everyone sets up their desktop to their liking so maintaining consistent development environments across all computers is difficult).

However, thanks to SVN, Xinc and Phing (and DBDeploy), this has changed! Now everything is in source control and automatically deployed to our dev server upon commit. I am currently talking with Arno about perfecting svn tag monitoring to automate staging and (possibly) live deployments, so I'll post about what I did when that's finished.

The great thing about this setup is all pieces are technically interchangeable. If you don't like Xinc you can use CruiseControl. If you don't like Phing you can use Pake, or a shell script even. If you don't like DBDeploy you can roll your own setup or swap it out for your database versioning system of your choice!

However this post will cover Xinc, Phing, and DBDeploy as (a) I have experience with them and none of the others, and (b) they integrate extremely well (Xinc and Phing's primary distribution method of choice are PEAR channels).

Continue reading ‘Automating the Development Workflow’ »

I am by no means a database expert, such titles are best left to people like Peter Zaitsev and Vadim Tkachenko, however one of my co workers has been asking me for some help on how I come about my database designs, particularly issues concerning normal form.

Whlie I could spend several posts going over the intricacies of 1st, 2nd, 3rd, and 4th normal form as well as "The key, the whole key, and nothing but the key so help me Codd" Boyce-Codd normal form, however reality dictates that time spent strictly to these academic levels is either time wasted (projects with due dates unfortunately cannot spend forever on the database design) or pointless as being reasonably intelligent will bring you very close, if not within 3rd normal form.

If you're really interested, Wikipedia provides a great resource to get started learning the more academic aspects.

I'm going to assume that you have done a bit of database work and at least understand the concept of a join.

The one of the goals of database normalization is to reduce the repetition of information and thereby reduce the complexity of an update of said information. If the same string is in the database N times, we should abstract it so that if we need to update said string due to a typo or anything else, we need to only change but one location.

Continue reading ‘Database Normalization And Design: A Primer’ »

I've started to amass a large amount of feeds in my reader and I thought I'd share a few with you:

PHPDeveloper.org (feed)

Great aggregation of blog posts and updates in the PHP world.

Zend Developer Zone (feed)

Zend's place for all things PHP related

SitePoint.com (blogs) (articles)

The infamous SitePoint. Not entirely PHP, but excellent web related articles and a good way to keep loose tabs on the other web spheres (ASP.NET and RoR in particular)

Particletree (feed)

Doesn't update very often, but each article is a gem (in terms of solving a programming problem).

Chris Shiflett (feed)

THE PHP and Web Application Security guy.

Joel on Software (feed)

A classic, not really PHP oriented.

And finally, definitely not PHP focused, but a very good blog to read if you do any MySQL work (if you are a PHP person, chances are this includes you)

MySQL Performance Blog (feed)

Great collection of articles and talks on optimizing MySQL, either at the server or query level, and more...

While this isn't all of the feeds I have on programming related topics, some of them are new adds and I want to give it some time before I share the links.

So I'm setting up my windows PC at home (hooray compsci classes being an all Microsoft setup, requiring Visual Studio 2005), downloading the MySQL GUI tools, launching them to test when I notice that MySQL Workbench went through a HUGE overhaul. When I say huge I mean "It Actually Works Now And Looks Completely Different" huge.

Which is funny 'cause last time I downloaded the work bench it was horrendously broken alpha software. Version 1.1. Now we are at 5.0 Beta? About 3 months later!?

Crazy. I guess I need to pay more attention?

Though the real question is right now, how does it stack up in a real world situation? Well I had to finish up the database design for some client work so I decided to try working it in MySQL Workbench (rather than my usual staple, the sadly discontinued DBDesigner).

Continue reading ‘MySQL Workbench 5 Beta (Or: WTF?)’ »