collylogic.com - Simon Collison's weblog

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: -

<ul>
{exp:query sql="SELECT cat_description, cat_name
FROM exp_categories WHERE group_id=’1’
ORDER BY cat_order LIMIT 6 “}
<li><a href="{path=work/client}category/
{cat_name}">{cat_description}</a></li>
{/exp:query}
</ul>

So, what’s going on here?

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.

Tomorrow, we’re getting all dynamic with EE.

Simon Rudkin published this on 07/11/06, at 2:56 PM

Comments

I’m enjoying these one-a-day snippets. It is a bit like Jackanory, but about EE rather than Roald Dahl books. Same time of day too.

Maybe we could get Rik mayall to narrate the podcast, and Quentin Crisp Blake to do the illustrations?

07/11 at 17:01 from Simon Collison

The Query module is in fact very mighty.

But I’m wondering why in this example we can’t just use the ordinary weblog:entries tag (with limit=18") with conditionals like 

{if count == 6 OR count == 12}< /ul>{/if}< /ul>
?

10/11 at 23:31 from Marcus

Note that comments are disabled 30 days after articles are published, or sooner if spammers target a particular post. Sorry...