Working with filters programatically

This is a multi-part tutorial on how to use filters in AppGini. Filters are a powerful means of searching tables. This tutorial explains how they work, and shows how they can be controlled programatically. In part 1, we'll understand how to apply a default filter to a table.

What this tutorial is and is not

This tutorial is a coding tutorial. You'll learn here how to program filters into pages. We'll write some html and php code to achieve this. If you're looking for a tutorial on how to use the filters page to perform a search, please refer to the section working with filters of the online help.

Filters work in groups

AppGini supports up to 80 filters grouped into 20 groups. The filters page exposes only 12 filters grouped into 3 groups. But this is still enough to create complex searches. For example, the filters defined in the screenshot to the right create a sophisticated search of the orders table in the Northwind online demo. Here is a quick preview of the filters page in action (click the "Apply Filters" button to view the filtered orders.)

So, for the example filters to the right, the generated SQL query would be structured like this:

( Filter 01 OR Filter 02 ) AND (Filter 05 OR Filter 06) AND (Filter 09 AND Filter 10)


Filter variables

The filters form in AppGini passes several variables to the application that define filters. If we understand these variables, we'll be able to create default filters, enforce a filter on pages, build customized search forms, ... etc.

Each filter has an index that identifies it. The first filter has an index of 1, the second one has an index of 2, and so on. We could define up to 80 filters (but that could possibly slow down our queries). Each filter is composed of 4 variables, as outlined in the table below.
Variable name in HTML Possible values Details
FilterAnd[i] and
or
Specifies how this filter is combined with the previous one. For the first filter (i=1), there is no need to specify a value for this variable.
FilterField[i] 1, 2, 3, .. etc. Specifies which field in the table is to be filtered. The first field of the table (as defined in your AppGini project) is 1, the second is 2, ... etc.

FilterOperator[i]
equal-to
not-equal-to
greater-than
greater-than-or-equal-to
less-than
less-than-or-equal-to
like
not-like
is-empty
is-not-empty

Specifies how to treat the FilterValue. For versions of AppGini older than 5.40, please refer to the table below
FilterValue[i] Any value we're looking for
Where i is the index of the concerned filter, a value from 1 to 80.

Accepted values of FilterOperator in AppGini versions older than 5.40
FilterOperator Description
<=>Equal to
!=Not equal to
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to
likeLike
not likeNot like
isEmptyIs empty
isNotEmptyIs not empty

Implementing filters in the generated code

In many AppGini applications, we'd like users to view only a subset of data rather than viewing all the records of a table. For example, in the orders table, we might want users to view the orders as of 1/1/2009 only. To do so, we'd write the PHP code for filtering the orders table by date as follows.
/* filter 1 code*/
	/* let's assume the date field is the fourth field in the orders table*/
	addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009');
So, the above code limits the orders table list only orders dating 1/1/2009 or newer. Now, where should this code be placed? It should be placed in the generated "hooks/tablename.php" file inside the tablename_init() function, where tablename is the name of the concerned table. For our example, the file would be named "hooks/orders.php" and the function orders_init(). So, the code in the file would look like this:
function orders_init(&$options, $memberInfo, &$args){
	addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009');
	return TRUE;
}


Using multiple filters

Let's say we want to filter orders to display only the orders of January 2009. In this case, we'll add another filter to our code to limit orders to the period from 1/1/2009 to 1/31/2009 [1].
addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009');
addFilter(1, 'and', 4, 'less-than-or-equal-to', '1/31/2009');
We've now finished part 1 of this tutorial. We've learnt how to enforce a filter on users when they view a certain table. In the next part of this series, we'll learn how to further customize the enforced filters.

Related pages

Creating a customized search form
Applying a default filter on a table


[1] In this tutorial, I'm assuming the date format specified in your AppGini project is "Month, day, year" with a Slash separator. If you're using a different format, you should change the code above to reflect that format.