Tuesday’s Expression Engine snippet

7th November 2006

Sometimes I need one of Expression Engine’s features to work for me in a slightly different way, and that’s when I reach for the Query Module. In a nutshell, this module lets you get data from EE’s databases using standard SQL, then make that data available.

The project I’m working on at the moment is a design portfolio using categories to store client names - allowing work to be viewed by individual clients. Problem is, the navigation is split into three columns, so what I need is a way of pulling out the first six clients, then the next six, and so on. It’s a job for the Query Module: -

We’ll, we’re starting off by using a query tag to pull information from EE’s database tables. The SELECT statement gets data from the category description and category name fields of the exp_categories table, but only for a single category group (WHERE group_id=“1”), sorts them using the category order, and just pulls out the first six (LIMIT 6). Good so far?

Next we use that data to build some category-type links. The query tag loops like a weblog tag until it’s completed each requested row from the table. The code uses the category desciption for the link, and a combination of path, “category” text and the category name to generate a category URL. I’m using the category name in url feature too.

Then for the second and third lists, we just repeat the same code, but with AND cat_order > 6 and AND cat_order > 12 added into the query. Remember junior school maths? It’s the same - these two additions simply say “where the category order is greater than 6” and “where the category order is greater than 12”.

So, a category list split into three using the Query Module. A final tip is to keep referring to ADMIN/Utilities/SQL Manager/Manage Database Tables in the EE control panel as you’re building your query.