Developer › Tutorials for Web Developers & Designers › Advanced

Database Filters

Filters let you control data when working with dynamic elements. They are commonly used with templating tags in a page's markup:


But can also be used in PHP scripts:

Filter Structure

Filters take the form of PHP arrays, and are structured so that they can be read left-to-right (or top-to-bottom, as with the following examples). The following examples should illustrate how their structure allows for both simple and complex filters:

Show me pages that are visible in navigation:


Show me pages that are visible in navigation and have a page icon:

Order of Operations

When writing complex conditions that contain both AND and OR operations, it's essential to be aware of the priority in which they are evaluated.  Similar to algebraic rules, AND is prioritised over OR.

In the example:
[[cond1], 'OR', [cond2], 'AND', [cond3]]

Will evaluate in the order of:
cond2 AND cond3 OR cond1

To reorder them, you can add extra brackets:
[[[cond1], 'OR', [cond2]], 'AND', [cond3]]

Differences Between Controls and PHP

When defining filters using the datafilter="" attribute in your markup, you only need to worry about the filter condition array. However functions in the PHP API often expect a Properties Array, which contains a Filter Array plus extra properties.

Filter Reference

Comparators

<
Less than
Compare numbers, dates and strings.
<=
Less than or equal to
Compare numbers, dates and strings.
>
Greater than
Compare numbers, dates and strings.
>=
Greater than or equal to
Compare numbers, dates and strings.
=
Equal to
The values must match exactly.
!=
Not equal to
The values must not match.
LIKE
String similar to
Match patterns. For example, 'contact_first_name', 'LIKE', 'J%', will match on all people with a first name beginning with the letter 'J'.
NOT LIKE
String not similar to
Exclude patterns. For example, 'contact_postcode', 'NOT LIKE', '4%', will exclude all people with a postcode beginning with four.
IN
Equal to any of the supplied values
Match on multiple possible values. For example, 'design_id', 'IN', [1, 2, 10], to match pages with 1 of multiple designs.
NOT IN
Not equal to any of the supplied values
Exclude multiple values. For example, 'contact_postcode', 'NOT IN', [1001, 1002], to exclude Contacts registered in the specified post codes.

Boolean Operators
AND
All of the filter conditions must be true for each row.
OR
At least one of the filter conditions must be true for each row.