Table of Contents

LookupLens

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

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.

You can also embed PHP global variables in the SQL. Precede the SQL with an %

%select value,caption from map where key=$key

Simple Lookup Popup Menu

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.

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.

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 Popups

Suppose we want to perform multiple related lookups on the same page, without refreshing. We want to select country, and all the states for that country appear in the next popup without a page refresh. We then select state, and all the cities in that state appear in the following popup. This is what we call hot updates, field refreshes which do not require a full screen refresh.

This is triggered by the # in the beginning of a lookup:

$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};"

The related field (eg. {country} in the example above) for a hot update does not need to be a popup menu, but could be a text field too.

Hot Update Internals

Hot updates require javascript integration. 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.

The phplensDatabase Property

To keep track of which database that phplens_remoteq.php needs to query, we need to set this 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')
   );

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!

Scalability of Lookups

You have to be careful of scalability when your lookup requires you to retrieve thousands of records to match one value. If your lookup has more than 100 values, we would recommend that instead of using a lookup in the grid or detail views, use a SQL join. For example you are viewing sales records; you have a field sales.productid which maps to the product.prodid field, and you want to display the product.prodname field. Use this:

$lens->sql = "select p.prodname, s.order, s.price from sales s, product p 
               where s.productid = p.prodid";

instead of the less efficient (but more convenient to code):

$lens->sql = "select productid, order, price from sales s";
$lens->lookupLens = "productid^select prodid, prodname from product";

For similar reasons, when editing or creating records, use the searchLookupLens property instead. When editing, it displays a text field where you key in a few characters to start with. Then you click on the search button attached to the field. All lookup records that match the search will then be shown in a popup menu. This ensures that instead of the whole lookup table (which contains thousands of records) being retrieved, only a few records are extracted in one search.

In the example below, we have a searchLookupLens for editing sales records where you can modify the product name:

We do this by setting the following, where {#} is the text entered:

$lens->searchLookupLens = 
  "prodname^select name from products where name like '{#}%' ";

You can define a minimum number of characters to enter in your search. In the following example, at least 2 characters must be entered (the >2):

$lens->searchLookupLens = 
  "prodname^select name from products where name like '{#}%' ^>2";

And we can force phpLens to modify other values dynamically. In the above example, when the user chooses a product name, you can make the supplier and category update its values too. Given products (name, supplier, category) needs to be copied to sales (sname, ssupplier, scategory), do the following:

$lens->searchLookupLens = 
  "prodname^select name,name,supplier,category from products where name like '{#}%' 
          ^sname^ssupplier^scategory";

In the SQL, select name,name is repeated twice because the first name is the text to display in the popup menu, the second name is the value to copy to the sname field. In this way, you can even modify the popup menu values to display more information, for example, the price:

$lens->searchLookupLens = 
  "prodname^select name||':'||price,name,supplier,category from products where name like '{#}%' 
          ^sname^ssupplier^scategory";

As the searchLookupLens is very complicated, we recommend looking at the ex919 and ex920 examples and the property description.

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.

This documentation system is maintained using phpLens