Technical Thoughts, Tutorials, and Musings

PHP Group By with Arrays

By far the most common idiom when using SQL from a web application (PHP or otherwise) is simply listing records. The standard logic looks something like this (give or take real templating):

<?php
$result
= mysql_query("SELECT tid, name, size, color FROM things ORDER BY name, size");
print
"<table>\n";
print
"<tr><th>Name</th> <th>Size</th> <th>Color</th></tr>\n";
while (
$record = mysql_fetch_object($result)) {
  print
"<tr>\n";
  print
"<td><a href='viewthing.php?tid={$record->tid}'>{$record->name}</a></td>\n";
  print
"<td>{$record->size}</td>\n";
  print
"<td>{$record->color}</td>\n";
  print
"</tr>\n";
}
print
"</table>\n";
?>

That's all well and good, but in practice can be quite limiting. Why? Because you can't then group records, that is, display not one but several tables, one for each color. SQL, of course, offers a GROUP BY clause. That doesn't do what we want, however. GROUP BY is an aggregate clause, and is used for creating totals and summaries of records. We want to cluster records by a field that is not the ordering field, or a value that is calculated off of the record itself.

I've generally used two different methods for PHP-side grouping, one of them much cleaner and more flexible at the cost of a little performance.

What is your accent?

So apparently I don't have an accent. The InterWeb said it, so it must be true!

Stupid quotes

A perennial problem for anyone in IT is the infernal beast known as "smart quotes". Smart quotes, also known as "curly quotes", refers to the angled apostrophe and quotation characters that are often used in print but are not found on any conventional keyboard. There's a number of problems with them. First of all, most people don't realize what they are. Then most people don't understand how they work. And finally, Microsoft broke them.

Emacs wins!

The great question of the day has been solved, and it is Emacs that wins.

Not that I use Emacs, mind you, but I've said for years that sooner or later, GNU/Linux would go away and be replaced by your choice of KDE/Linux (KDE having taken over so much functionality that all it needs is a kernel) and Emacs/Linux (Emacs already being almost an OS, except for missing a text editor). The only question was which would happen first.

Merlin the Amazing

I make no secret about the fact that I am a fan of Free and Open Source software. There are many reasons. The quality of the code tends to be better. I like to tinker with it and see how it works. It's usually gratis as well as [Free|Open]. But what really makes Open Source so attractive as both a user and a developer is talking to merlinofchaos.

Simplifying SQL

Most PHP applications do fundamentally the same thing: Shuffle data from an SQL database to a web page and back again. The details vary with the application, but in general that's what most web apps do. That very quickly runs into the bane of most PHP developers' lives: SQL syntax.

It's not SQL syntax itself that is bad per se. The problem is that it is a string-serialized format, which means you have to take your nice clean data structures and serialize them out into a string that has no semantic meaning to your PHP application. That's boring, dull, and introduces all sorts of places to totally mess up your application with a typo, and that's without even touching on issues of security. And then there are the issues with SQL syntax itself, in particular the way in which INSERT and UPDATE statements, which seem like they should be similar, have no similarity whatsoever. That makes "replace" operations (insert if new or update if not) very tedious to write, particularly if you have a lot of fields.

Fortunately, with a little ingenuity and help from PHP's array handling, we can give ourselves a common syntax for INSERT and UPDATE operations that maintains semantic meaning, and then get DELETE statements free of charge. Let's see how.

Too much of an upstart

So it seems Ubuntu, a distribution I have grown to like more and more of late (especially every time something breaks in Debian Sid), has decided that init has got to go. Their answer? Not any of the various attempts to replace it in the past, but once again start from scratch with something called Upstart. While I agree that init is one of the many parts of typical GNU/Linux system that desperately needs to grow out of the 1970s, I'm not sure that Upstart is the right way to do it.

A blog reborn

As both of my avid readers have likely noticed, this blog has not been particularly active of late. That is to say, today is the one year anniversary of the last time it was actually used. :-) So what do I do to celebrate? Rebuild the whole thing from scratch, of course!

How does the other half live?

Some people complain about how GNU/Linux isn't desktop-ready. It's too hard to use, the applications aren't there, it does things in silly and quirky ways... We've all heard the list. And some of us persevere anyway.

Recently, though, I've been working on-site with a client for a few weeks on a PHP project. The web app we are building is on a remote GNU/Linux server. Our desktops are all Windows XP SP2, of course. Because I need to edit the files locally but test them remotely, I need a fair bit of network transparency. Of course, Windows XP provides none unless everything is using SMB, which our production web server does not (naturally). So what setup did I have to cobble together?

Shared web hosting: Where is open source?

In an earlier post, I mentioned some research I'd been doing with regards to Linux-based server software. To be more specific, I was investigating shared web hosting control panels. Most any web hosting service you find offers a web-based control panel. Generally such a system allows each user to manage their domain information, files, mail accounts, FTP accounts, and other such common features, and allows the admin to manage different user and reseller accounts. Some users get access to run web scripts, some don't, some have more disk space than others, etc. Some require specific underlying server software (a specific Apache version, postfix vs. qmail for email, etc.), others support a variety of alternatives.

Syndicate content