Boblet Learned things and Japan diary, by Oli Studholme

Fun with SQL

By Oli
At 4:38 PM · Friday, 12 September · 2003
To Coding · Weblogging

I’ve been trying to make an index page that lists recent posts from all categories except one. I initially tried Scott Andrew’s MTExcludeCategories, which seemed to work ok, but for the caveat that it doesn’t play well with lastn="number". I read a post from Scott saying that SQL users could try Brad Choate’s MTSQL plugin. This seemed perfect, until I tried to implement Scott’s suggested code and found that categories are assigned via the mt_placement database.

I know nothing about SQL, but managed to produce this before I gave up.

query="SELECT * FROM mt_entry LEFT JOIN mt_placement ON mt_entry.entry_id=mt_placement.placement_entry_id WHERE mt_entry.entry_blog_id = # AND mt_placement.placement_category_id !=# limit 10"

I was getting a result, but each entry was listed multiple times if it was assigned to multiple categories. Here are some SQL links to peruse later

MySQL Manual

The ultimate source for how to use MySQL. However this manual suffers the same problem the W3 specs do - it’s a reference for people who already know SQL.

W3 Schools SQL Tutorial

This is a great introduction for beginners, and even has a SQL query demo you can practice on. It’s only a beginner’s guide though.

SQL Course (and SQL Course 2)

Basically the same as the W3 Schools tutorial, although this one has exercises and an SQL query window on each page to practice as you go.

eVolt’s SQL Links

I expect the perfect resource for me, one that has advanced stuff but doesn’t require you to be a programmer, is in here. I’ve yet to find it.

In the end I changed back to MTExcludeCategories, and substituted days="number" for the problem lastn.