Happy 7-11 everyone. Go buy a Slurpee.

I finally solved a problem I've been working on (a little) over the past two weeks. Some folks (and you know who you are) have requested "Previous" and "Next" links while reading poems - for instance, if you were reading Shakespeare's "Sonnet 45" the previous link would take you to "Sonnet 44," etc.

While this is a perfectly reasonable request for someone to make, it's not as simple to implement as it sounds.

For one thing, there really isn't any order to the poems, per se, because they're in a relational database. The only order the DB keeps the poem in is the order that each poem was entered into the database.

Each poem gets a unique ID number which is automatically added to it when the poem is submitted via our update script.

But all of the poems for each poet may not have been added at once. I could have added ten poems by Shakespeare one day, then five more by Frost, then another ten by Shakespeare, then another five or six by someone else... so the Bard's poems aren't in any kind of order in the DB.

Okay, no problem - I can grab all of the poems that have the author ID number of the Shakespeare entry in the Poets table. And I can sort them by the poem titles. So when you view the table of contents for a poet, you'll see the poems in the right order (and paginated by 25 poems a page).

So what's the problem? Well, when I'm building the "Previous" and "Next" links, there really isn't a cheap and easy way of ascertaining what the "next" poem is. It's not the next ID - it's the next title by the current author after the current title.

The easy way to do that would be to write a DB query that simply selects ALL of the titles by an author, sorts them, finds the current title, and uses a data seek query to find the next row of the previous result set (don't worry if you don't follow this, it's pretty basic but it IS database geekery after all).

The problem with that is that while it's easy, it's not cheap. It's expensive on DB lookup time, it's expensive on the amount of server memory that is used - it's just messy and dirty and slow.

Now, when there's just one person accessing these things, retreiving 1,000 rows from a table is no big deal. But really, I only need one row - the next one. Or the previous one. Actually, I need two rows, since I have to build the links on the fly.

So I tried my hand at writing what I thought would be a working query. In English, the query pretty much said:

Select the poem ID from the Poems table where the poet ID is the current poet, sort by the poem titles, and then once they're all sorted send me back just the row where the poem ID is greater than the current poem ID (essentially, this would give you the next row).

For the previous link, you just sort them in reverse and do the same thing.

But this didn't work.

So I posted a message on a message board that's frequented by programmers who spend a lot more time memorizing database manuals than I do. And the best they could come up with was "Why don'tyou add another field to your table called "Sort," and then you can sort them by hand and give each an "order number" so you can drag them out more easily?"

It's a good thing the guy posted that on a message board, because if he said that to my face I may have had to slap him.

I'm a "do some extra work in the beginning to save myself time and effort later" kind of guy. I don't mind a little programming difficulty if it means I can kick back with a cocktail with a little umbrella in it, put my feet up, and watch re-runs of "Hee-Haw" on the SuperStation while I update my web site in 1/20th the time it would take me to do it manually every time.

Anyway, to sum up, I fixed it today.

It felt goooooooood.

The problem? The function "mysql_result" in PHP won't sort on a field that you're not selecting, but "mysql_query" will, but if you're selecting a result on a query you're subject to the rules of the result function, not the rules of the query function.

Why do they not both work the same way? Ask the geniuses that wrote the thing.

In essence, I suffered for awhile because of some other group's poor decisions.

Then I ate some Chinese food and forgot my troubles.

Later I edited 46 poems by Ezra Pound. He was a wild man, that Ezra. And isn't "Loomis" just the coolest middle name ever?

Between the time I go to bed tonight and the time I start working on the site again tomorrow I'm going to have to decide on the layout of the options and features of the poem display page (the page that shows the actual poem).

Features include:

• A link back to the Author's TOC
• Print the Poem
• E-mail this poem to a friend
• Analysis
• More info
• Comments
• Last Read:(date)
• Viewed xxx times
• Added on (date)
• Buy the book from Amazon.com
• Save this poem to your favourites (for members only)

Etc.

On Plagiarist I had all of that floating to the right of the poem in a side-bar. The problem with having the menu items next to the poem is that for poems with long lines, the icons and menu items interfere with the poem.

Most of the time, the poem lines are short and the sidebar isn't a problem at all, and I minimalised the impact of the little icons so that you could really just ignore them and read the poem, but I like the idea of making the poem page fully vertical in placement of features and elements - and giving the poem plenty of room to breathe.

And of course if someone who's half-blind, like me, makes the text really big so that he can read the poem, then the extra space will be appreciated.

I'll figure it out. I'll probably split the difference - I'll put some of those elements above the poem and some below.

I've been thinking that I'd like the "Print," "E-mail," and "Save" options up top, maybe just under the line seperating the title from the author (in the default theme, at least).

I don't know. But I'll know by the time I post here tomorrow.