Table of Contents

Searching and Filtering

When users clicks on the search icon, a search screen will appear where they can search for text strings and numeric values. In the following example, you can search from a popup, use checkboxes to select multiple "Or" criteria to search on, and enter search criteria in a text box. You can see that we can also search for all products with less than 10 units in stock. All text boxes allow you to enter comparison operators (=, !=, <>, <, >, <=, >=).

You can search for dates using the format defined by the dateInput property. Two digit years will wrap around correctly.

Searching by Range

You can perform range searches using the words to and until since phpLens 2.6.0. For example to search from 10 to 20, you many enter either:

10 (to) 20
10 (TO) 20
10 (until) 20

To search for all countries from Argentina to Nigeria you can use either:

Argentina (to) Nigeria
Argentina (until) Nigeria
Argentina(TO)Nigeria

The words used to identify a range is controlled by the language array $lens->lang->betweenTxt (defined in the language file phplens-lang-??.inc.php). You can override this to use your own language. For example, in Bahasa Malaysia, we can set it to:

$lens->lang->betweenTxt = array('(ke)','(hingga)'); // ke==to, hingga==until

We still support the old >< operator. To search for all countries from Argentina to Nigeria, use:

>< Argentina,Nigeria

This will generate a query where 'Argentina' <= Country and Country <= 'Nigeria'.

Searching with Match Anywhere

You can specify that a particular field searches using match anywhere, overriding the default match settings using the =% operator. For example, to search for the name PISTON anywhere in a particular field, enter:

=%PISTON

Whether the search is case-sensitive or not depends on the database.

Searching using NOT LIKE

If you search on a text field and the first character begins with ! (and does not match !=):

!APPLE

will generate the following SQL:

SELECT * FROM TABLE WHERE FIELD NOT LIKE 'APPLE%'

Find More

To enable Find More, you must set $lens->showFindMore = true.

Dates

Date input format is controlled by $lens->lang->fmtDateInput (eg. 'd-m-Y'), and can be found in the phplens-lang-[language code].inc.php files.

In phpLens 4.2 and later, we have improved the date entry, so the following will represent the same date and time(assuming d-m-Y format):

 2 Jan 2005 3pm
 Jan 2 2005 15:00
 Jan 2 2005 15
 2 1 2005 15 Pm
 2/1/2005 3 pM
 2-1-2005 3 PM
 2,1,2005 3 pm
 2005 Jan 2 3 pm
 2005 2 Jan 3 pm

We are also able to detect swapped days and months, so the following are identical:

31/1/2005
1/31/2005

Relative Date Functions

Relative dates are also supported since 4.2 using the new YEAR, MONTH, WEEK and DAY functions. These can be entered directly into the search input fields. This makes generation of reports much easier.

Some examples will clarify their usage:

Configuring Search

The following configuration screen is diplayed when you click on the white Supplier icon. Note that Use Checkboxes is enabled. The filter hint is taken from the lookupLens settings, but is not applied in the search screen automatically so that you can choose whether to display a text box or popup.

How Do I...	Solution	Property modified
Decide on which fields to display	Click on the green icon to see the Filter Columns screen. Then move the fields from left to right to display, and right to left to hide.	filterLens
Change the field title	Click on the white icon for the field you want to modify and change that field's title.	nameLens
Use a popup instead of a text field	Click on the white icon for the field you want to modify and set theFilter Options you want for the popup.	filterParams
Use checkboxes	Click on the white icon for the field you want to modify and enable the Use Checkboxes.	filterParams
Change default string search matching	You want to change the default from Match From Beginning (1) to Match Exactly(0) or Match Anywhere(2). Set the filterUseLike property programmatically.	filterUseLike
Start phpLens with the search screen	Click on the green icon to see the Filter Columns screen. Click on phpLens Settings at the top of the screen. Scroll past the color settings, and set Start phpLens to Search Form.	firstState
Hide the search form once a search is initiated	Set the filterShowForm property to false.	filterShowForm
Show a search form with some filter criteria defined	Enter the search criteria, click on the Search button, and save the URL generated. Use the URL to go to the applet. The filter settings are saved in the GET parameters.