SQL – Inner joins and limit…

I’ve been hard at work at the rockface of the new Wiblog system recently, and it seems that the more I get done the more there is to do. I met a bit of a problem tonight regarding tags (those lovely bits of folksonomy) which I think I’ve fixed. This one is quite hard to explain, and is only suitable for people who would wear a “geek” badge with pride, but I’ll do my best.

I have a MySQL database containing table with blog entries in it. That table is called ‘entries’. And I have another table with tags in it called ‘tags’. Each entry in the entries table has a unique ID, and each tag in the tag table is linked to the entry it belongs to using a field called ‘entryid’. So far so good.

However, I want to paginate (that is, split into multiple pages) my blog entries, showing 10 on a page then a link to the next page of 10. That’s great as long as there are zero or one tags linked to each entry. The code would look something like this:

select e.id, e.title, e.body, t.tag
from entries e
left outer join tags t on t.entryid = e.id
order by e.id desc
limit 0, 10

Those of you with some SQL knowledge will see my problem. When any particular entry has more than one tag linked to it, the ‘limit’ clause will not work correctly. Rather than bring back the top 10 entries, it will bring back the top 9, with one of thm (the one with two tags) twice. Once for each tag. Let me illustrate:

Output for the above code with one tag per entry:

id title body tag
100 100 title 100 body 100 tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag
91 91 title 91 body 91 tag

Output for the above code where entry ID 100 has two tags:

id title body tag
100 100 title 100 body 100 tag
100 100 title 100 body 100 another tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag

I’ve made the second tag bold to make it obvious. So, where has ID 91 gone? Dropped off the end because of the second tag linked to ID 10, that’s where. No good, in fact it’s downright bad.

I’ve had an idea for how to fix this for a while, but tonight has been my first chance to get it working. I’m sure this isn’t unique, but I had a quick search and couldn’t find anything similar. So, here we go.

The important thing we need to know is what the highest and lowest IDs of entries are that we want to bring back. Once we know that it doesn’t matter how many tags we have linked to any entries, as we always know our list will be bound by the upper and lower limits.

Here’s the code, with (hopefully) clear instructions:

# select the fields we want
select e.id, e.title, e.body, t.tag
# fom the entries table
from entries e
# join the tags table
left outer join tags t on t.entryid = e.id
# here's where it gets interesting
# firstly, get the minimum entry ID and check we only get ID equal or greater
where (e.id>=(select min(l.id) from (select id
from entries
# make sure we order correctly
order by id desc
# set our limit (used for pagination)
limit 0, 10) as l))
# then get the maximum entry ID and check we only get ID equal or less
and (e.id
from entries
# make sure we order correctly
order by id desc
# set our limit (used for pagination)
limit 0, 10) as l))
# make sure we order correctly
order by e.id desc

So, the above code should give you:

id title body tag
100 100 title 100 body 100 tag
100 100 title 100 body 100 another tag
99 99 title 99 body 99 tag
98 98 title 98 body 98 tag
97 97 title 97 body 97 tag
96 96 title 96 body 96 tag
95 95 title 95 body 95 tag
94 94 title 94 body 94 tag
93 93 title 93 body 93 tag
92 92 title 92 body 92 tag
91 91 title 91 body 91 tag

ID 91 is back! And while we actually have 11 rows returned by the query, it’s pretty easy to parse it using PHP to make sure we don’t show the same entry details twice. The only thing to watch out for is that you get the ordering and limits the same in both the min() and max() statements. I hope that’s useful to you.

Wearing different hats…

It’s one of the main things I enjoy about my job that I have to wear different hats. Not real hats, of course, I’m talking about metaphorical hats signifying different roles. Some of these hats I enjoy wearing more than others, and some I absolutely resent even going near as they are way outside my responsibility. It seems like web guys are lumped alongside general IT people when they clearly aren’t the same breed. But I digress.

Here’s a quick rundown of the hats I wear, how much time I wear them (in percent), and how well-fitting they are to me.

Developer Hat: Worn 40% of the time

