My coding continues to go smoothly. I hit a snag yesterday as I worked on an administration function. I attempted to display all the photos that did not belong to a post, the orphaned photos, if you will.

In the old sewcrates, I tied the photos to individual posts. The photos were uploaded to the post’s directory (remember, each post had its own directory that contained flat files and attachments), and I generated the /slides and /thumbs as subdirectories for the posts.

In NAIS, I decided to separate the photos from the posts. Each photo (and I use photo loosely—it includes music and videos as well) lives in a table and in the /photos directory. I generate the /slides and /thumbs as subdirectories from /photos, and use a table in the database to tie the posts and photos together.

I designed it this way to allow for different types of albums. Besides the post-based albums (what you currently see on sewcrates), I wanted to create albums based off tags. For example, I could view an album with every photo tagged with "Doolies." While the functionality will be there, I still need to go back and tag all my old photos. This is not something I'm rushing to do. I’m planning to build in functionality to allow for batch drag and drop tagging of photos. It may not make it until 3.1 as I’m getting anxious to post NAIS.

Let's get back to my snag with the orphaned photos view. At first, this seemed easy. I used my photos index code to generate a page with a group of photos. I just needed the MySQL query to finish it. I thought I had gotten rather good at writing the queries. When I coded castofhorribles.com, I spent a lot of my time looking up the format of the queries. After coding NAIS, I no longer have to refer to the documentation to generate most queries.

The problem seemed simple: find me all entries from the table of Photos where there is not a corresponding entry in the table of PhotosPost (the table that ties together Photos and Post). Here’s the abbreviated Data Dictionary:

This stumped me for a while. I can easily write a query to find all photos in a post, or all posts that a photo belongs to, but when it comes to finding something in the negative, I hit a huge wall. That’s where the magic of sub-queries came in. After much head pounding and Googling, I arrived at the following query:

It looks complicated, but it’s actually very easy and logical. You don’t even need the Post table. What you’re saying is find me all photos where the photo does not exist in a post. The key insight I had was that you can refer to columns from the first query in the sub-query. In the above code, the Photos.PhotoKey is referring to the Photos column in the first query. Once I realized this, everything else fell into place.

This page from the MySQL documentation provided me the insight. Notice the second example:

This query finds what kind of store is not in a city (which is very similar to asking what photo is not in a post).

This is a long way of saying that I regret not taking a database class during any of my computer science degrees. I can’t believe it’s not a required class. I never understood the value of databases until I started working with them. To give you an idea, let’s look at the lines of code in NAIS compared to the old sewcrates: the original sewcrates had 2,287 lines of code.[1] The new sewcrates has 1,034 lines of code.[1] While I can attribute some of the improvement to more efficient design, the majority of the weight reduction is in the removal of flat file parsing. I replaced what used to take hundreds of lines of code with a simple MySQL statement. Isn’t modern technology wonderful?

[1] The lines of code are approximate. I used: grep -c ";" *.php on my two code directories. It’s not perfect, but it provides a decent sketch.