Table of Contents

## LookupLens

Lookups are very useful concept in phpLens. There are available in the Basic, Professional, Advanced and Enterprise versions. They allow you to remap codes and display popup menus in the edit and new record screens.

Some examples of usage are:

- Remapping popups: You have a database field containing 2-letter US state codes, and you want to lookup and display the actual state names in a popup, but save the 2-letter codes in the database.
- Simple lookup popups: When creating a record, you want the user to fill out the US state field by selecting from a popup.
- Search Popups/Checkboxes: When you click on the search icon in phpLens, you want to view a list of states as a popup or set of check boxes.

### Remapping Popups

Let's say you have a numeric field containing values 0 to 5 and you want to remap the numbers 0 to zero, 1 to one, etc. So the database will store the 0,1...5, and the popup will show "zero,one,...,five".

Go to column settings of the column you want to lookup. In the lookupLens text field, enter

    =0/zero/1/one/2/two/3/three/4/four/5/five

If you have a SQL table called `digits` containing the following fields:

num | val
0 | zero
1 | one
2 | two
3 | three
4 | four
5 | five

Then enter into the lookupLens field:

    select num, val from digits

Note there is no equals sign as the first line, indicating that we are using SQL. The first column must always be the original value, and the second column the display value.

### Simple Lookup Popup

This differs from the lookup examples above because we are not remapping from one column to another column.

Suppose we have a trading application that can handle 3 currencies: Euro, USD, and Yen. We have a popup that allows the user to select currency. There is no remapping required, we will enter the value selected into the database.

We enter into the lookupLens field (note the double equals):

    ==Euro/USD/Yen

Similarly, if the info is stored in a database table called `forex`, column `currency`:

    select distinct currency from forex

Note that we only need one column in the SQL statement because there is no remapping.

New to phpLens 1.2 is that the lookupLens supports PHP global variables in SQL statements. This is triggered by % as the first character (not = as that is used for text lookups).

### Search Popups and Checkboxes

Go to the search screen and click on one of the column settings. The column settings screen for searching differs from the one used when editing or viewing column info.

The lookups for searching are independant from those used in editing and viewing. This makes it more flexible.

In the example above you can see the edit/viewing lookupLens in the 3rd row (this is not used in searching, it's there to help you formulate your Filter Options search criteria if you get stuck). Filter Options will generate a popup for searching. Just below it is an option to display the search column as a checkbox.

### Hot Updates for Popup Lists

PhpLens 2.0 now supports hot updates using a new lookupLens feature. Have a look at the following property setting:

    $lens->lookupLens =
      "STATE^#select distinct state from cities where country={country};"

observe that the select statement is prefixed with #. This means that all variables enclosed in { } are actually data fields when creating or editing records. So if the COUNTRY popup field is set to "USA" in the browser, then we populate the STATE field with the values generated by:

     select distinct state from cities where country='USA'

If you have codes and text descriptions, you can change your sql so that codes are stored but the more descriptive text is displayed:

`$lens->lookupLens =
"STATE^#select distinct statecode,statename from cities "
."where country={country};"`

#### Javascript

We have integrated these lookups with Javascript so hot updates work in the browser. IE 5-6, Netscape 4.77 and Mozilla 0.9.* are supported. When you change the COUNTRY popup in the browser, we create an invisible browser window and query the web server (actually the page phplens/phplens_remoteq.php) with an SQL statement and retrieve the data from the database. The source code to phplens_remoteq.php is available for your review in the phpLens 2 release.

### The phplensDatabase Property

To keep track of which database that phplens_remoteq.php needs to query, we need to set a second property:

    $lens->phplensDatabase = 'testdb';

This 'testdb' is a one of the databases you defined in the file
phplens/config/phplens.config.inc.php. Eg.

    $PHPLENS_DATABASES =
       array(          //driver, server, userid, pwd, database
       'testdb' => array('mysql','localhost','root','','test')
       );

### Security

We have to embed the SQL and phplensDatabase values in the javascript phpLens generates. This means that the end-user can view the HTML source. We feel that this is not a problem because we fingerprint the SQL and database values using an md5 checksum with a salt you can redefine using

     define('PHPLENS_MD5_SALT','Define Your Own Random String here &*(&^*&#');

at the very top of your PHP script. This means that any hacker attempt to modify the HTML will be detected and rejected.

An added layer of security is that we explicitly check and prevent DESCRIBE, DROP, DELETE, UPDATE and INSERT statements from running.

See also Security for information on using PHPLENS_REMOTE_CODE to globally remap the phplens/phplens_remoteq.php file to another location. Alternatively, you can use the remoteQuery property to remap phplens_remoteq.php for selective phpLens objects.

### Setup of Demo phplens-hotupdatesdemo.php

The setup instructions are simple.

1. Run phplens-demodata.sql on your database. This will create 2 tables, "cities" and "people".

Then modify the phplens-hotupdatesdemo.php file to suit your environment. This means:

2.. modify the include('/path/to/phplens.inc.php') to the correct path.

3.. modify the database connection parameters to suit your database.

4. add 'testdb' to your $PHPLENS_DATABASES in the file phplens.config.inc.php

5. try it!