My main hat is the Developer Hat. It’s plain black with a very small purple PHP logo on it. It fits pretty well, although sometimes I see people wearing the same hat and it’s quite obvious that they were born wearing the Developer Hat and I only got my one relatively late in life.

What most people can’t see is the intricate and complex mesh of technologies that make up the inside of the hat, a mesh that I concieved and constructed with my own two fingers (hmm, note to self, learn to type properly). A mesh of objects, functions, expressions and tricks that I’ve picked up over the last decade and now carry around with me. This is my Developer Hat, and I wear it with pride. It’s a pity that other people see it as a…

Technical Support Person Hat: Worn 25% of the time

That’s right, many people seem to see me as a general helpdesk dogsbody. Just because I work on the internet they ask me things like “Are my credit card details safe on this site?”, or “My screen has frozen, what do I do?” (reboot, muppet) or even “My mouse is going a bit funny, can you help?”.

The Technical Support Person hat is very distinctive. It is bright yellow with a big badge on it that says “Happy to help!”. I hate wearing this hat, it just Isn’t Me.

Let me say this once, in the hope I don’t have to say it again: there are people who are paid to answer these questions, they are called Technical Support. Ask them, not me. I am too busy to figure out how you can print every other page of your document.

Secretarial Dogsbody Hat: worn 10% of the time

Quite a lot of the time I find myself doing menial tasks that could be easily done by a much more junior person than me. The Secretarial Dogsbody Hat, therefore, covers a whole range of scenarios; from checking spelling in web pages to resizing images. While this brain-light work is sometimes a welcome change from the heavy development brain-ache stuff, it’s more often than not an annoying distraction from more important work.

Still, at the moment, there isn’t anyone else available to wear this hat, so I’m stuck with it.

Designer Hat: Worn 10% of the time

The Designer Hat is also quite distinctive. It has a set of tools down the left hand side containing things like ‘Gradiated Fill’ and ‘Bezier Curve’ and sucks up memory like nothing else on earth. It’s not a natural fit for me, but very occasionally I get it a a good angle and someone says “That looks nice”. I’d like to fit inside the Designer Hat better, but most of the time it’s a case of just jamming it on and hoping it doesn’t fall off.

Marketer Hat: worn 5% of the time

The Marketer Hat is slightly wierd. On the ouside it is all smiles and flashy graphics and low, low prices. But on the inside it is hollow, built with bits of string and devoid of soul. The kind of Marketing Hat I’d like to wear is one that is big enough for a whole community to get inside, but those hats are few and far between in big business.

Business Development Person Hat: worn 5% of the time

I like the Business Development Person Hat. It’s a no-nonsense sort of hat, plain and strong, with several useful compartments. The problem is I don’t get to wear it very often because people think that I am only capable of wearing the Technical Support Person Hat. And the few times I do get to wear the Business Development Person Hat most people tend to think I dont know what I’m on about because I don’t have an MBA or wear expensive suits.

Community Facilitator Hat: worn 5% if the time

The Community Facilitator Hat is a fun hat to wear, even if it does mean hard work. It comes with a mop for cleaning up messes, and a megaphone for making announcements. Wearing this hat means you have some degree of power, but it definitely doesn’t mean you have some degree of respect. The Community Facilitator Hat is the rarest of hats to find a perfect fit of, but once you find the right hat it’s there for life.

