Month: August 2005

Ask and you shall receive!

As you may be aware, I allow anyone to link to the images on my site. They don’t have to ask permission, they don’t have to pay. All I ask is that people let me know so that I can keep a track of who’s using them (most people don’t, but can always check my server logs to get those sites).

However many people are in a different position to me, where others’ use of their images becomes unreasonable and starts to cost a lot of money in bandwidth charges. Add to this the fact that some website owners run businesses based on those images, and you can easily see why totally unrestricted use of web content can be a very negative thing.

Now, I’m all for free. I use free software, I subscribe to free blogs, I even offer free software myself, so it’s not like I’m against the relaxing of the corporate shark-pool mentality regarding copyright. However, Dave at Cartoon Church relies on his images for his business, so people shouldn’t steal them.

Or maybe I just don’t “get it”.

I’ve sold my car. I am now a pedestrian again. Well, not quite as we still have one car between us, which I can use occasionally. But still, it was a sad day. So, rest in peace, you hunk of junk.

Wise words from Aaron Reed. And a truth that many managers don’t realise:

Software development is not your standard ?sit at your desk? job. It?s the equivalent of taking a difficult test 8 hours a day, 5 days a week (plus overtime).

Thanks to the genius of Simon, your friendly neighbourhood SQL man, I now have a solution to a problem that has bugged me for ages. Let me paint you a picture:

You have a list of categories in a database table. You have a list of items in another table, there are many items in each category. The categories and items are linked, the field “category_id” in the categories table is used in the “category_id” field in the items table.

So, what you want is to show a list of all the categories, a count of the number of items in that category and the name of the first X number of items in that category. And here’s what you’d do:

select c.category_name, i.item_name,
(
select count(item_id)
from items where category_id =
i.category_id
) as countitems
from items i
inner join category c on c.category_id = i.category_id
where (
select count(*) from items
where item_name >= i.item_name and category_id = i.category_id
)
order by c.category_name, i.item_name

Magic. You get exactly what you want, all in a single SQL query. And by changing the number (where it says “2”) you can bring back however many items from each category you want. Using this you could easily display the following:

Category 1
Cat1_Item1, Cat1_Item2 (37 more items…)

Category 2
Cat2_Item1, Cat2_Item2 (19 more items…)

Category 3
Cat3_Item1, Cat3_Item1 (140 more items…)

And that must be useful for somebody. All hail the great Simon!