Category: Web development

Dot net, dot schmet…

Posted on by chris

Anyone who’s used a Microsoft Access database in anything approaching a serious application knows that Bill Gates made a serious mistake expecting people to use that “database”. I use the term in it’s loosest sense. Hey, maybe I’m biased as I’ve had some horrific experiences with the dreaded .mdb files.

But one of the bains of my working life may have just got a little easier with the discovery of this little Access query analyser tool by Jeff Key who seems like a knowledgable guy. Well done Jeff for making this cool little utility, even if it did only take you 45 minutes (including 15 minutes for the icon!).

However it brings me onto something which has been bothering me for a little while. That whole .net thing. It’s not that I think it’s bad, after all I use the incomparably wonderful SharpDevelop to write little Windows apps using .net and I love that. Suddenly I feel like I’m (almost) a proper programmer.

It’s the attitude of several of the large web houses I’ve investigated that seem to think the only worthy technology on the web is .net, specifically asp.net, that bothers me. Why? What’s wrong with PHP? Or Ruby on Rails? Or Python (good enough for Google)? Heck, I still spend large parts of my day using classic ASP which works absolutely fine for 95 percent of what I need it to do.

So why the constant hankering after the latest bandwagon? Well, partly it’s because some companies are afraid of falling behind, so they try to keep ahead of the curve all the time. That’s OK, but sometimes you need to stick with what you know and become an expert, rather than jump on the latest thing and make a hash of it.

Some companies don’t understand the technology that well, and so pick the newest thing on the shelf. Using asp.net for a simple 6 page brochure site? That smells dodgy to me. Use the simplest technology possible that gets the job done well, I say.

And, I have to admit, sometimes asp.net is just the right thing to use. It’s easier than Java and Perl, admittedly (even though I Just Don’t Get It). And it’s been used to create some very good web applications, even if it is still too easy to let the IDE do it for you and end up with a dogs dinner of code.

Horses for courses, maybe. I’m sticking with PHP for now, and if I move to developing much in anything else anytime soon it will be RoR or Python (or both). Bill G can keep his over-complex framework in my view, at least for web development.

SQL – Inner joins and limit…

Posted on by chris

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…

Posted on by chris

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.