What most business people don’t realise is that the Community Facilitator Hat, Business Development Person Hat and Marketer Hat could (and should be one and the same.

Open letter to Oxfam…

Dear Oxfam,

I was happy when I heard that you are selling music online. However I wan’t so happy to learn that you only allow Windows IE use only.

Is that because you think Macintosh and Linux users don’t want to give to charity? Is it because you are unaware of the other, more secure browsers available? Or maybe you’ve signed a deal with Microsoft to use their audio file format rather than one of the many other formats available.

Also, your main music page is also built using broken and invalid code. I’ve done a new version, built using web standards which works in all browsers.

The advantages of web standards are clear to see; for example consider that the HTML code of my version of your page weighs in at a fraction the size of your HTML code. That may save you a considerable amount in bandwidth charges when applied across your whole site.

Whatever the reason for the decision to not build your websites using international standards, I’m disappointed and it’s made me have difficulties in using your site. Surely it’s in your interest to make your website as open as possible, so as many people as possible can use it and help to fund your excellent work. I firmly believe that web standards are the way forward to ensure a more open, accessible and scalable future for the web.

Yours sincerely

Chris Taylor

DOMBuilder…

A few months ago I asked the question whether it was possible for some Javascript to parse through a piece of HTML and render it all out using the “proper” DOM methods rather than the sledgehammer-to-crack-a-nut manner of innerHTML. The great Stuart Langridge commented that this woul open up a huge can of worms in case people put non-standard code into it. I agree.

Well, it seems there’s a way to do this from the other side, if you know what I mean. The clever chaps (and chapess) at Vivabit came up with this clever DOMBuilder script that allows you to build snippets of HTML code using the DOM in an almost-easy manner. Well, it’s certainly easier that createElement-ing and appendChild-ing everything.

And it’s also another neat use of the Javascript Object Notation thingamajig. Clever.

Prototype testing, Javascript with recordsets, and JSON…

Today I had the chance to mess around a bit more with the marvellous Prototype Javascript framework which in my opinion just keeps getting better and better. It makes me wish I had started using it 18 months ago when I first started getting (back) into Javascript and this thing called AJAX, it would have saved me a lot of head – and heart – ache. I’d highly recommend it.

However there’s one problem. And that’s tied to the fact that the vast majority of work I do is with databases, and therefore that means recordsets. A recordset, in case you didn’t know, is just a collection of records from a database. Say, a list of the details of all the users with a security level of ‘Administrator’. So, of course, I’m looping those records in PHP or ASP and outputting the result. So far so good. But what if I want to attach Javascript functions to each record, for example toggling a ‘more details’ section?

What I’d traditionally have to do is find out the records I’m going to be printing out, set up some kind of listener for each one at the top of the page, then print the recordset further down. That means two loops. Let me explain, in psuedo-code:

// Get all the users with a type of 'Administrator'
   $recordset = $database->getRecordset("All users who are Administrators");
// Loop all the records
   for each $user in $recordset {
// print each users name
      print $user["name"];
// print a hidden area with further details on this user
      print "";
// print a few more details about the user
      print $user["email"]
      print $user["phone"]
// end the hidden area
   print "";

What I want to do is place a Javascript listener for each username so when it is clicked the hidden area is shown for that area. Of course, using Prototype and all the other Javascript frameworks I’ve seen, you have to add a separate listener for each thing you want to listen for. That makes sense.

However the difficulty is that I need to know about all the usernames at the top of the pge, where I can create my Javascript and put it in the head section, but often I don’t open the database until further down. Yes, I know, I know, doing it all as objects sorts this problem out, but that’s not always practical for older projects.

What I need is a method in Prototype that will allow me to listen for elements with attributes like what I want. In my example (using real code this time) I could create the following using my server-side code:

User 1
   

user1@email.com, 11111&lt/p>

User 2
   

user2@email.com, 22222&lt/p>

User 3
   

user3@email.com, 33333&lt/p>

Then I would set my Javascript code to look for all elements with and ID like ‘user-[whatever]’ and set a listener for each element it finds. Like this:

// Loop all elements with ID like 'user-' then any series of numbers, using regular expression syntax
for (var i=0; i    // get the value from the ([0-9]*) section for this element
   var thisId = document.getElementsByIdLike('user-([0-9]*)')[i][0];
   // for each element found set a listener using Prototype
   Event.observe(document.getElementsByIdLike('user-([0-9]*)')[i].id, 'click', toggleDetails, false);
}

It’s a bit rough-and-ready (what else do you expect from me?) but I think this function would be really useful. Does anyone else agree? Does anyone else get what I’m on about?

The other thing I’ve been using today is JSON, a really lightweight way of transferring data about. It’s as easy as pie to parse it using Javascript, and makes a big step forward from my usual blunderbuss method of returning chunks of HTML from AJAX calls. Maybe this goes to prove I’m moving on in my geekdom. I’ll leave it up to you to decide whether that’s a good thing.