Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in /var/www/html/lens/lensman/gen/propallpage.php on line 30 allowEmptyFilter
Filtering and Searching
allowEmptyFilter
When user performs an empty filter/search, show all records?Default: If set to false, when no filter/search criteria is defined, no query is performed.
Setting to true seems to be more natural, while false will improve database performance.
Always save even if record has not changed when set to trueDefault: Before saving a record, we check if the record has changed. If it is not changed, we normally do not save it again as an optimization. Setting
$lens->alwaysSave = true;
overrides this default behaviour. Useful when all records in editMultiple have to be saved in a specific sequence.
Store blobs are downloadable attachmentsDefault: Attachments are files which are stored in the database. These are stored using blob, image or bytea (for postgresql) types. When you store the attachment, phplens will also store the file name and file size in readable format in a separate varchar field. If the blob field is called myblob and the varchar field is called myfiledetails, then set
$lens->attachment = 'myblob;myfiledetails';
The second field (myfiledetails) is optional.
You need to configure your phplens SQL statement in a special manner when using attachments. If the table is called myfiles, and has a primary key id then the following SQL is required:
$lens->sql = 'select id, myfiledetails, 0 as "_ATTACHMENT_" from myfiles';
You do not directly access the attachment fields in your SQL because you don't want to retrieve the attachment data (it could be a several very large attachments) everytime you view the phpLens grid. _ATTACHMENT_ is a special alias to the actual field, with a dummy value (0 in this case, though it could be any constant value). PhpLens will detect the _ATTACHMENT_ field and will display a download link when viewing the grid or details. When editing or creating records, the standard upload field form will be shown.
The file info field, myfiledetails in the above example, is also special. You can make it visible in the grid or details. But make sure it is readonly or hidden in the edit/new record form. This field will be automatically filled with the file name and size on record update.
The maximum size of an upload is determined by the imageMaxSize property. There can only be 1 attachment field per record.
Blob Header Format
All BLOBs begin with a header. The first bytes of the header are defined by a constant LENS_ATTACH_HDR in phplens.inc.php. The final bytes of the header consists of '/'.LENS_ATTACH_HDR. Between the header and the end of the header is the header contents.
In phpLens 3.5.0, the header contents is the name of the attachment. In phpLens 3.5.1 and later, it is a MIME content-type definition that looks like this:
Sets tag attributes for date and timestamp input fieldsDefault: Typically used to set the CSS class selector of input fields for date and timestamp fields when editing or creating records.
Makes phpLens autogenerate an integer to be stored into keyCol when inserting a new recordDefault: Set this property to true to enable generation of keys by phpLens. If you want to use a custom generator, set this property to the name of the PHP function that returns the key. The generated key is also passed to the eventPostInsertSQL property, and also stored in the generatedKey property after Render() is called.
Note that is only required on selected databases which do not have an auto-incement primary key, including early-PostgreSQL versions, Interbase and Oracle (oci8) currently. Internally, phpLens will create sequences or tables to manage the integer generation.
If you are using autoincrement columns generated by the database (eg. mysql or mssql), you can get the last inserted value using $lens->connection->Insert_ID();
Regular expression describing which tags are banned for data entryDefault: Only works if htmlLens is set for that column. Otherwise all tags are converted by htmlspecialchars() to their html entities. Uses Perl compatible regular expressions.
Note: since phpLens 3.0, we no longer ban table, th, td. tr tags by default. This is because the new htmlEditLens allows HTML tables to be created.
Set table borders to 0Default: 0 = use table borders set to border="0"
1 = no borders in cells nor around table
2 = no borders between cells, but place a border around table
It is recommended you do not manually modify this property in an editor, but use the Dynamic Editor as phpLens does special processing on this property.
If you want to manually change the borders, phpLens will not modify the border if you set the border without the quotes, or uppercase the attribute, eg.
Cache data in $ADODB_CACHE_DIR for cacheData secondsDefault: Make sure that the global variable $ADODB_CACHE_DIR points to the correct directory. See cacheLookups property for more examples.
Data is only cached if the data is set to readonly (no editing/deleting/inserting). Do not set this to true when you have multiple Web servers accessing the same data.
And if scrollLinks is set, then the number of links to display is also cached. This occurs whether or not the data is readonly. In other words, the SELECT COUNT(*) performed by scrollLinks is cached.
Cache lookup values for cacheLookups seconds in the $ADODB_CACHE_DIR directory.Default: PhpLens uses the ADODB database library internally. ADODB has the ability to cache queries and recordsets in a directory defined by the variable $ADODB_CACHE_DIR. You should define this constant at the top of your script.
This property can speed up phpLens significantly by reducing queries to the database.
Show Delete button and allow deletesDefault: Clicking on the button will ask for confirmation from the user if Javascript is enabled on the browser.
The icon can be modified. It is configured in the language class $lens->lang->iconDel.
Also requires the keyTable and keyCol properties to be defined.
If you want only selected rows to be deletable, you can define an additional column called "_CANDELETE_" and set the value to 0 or 1. Non-zero values means that the row can be deleted. This is available since 2.2.0.
For example, set $lens->sql to the following if you want the user to only be able to delete products that begin with 'A' (assuming your database supports CASE)
SELECT productname, supplier, unitinstock, unitprice,
CASE
WHEN substr(productname,1,1) = 'A' THEN 1
ELSE 0
END "_CANDELETE_"
FROM products
Show Edit button and allow record updatesDefault: Also requires the keyTable and keyCol properties to be defined.
The icon can be modified in $lens->lang->iconEdit.
If you want only selected rows to be editable, you can define an additional column called "_CANEDIT_" and set the value to 0 or 1. Non-zero values means that the row is editable. Available since 2.2.0.
For example, set $lens->sql to the following if you want the user to be able to only edit products that begin with 'A' (assuming your database supports CASE)
SELECT productname, supplier, unitinstock, unitprice,
CASE
WHEN substr(productname,1,1) = 'A' THEN 1
ELSE 0
END "_CANEDIT_"
FROM products
Fields listed in this lens are displayed as chartsDefault: The field must be of type integer or numeric. There are two types of charts you can create.
Bar Charts
The first is a horizontal bar chart. If the chart has both negative and positive numbers, you can choose two different colors for the negative and positive values.
Chart dimensions must be set for bar charts. This is the maximum size of a chart. Set the width and height.
To fit the chart into the chart dimensions, phpLens needs to estimate the maximum and minimum values of the field. You can provide an SQL statement to calculate it here, or hard-code numeric values (this is faster).
Icon Chart
The other chart is the icon chart, which shows a picture for every Scale units (rounded up).
Setting Chart Min/Max
The scale of the chart is determined by these settings. Chart Min is the smallest possible value and Chart Max is the largest possible value in that column. You can set this value manually (default), or execute an SQL statement to do this, normally:
select min(col),max(col) from table
From these values and the Chart Dimensions, phpLens is able to scale the chart correctly.
Stores charting parameters set when dynamic editing enabledDefault: If you need to save the chart settings as source code, set your charting parameters using dynamic editing, generate the PHP code, then copy and save the chartParms and chartLens properties.
Syntax Not documented currently because the format will change in future versions.
Will make a set of checkboxes appear on the grid, one for each row. There will be a menu to select "Move Records" and "Hide Records". Make sure that the keyCol property (defines the primary key) is also set to ensure that the checkboxes return something meaningful.
To process records, check multiple records, then select an item from the menu, say "Move Records", then click on Go. This will cause a POST to occur, with $id being the applet id:
$_POST[$id.'__cbopt']
set to 'move'
$_POST[$id.'__cbstr']
Primary keys of the checked records stored as a string, delimited by the value of $lens->checkBoxesSep, eg. '!#@'.
$_POST[$id.'__cbnotstr']
Primary keys of the unchecked records stored as a string, delimited by the value of $lens->checkBoxesSep, eg. '!#@'.
Then you will have to write some PHP code to process this $_POST yourself, eg.
if (isset($_POST[$id.'__cbopt']) && $_POST[$id.'__cbopt'] == 'move') {
$arr = explode('!#@', $_POST[$id.'__cbstr'];
foreach($arr as $primkey) {
# some code to move records based on $primkey ...
}
}
The checkbox posting code uses javascript to post the data, so will not interfere with any form tags you have embedded inside the grid, or the editMultiple property form tags.
Special Flags
_auto_
The default dropdown menu has a Go button that needs to be pressed before the form results are submitted. Setting this flag will cause selecting from the dropdown menu to submit the results.
When a user selects 'move', then all checked records will be updated with 'move':
UPDATE $keyTable SET statusfld='move' where (statusfld != 'move' or statusfld) is null and $keyCol=$checkedPrimaryKey
The $updateflds parameter is useful when you need to update additional information. For example:
UPDATE $keyTable SET statusfld='move',modified=sysdate where (statusfld != 'move' or statusfld) is null and $keyCol=$checkedPrimaryKey
_col_^$colname[^$emptyval][^$updateflds]
This ties the checkbox to a database column. Useful for approvals and similar functionality. When the user changes the field, the database is also updated.
If the value of the field satisfies the PHP empty() function [null or 0 or '' or '0' or false], then the checkbox is unchecked, otherwise the checkbox is checked.
In this mode, only the records that are changed (clicked on by the user) are POSTed to the server.
So records that are checked will be updated with:
UPDATE $keyTable SET select_field=1 WHERE (select_field!=1 or select_field is null) and $keyCol=$checkedPrimaryKey
and records that are unchecked:
UPDATE $keyTable SET select_field=0 WHERE (select_field!=0 or select_field is null) and $keyCol=$uncheckedPrimaryKey
The _update_ flag is ignored in when _col_ is defined.
The $updateflds parameter is useful when you need to update additional information, and is used in the same way as in _update_.
Replaces the details grid with an editor to create/edit recordsDefault: Legal settings:
Create and Edit
New record form is the default, unless a record is selected for editing
Edit Only
Always show the edit record form for the current record
Create Only
Always show the new record form
Edit and Create
Show edit record form for the current record is the default. When the new record icon is clicked, show the new record form in details
This is called childLens because internally, it creates a second phpLens object(the child) to do the editing.
Syntax:
$lens->childLens = '{id of child phplens}[;editdetail][;newdetail][;editfirst]';
The string editdetail is a constant that specifies editing is allowed in the detail grid.
The string newdetail is optional and when defined, will display the create new record screen in the detail grid when no record has been clicked on. The editdetail constant must also be set for newdetail to work.
The id of the child phpLens should be a unique value that is not used by other phpLens objects.
Since phpLens 2.4, both editdetail and newdetail are optional.
Since phpLens 2.4.8, a new optional setting, editfirst is available. When makes editing the default instead of creating a new record if both editdetail and newdetail are defined.
Color of even rows in gridDefault: Set the bgcolor in the TR tag. Addional TR properties can be placed after the color, for example:
$lens->colorEven = 'yellow valign=bottom';
Dynamicly change color of column or cell, and column attributes too.Default: You can use this property to enter a grid background color such as 'white' or 'black', or use the numeric RGB values where #000000 and #FFFFFF is white.
To set dynamic colors in your columns, set the first character to an equals (=), then you can use the PHP's ternary logical operator or your own pre-defined function to perform checks and comparisons.
PHP has a nice way of allowing you to choose between multiple values in one line of code: = (expr) ?
"value if expr is true" : "value if expr is false"
An example is given a column named UnitsInStock, and we want to color the column red if the units are less than 10: =({UnitsInStock} < 10)? "red" : ""
You can also use any global variables defined in the $GLOBALS array using {$VARNAME}, for example {$PHP_SELF}.
The colorLens property can access any column in the current row using the {columnname} syntax just like the powerLens.
Additional Macro Variables Available
{NBSP} = ' '
{SEMICOLON} = ';'
{_HILITE_RECNO_} = current record number
{_LENSID_} = the phpLens object's id
{_ODDC_} = the odd row color
{_EVENC_} = the even row color
{_SELECTC_} = the hilite color for the current line
{_HASDETAILS_} = whether the detail grid is visible
Changing Column Attributes
You can also change column widths with this property by taking advantage of a side-effect. Any attribute can be added to the TD tag using colorLens. See example in syntax. Note that setting the column width is discretionary. The browser can override this setting if it wants to.
However since phpLens 2.6, we have the tdLens property, which allows you direct access to all td attributes. Note however that tdLens does not do column interpolation (eg {col1}) nor php code processing (with =).
Title and Input Background Colors
Since phpLens 4.9, you can control the background color of the detail, edit and new titles, and the input background colors. To set the title background color for field FIELD1 to black, the title font to white, and the input background to lightyellow, use
Color of the navigation bar cellsDefault: This property determines the color inside the menu option cells. ColorNavBorder sets the border color of the cells.
Color of odd rows in gridDefault: Set the bgcolor in the TR tag. Addional TR properties can be placed after the color, for example:
$lens->colorOdd = 'yellow valign=bottom';
Color of column/field title cellDefault: Set the bgcolor in the TH tag. Addional TH properties can be placed after the color, for example:
$lens->colorTitle = 'yellow align=right';
Number of columns to display in gridDefault: Auto-Resizing
If $lens->columns > 1 and $this->pageSize >= 1000, then phpLens will auto-resize the grid to fit all records in one page by adjusting the number of records per column.
Holds the ADOdb connection objectDefault: This represents the connection to the database. You can access access the connection resource directly using $lens->connection->_connectionID.
See also rs, the recordset property, and the ADOdb manual.
Instead of using the default connection, use this connection for sql lookupsDefault: There are 2 ways to use it. First is to set it to an existing database connection:
CSS include file.Default: CSS file to include that contains CSS definitions. Should be the absolute path if defined. See the CSS Blue and CSS Red examples, which make use of the default phplens/img/phplens.css file.
'VIEW' means view the normal grid
'EDIT' means edit a record
'FILTER' is when we are in the search screen but have not initiated a search.
'FILTERVIEW' means we have initiated a search
'NEW' is when we are creating a new record
'EDITSAVE', see below, has been available since phpLens 2.4.2.
'HIDE' hides the phplens object. Available since phpLens 3.2.
This property is read-only and should not be changed directly. To force a record edit or to create a new record or to force a search, use GET variables instead, as documented in the FAQ at http://phplens.com/lens/faq/faq.php?#402
New to phpLens 2.4.2 is the EDITSAVE state. Set firstState = 'EDITSAVE' when we want to display an EDIT screen in firstState, but after we successfully save, we want switch to VIEW.
Also if firstState is EDIT and we click on Save and save was successful, we set curState to EDITSAVE. This is a state that is otherwise identical to EDIT. To maintain backward compatiblity, to detect EDIT state, we suggest using
substr($this->curState,0,4) == 'EDIT'
HIDE
The curState property is normally read-only, but you can hide the phpLens object for 1 page request by setting $lens->curState = 'HIDE'.
Print SQL generated by phpLens and halt execution.Default: Useful for debugging poorly optimized SQL that is running too long. The SQL is not executed.
On new record, these are the default values of specified fieldsDefault: When creating a new record, you might want a default value to be automatically entered. You can enter a normal string (without quotes) or number, or generate the value dynamicly using PHP or your RDBMS.
Default values can be hard-coded values, or you can execute PHP code if you prefix with the equals(=) character.
Dates can either be generated in Unix timestamp format provided you return the value as an integer, or in 'Y-m-d H:i:s' format.
Executing PHP Code
When you want the value to be dynamically generated by PHP, set the first character to an equals (=) to treat the following text as PHP code.
To generate the current date, use the PHP time function like this:
=time()
PHP Globals
To access global variables, before 3.2, you needed to use the following:
=$GLOBALS['PHP_SELF']
Since 3.2, you can use this directly:
=$PHP_SELF
Database Example
To access the ADODB connection from here, use the following example, which passes the connection object to the getvalue function:
Executing SQL Code
Sometimes, you have a read-only field whose value is generated by your SQL server and not by the user. You can do this by setting the first character to percentage (%) to treat the following text as SQL code. Since 2.5.2, you can also put PHP global variables in the SQL string.
To set a column to a random number in Microsoft SQL server, use the rand function like this:
%rand()
Syntax To set a field called thetime to the current datetime:
$lens->defaultLens = 'thetime^=time()'
To execute a standard SQL function for a field called rr:
The separator used to display the checkbox choicesDefault: When selecting multiple checkboxes, the choices selected are stored in the field, using the displaySep as the separator. For example, you have a set of checkboxes storing the following values: "Giraffe", "Lion", "Zebra". The user selects the first two values, so we store in the database "Giraffe, Lion".
If the displaySep was '||', then we would store "Giraffe||Lion".
Turn on dynamic editingDefault: Use this to determine whether users can modify phpLens dynamic settings.
When dynEdit is disabled, all dynamic edit settings are not loaded from the phpLens table. So make sure you Generate PHP Code and paste your changes into your .php file before turning off dynEdit.
Sometimes the phpLens source code settings are in conflict with the dynamic editing settings. In this case you should Generate PHP Code and save it as a backup. Then click on the Remove Settings to wipe out all dynamic editing settings that might be in conflict with your PHP source code. Finally, manually merge any settings from the backup to that are missing in the original source code.
Use dynamic editing settingsDefault: Set this to false if you want to ignore dynamic editing settings. This will speed up phpLens because phpLens will not query the phpLens table to get configuration settings.
This means that all dynamic editor settings are ignored. When using applets, dynUseSession is always set to false.
Set to true to edit a record when you click anywhere in a rowDefault: Due to problems with browser incompatibilities, this property is no longer supported. Though we will leave it available for backward compatibility, we will not entertain any support requests regarding using this property.
Set to true to enable.
This only works when the keyCol property is defined (eg. the primary key is known), editMultiple = false and no child editor is configured (childLens property).
Define hidden input tags to be included in edit formDefault: Any invisible tag can be defined here. These tags can be used to embed any additional information that you require for your own processing.
This lens sets which fields are shown when editingDefault: This is normally combined with readonlyLens for fields that are viewable but not modifiable.
Allow editing of multiple records in the gridDefault: Requires the keyTable and keyCol properties to be set.
If the column is defined in both editLens and gridLens, the column is editable. If the column is defined only in gridLens, then the column is read-only. The details grid is not modified by this property and remains read-only. Works with popup hot updates, must-fill checking and also type checking.
If you are using the templateGrid property, then make sure you display the _RECNO_ because some of the hidden input fields are stored with the _RECNO_.
Currently the main limitation is that client-validation with javascript is not supported.
Commits
Before 3.1, the saving of each record is treated as a separate transaction. So if 3 records are saved, then 3 transactions are generated. In 3.1 and later, all 3 records will be saved in 1 transaction.
Always Saving
We normally check to see if record has changed before saving. You can force saving to occur by setting $lens->alwaysSave = true. Since 4.0.1.
New Records
See the newMutiple property. Since 4.1.
Control checkbox style and formating in edit/new record formDefault: All checkboxes and radio buttons are laid out in an invisible table. This allows you to control the cell formatting of this invisible table, to set font sizes and other formatting.
Since 4.9, you can choose to have editNotes appear only on NEW or EDIT state by prefixing with ?NEW? or ?EDIT?. For example the following note will only appear in EDIT mode.
If true, then zero length strings are stored as nullsDefault: This applies when the metatype is 'C' or 'X' (char and varchar) and strlen($data) == 0. Note that we do not trim the data first, so if you want to also strip off white-space, you will have to do it manually using spEdit or spNew properties.
Syntax # the following fields will store null when the string is empty.
$lens->emptyCharIsNull = 'field1;field2';
Define errorHandler function that is called when errors occurDefault: Returns true if error should be shown, false to ignore the error. The parameters are:
$msg = holds the error message
$severity = one of {LENSERROR_SEVERE=1, LENSERROR=0, LENSWARNING=-1}
$objid = the phpLens object id
Calls PHP function defined here whenever the detail view changesDefault: This event will be triggered as long as a keyCol primary key has been defined.
The easiest way to explain this is to give an example:
The detail view is useful for emulating user interfaces such as email readers. Whenever we view an email, we want to mark the email as read. So we do the following:
function MarkAsRead($id)
{
global $DB;
$DB->Execute("update table set read=1 where id=$id and read=0");
}
Name of function to call after record is deleted.Default: The function is passed 3 parameters. The first parameter is $key, the primary key. The second is $deleteok which is set to true if the record was deleted correctly. The 3rd parameter is the $lens object.
Syntax $lens->postDeleteSQL = 'DeleteChildren';
function DeleteChildren($key,$ok,$lens)
{
if ($ok)
$lens->connection->Execute(
"delete * from childtable where parentid=$key"
);
}
Name of function to call after New Record is createdDefault: The function is passed 3 parameters. The first parameter is $key, the primary key. The second is $insertok which is set to true if the record was inserted correctly. The 3rd parameter is the $lens object.
Due to a bug, in phpLens 4.0.4 or earlier, $insertok returned the strings 'true' or 'false'. In 4.0.5, $insertok will return 'true' (string) if the insert succeeded or false (boolean) if the insert failed.
Note that some PHP extensions do not support the insert_id() function such as Oracle/OCI8 and Interbase (we set $key to the OID in PostgreSQL). For those databases, the $key variable will be empty. If you are using sequences to set the default value of the primary key, then you can still obtain the current value of the sequence. For example if you are using oci8 and $seqname holds the name of your sequence:
$rs = $lens->connection->Execute("SELECT $seqname.currval FROM dual");
$key = $rs->fields[0];
Syntax function preinsert($lens)
{
GLOBAL $HTTP_POST_VARS;
$lens->connection->BeginTrans();
}
function postinsert($key, $insertok, $lens)
{
if ($insertok) {
$rs = $lens->Execute("insert into childtable (parent) values ($key)");
if ($rs) $lens->connection->CommitTrans();
else $insertok = false;
}
Name of function to call after record is changedDefault: The function is passed 3 parameters. The first parameter is $key, the primary key. The second is $updateok which is set to true if the record was updated correctly. The 3rd parameter is the $lens object.
Syntax function preupdate($key, $lens)
{
GLOBAL $HTTP_POST_VARS;
$lens->connection->BeginTrans();
}
function postupdate($key, $updateok, $lens)
{
if ($updateok) {
$rs = $lens->Execute("update table set values=??? where parentid=$key");
if ($rs) $lens->connection->CommitTrans();
else $updateok= false;
}
Name of function to call before record is deleted.Default: This function will be passed 2 parameters, $key, the primary key of the relevant record, and the $lens object. This function is typically used to initiate a transaction. The transaction is completed in the eventPostDeleteSQL function.
Name of function to call before every field is processed for new recordsDefault: This function should have 3 parameters. $colname is the field name, &$data is the data and $type is the ADODB data type (not the native type).
For $data, dates are passed as strings in 'Y-m-d h:i:s' format, eg. 4pm 23rd February 2002 is '2002-02-23 16:00:00'. Strings are passed without quoting. Do not quote strings yourself because phpLens does it for you.
For $type, legal values are X (long text), B (blob/image), D (date), T (timestamp), C (char), N (numeric), I (integer). See ADODB readme.htm documentation.
Return a value of
-2 to cancel the insert, show the New Record form, and clear all fields
-1 to skip this field when inserting,
0 to cancel the insert, show the New Record form, leaving all fields unchanged
1 to continue inserting.
There is no eventPostInsertField property.
Example
//I want to cancel the insert if one
//specific column has one specific value...
$lens->eventPreInsertField = 'CheckField';
// return -1 = skip processing this field
// return 0 = cancel INSERT sql
// return 1 = process this field and continue INSERT sql
function CheckField($colname, $data, $type)
{
// $colname is uppercase
if ($colname == 'FIELDTOCANCEL' and $data == 'datatocancel')
return 0; // cancel
Name of function to call before New Record is createdDefault: This function will be passed 1 parameter, the $lens object (unlike eventPreDeleteSQL and eventPreUpdateSQL which get 2 parameters). This function is typically used to initiate a transaction. The transaction is completed in the eventPostInsertSQL function.
This property is obsolete. We recommend using spNew instead.
Syntax function preinsert($lens)
{
GLOBAL $HTTP_POST_VARS;
$lens->connection->BeginTrans();
}
function postinsert($key,$insertok, $lens)
{
if ($insertok) {
$rs = $lens->Execute('update some table with $key values');
if ($rs) $lens->connection->CommitTrans();
else $insertok = false;
}
Name of function to call before every field is processed for updatesDefault: This function should have 3 parameters. $colname is the field name, &$data is the data and $type is the ADODB data type (not the native type).
For $data, dates are passed as strings in 'Y-m-d h:i:s' format, eg. 4pm 23rd February 2002 is '2002-02-23 16:00:00'. Strings are passed without quoting. Do not quote strings yourself because phpLens does it for you.
For $type, legal values are X (long text), B (blob/image), D (date), T (timestamp), C (char), N (numeric), I (integer). See ADODB readme.htm documentation.
Returns a value of -1 to skip this field when updating, 0 to cancel all updates, and 1 to continue updating.
There is no eventPostUpdateField property.
Syntax // return -1 = skip processing this field
// return 0 = cancel UPDATE sql
// return 1 = process this field and continue UPDATE sql
function UpdateField($colname,&$data,$type)
{
print "<BR>$colname[$type] = $data";
return 1; // update everything
}
Name of function to call before record is changedDefault: This function will be passed 2 parameters, $key, the primary key of the relevant record, and the $lens object. This function is typically used to initiate a transaction. The transaction is completed in the eventPostUpdateSQL function.
This property is obsolete. We recommend using spEdit instead.
Syntax function preupdate($key, $lens)
{
GLOBAL $HTTP_POST_VARS;
$lens->connection->BeginTrans();
}
function postupdate($key, $updateok, $lens)
{
if ($updateok) {
$rs = $lens->Execute("update table set values=??? where parentid=$key");
if ($rs) $lens->connection->CommitTrans();
else $updateok= false;
}
List of fields to export as Excel file or CSV when they click on the export buttonDefault: List of fields to export as CSV or as an Excel file. All carriage returns are converted to spaces.
The files generated need to be stored in a temporary directory before they are downloaded. Set the temporary directory using the imageTmpDir property.
Excel files are generated using the LGPL class, SimpleXlsGen, which can be found in phplens-excel.inc.php. We conform to Microsoft Excel 5.0 File Format. They are exported by default with the .xls extension.
For CSV, all text fields are quoted with double-quotes, and double-quotes are escaped with double-quotes, following Microsoft Excel CSV conventions. CSV files generated are by default exported with the .csv extension.
The source code for the exporting can be found in phplens-template.inc.php. You can also modify the mime types used from this file. The actual file downloading to your web browser is performed by a helper script, phplens-img.php.
Also see the exportOptions property to change the defaults, or change the file name to use for exports.
Microsoft Excel is a copyrighted product of Microsoft Corporation.
Export options for exportLens propertyDefault: The default of exportLens is to export all records in CSV format.
To only export current page, set exportOptions = 'CURPAGE'.
To export Excel format instead of CSV, remove the 'CSV' setting from exportOptions.
To change export file name, use exportOptions="NAME^export-$DATE.xls". The $DATE in the file name will be replaced with the current date in Y-m-d format.
Note: In 2.4.10, default export mode was changed from Excel to CSV.
Syntax # use ; to combine choices, for example:
# export CSV, only current page, with name file.csv
Holds the data of the current record in VIEW/FILTERVIEWDefault: This array is only valid after Render() is called. As each row from a SQL query is processed, this property is updated.
For non-applet phpLens objects, store session info in a file instead of in the phpLens tableDefault: Instead of saving phpLens settings in a database table, save in a file stored in the directory refered to by fileSessionPath. This makes it easy to install new phpLens objects because you do not need to download and upload database records, but simply copy the phplens settings files into the fileSessionPath directory.
This property is smart enough to attempt reading from phplens table in database if session file not found, so is backward compatible with old phplens objects.
Warning: This might not work in safe mode. Save settings to a database table instead.
Search clauses are ORedDefault: The default (filterAllOr = false) will cause a filter/search on column1, column2 like this (assume that exact match has been configured):
SELECT * FROM table WHERE column1 = '$col1' AND column2 = '$col2';
When filterAllOr = true, this becomes
SELECT * FROM table WHERE column1 = '$col1' OR column2 = '$col2';
Determines whether the match anywhere in the filter/search form is visibleDefault: For scalability, we might not want a user to search for substrings in any field. To disable this feature, set
Set the default values for filtering/searchingDefault: If you have 2 search fields, the first being a free text field for product launch date with the default being all products to be launched in the future(m/d/Y), and the second is a set of checkboxes, indicating product status, and you want the checkboxes "Development" and "Launching" checked, then set:
Allows you to perform special searching on a columnDefault: Allows you override normal WHERE searching clauses, so you can use database specific syntax. Useful for full text search and case-sensitive matching. Formerly named filterLikeFunction.
First a quick overview of how it works. Let's say we have a WHERE clause for field id:
id = 'abc'
which we want to rewrite to:
id like 'abc%'
Now the filter modifier works by breaking the string to the component parts:
$colname = 'ID' # automatically converted to uppercase for you
$oper = ' = '
$prefix = "'"
$valArray = array('abc')
$suffix = "'"
We need to change it to:
$colname = 'ID' # automatically converted to uppercase
$oper = ' like '
$prefix = "'" # remains unchanged
$valArray = array('abc%')
$suffix = "'" # remains unchanged
or
$colname = 'ID' # automatically converted to uppercase
$oper = ' like '
$prefix = "'" # remains unchanged
$valArray = array('abc') # remains unchanged
$suffix = "%'"
Since phpLens 2.5.2, we have added two additional parameters to the function, $valueArray and $inputArray.
function LensFilterFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode, &$valArray, &$inputArr)
$colname is the name of the column
$oper is the operator to use (LIKE/=)
$prefix is the prefix to attach to the search string
$suffix is the suffix to attach to the search string
$quote = true to let phplens quote the search string, false if you do it yourself, default is true (phplens quotes for you)
$matchMode is 0 == exact match, 1 = match beginning, 2 = match anywhere.
$valueArray is an array holding the values keyed in by the user. It is an array because a series of checkboxes could have been keyed in.
$inputArray allows you to use data binding. Initially set to false, modify this to set an array value. See example 4 below.
$sqlFrom which holds the FROM tables/views. You can modify it to join with additional tables. See example 5 below.
So the above change to ID can be implemented with
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode, &valArray)
{
if ($colname == 'ID') {
$oper = ' LIKE ';
$valArray[0] .= '%';
}
}
Syntax Example 1: MySQL Full Text Search
/*********************************************************************************
example to support say mysql full text search for column 'message':
SELECT * FROM articles WHERE MATCH (message) AGAINST ('text');
*********************************************************************************/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
if (strtoupper($colname) == 'MESSAGE') {
$quote = false;
$colname = " MATCH ($colname) ";
$oper = " AGAINST ";
$prefix = "('";
$suffix = "')";
}
}
$lens->filterModifierFn= 'LensLikeFn';
Example 2: Switch to using PostgreSQL ILIKE operator
# ILIKE is case sensitive
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
if (strtoupper($oper) == ' LIKE ') $oper = ' ILIKE ';
}
$lens->filterModifierFn= 'LensLikeFn';
Example 3: Using Full Text Search in Microsoft SQL Server
/*********************************************************************************
To support full text search for column 'message':
SELECT * FROM articles WHERE CONTAINS(message,'text');
*********************************************************************************/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
if (strtoupper($colname) == 'MESSAGE') {
$quote = false;
$oper = " CONTAINS($colname,";
$colname = "";
$prefix = "'";
$suffix = "')";
}
}
$lens->filterModifierFn= 'LensLikeFn';
Example 4: Implementing data binding with oci8
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode,&$valArray,&$inputArr)
{
foreach ($valArray as $k => $v) {
if ($matchMode == 1) $v .= '%';
else if ($matchMode == 2) $v = '%'.$v.$'%';
$inputArr[$colname.$k] = $v;
$valArray[$k] = ':'.$colname.$k;
}
}
$lens->filterModifierFn= 'LensLikeFn';
Example 5: Joins
/*
This is only a partial example.
The main table is TABLE1, but we need to search for data in TABLE2.COL2.
This requires a join with TABLE2. So we modify $sqlFrom to add TABLE2,
and there join condition is added to $colName.
*/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode,&$valArray,&$inputArr, &$sqlFrom)
{
if ($colname == 'COL2') {
$sqlFrom .= ',TABLE2';
$joinCondition = ' TABLE1.KEY1 = TABLE2.KEY2 AND ';
$colName = $joinCondition . " COL2";
}
}
Sets which fields must be filled in the search/filter formDefault: This allows you to define certain fields to be compulsory in the search form. This is normally to restrict the data that is returned, and improve scalability.
All fields which must be filled are tagged with a red star. This can be configured in the mustFillMark property.
Certain types of fields, such as checkboxes and radio buttons cannot be set to must fill.
Additional settings that are used internally by filterLensDefault: To save these settings as source code, go to Search/Filter mode, change the field settings, generate PHP code and save the filterParams and filterLens properties.
Control checkbox style and formating in filter formDefault: All checkboxes in the filter form are formatted in an invisible table. This allows you to control the cell properties, including the font size with CSS.
The number of field columns to displayDefault: This allows you to display multiple fields in one row. Formerly 3 search fields would display by default as:
Field 1: [ ]
Field 2: [ ]
Field 3: [ ]
Search Cancel
Now by setting $lens->filterTableCols = 3; the fields will appear as
Field 1: [ ] Field 2: [ ] Field 3: [ ]
Search Cancel
The background color of the top of the search/filter formDefault: If no value is defined for this property, then it will use the $lens->colorTitle property used by the Edit/New record form.
Syntax $lens->filterTopColor = 'red class=topbar'; # red, or use CSS class topbar
Which screen to show on startup and when phpLens is resetDefault: Usually we set firstState to VIEW or FILTER on startup. We set it to VIEW if we want to view the grid on startup. We set it to FILTER if we want a search screen to appear on startup.
Additional Notes
If you want phpLens to create a new record on startup, set it to NEW.
If you want phpLens to edit a specific record on startup, use the URL:
$PHP_SELF?lens_$id=e&lens_p1=$primarykey
where $id is the id you passed to phpLens and $primarykey is the primary key of the record that matches $lens->keyCol.
EDITSAVE
New to phpLens 2.4.2 is the EDITSAVE state. Set firstState = 'EDITSAVE' when we want to display an EDIT screen in firstState, but after we successfully save, we want switch to VIEW.
The default date input formatDefault: Default for USA is "m/d/Y".
a - "am" or "pm"
A - "AM" or "PM"
d - day of the month, 2 digits with leading zeros; i.e. "01" to "31"
D - day of the week, textual, 3 letters; i.e. "Fri"
F - month, textual, long; i.e. "January" -- uses short always
g - hour, 12-hour format without leading zeros; i.e. "1" to "12"
G - hour, 24-hour format without leading zeros; i.e. "0" to "23"
h - hour, 12-hour format; i.e. "01" to "12"
H - hour, 24-hour format; i.e. "00" to "23"
i - minutes; i.e. "00" to "59"
j - day of the month without leading zeros; i.e. "1" to "31"
l (lowercase 'L') - day of the week, textual, long; i.e. "Friday"
m - month; i.e. "01" to "12"
M - month, textual, 3 letters; i.e. "Jan"
n - month without leading zeros; i.e. "1" to "12"
s - seconds; i.e. "00" to "59"
w - day of the week, numeric, i.e. "0" (Sunday) to "6" (Saturday)
Y - year, 4 digits; i.e. "1999"
y - year, 2 digits; i.e. "99"
Default format of dates for display in grid or detail tableDefault: Default for USA is "M-d-Y".
a - "am" or "pm"
A - "AM" or "PM"
d - day of the month, 2 digits with leading zeros; i.e. "01" to "31"
D - day of the week, textual, 3 letters; i.e. "Fri"
F - month, textual, long; i.e. "January" -- uses short always
g - hour, 12-hour format without leading zeros; i.e. "1" to "12"
G - hour, 24-hour format without leading zeros; i.e. "0" to "23"
h - hour, 12-hour format; i.e. "01" to "12"
H - hour, 24-hour format; i.e. "00" to "23"
i - minutes; i.e. "00" to "59"
j - day of the month without leading zeros; i.e. "1" to "31"
l (lowercase 'L') - day of the week, textual, long; i.e. "Friday"
m - month; i.e. "01" to "12"
M - month, textual, 3 letters; i.e. "Jan"
n - month without leading zeros; i.e. "1" to "12"
s - seconds; i.e. "00" to "59"
w - day of the week, numeric, i.e. "0" (Sunday) to "6" (Saturday)
Y - year, 4 digits; i.e. "1999"
y - year, 2 digits; i.e. "99"
Number of decimal places to display for numeric dataDefault: This property is for display purposes only. When editing a record, we display the full precision of the data and ignore this property.
Default format of timestamps for data entryDefault: Default for USA is "m/d/Y h:i:s A".
a - "am" or "pm"
A - "AM" or "PM"
d - day of the month, 2 digits with leading zeros; i.e. "01" to "31"
D - day of the week, textual, 3 letters; i.e. "Fri"
F - month, textual, long; i.e. "January" -- uses short always
g - hour, 12-hour format without leading zeros; i.e. "1" to "12"
G - hour, 24-hour format without leading zeros; i.e. "0" to "23"
h - hour, 12-hour format; i.e. "01" to "12"
H - hour, 24-hour format; i.e. "00" to "23"
i - minutes; i.e. "00" to "59"
j - day of the month without leading zeros; i.e. "1" to "31"
l (lowercase 'L') - day of the week, textual, long; i.e. "Friday"
m - month; i.e. "01" to "12"
M - month, textual, 3 letters; i.e. "Jan"
n - month without leading zeros; i.e. "1" to "12"
s - seconds; i.e. "00" to "59"
w - day of the week, numeric, i.e. "0" (Sunday) to "6" (Saturday)
Y - year, 4 digits; i.e. "1999"
y - year, 2 digits; i.e. "99"
Default format of timestamps for displayDefault: For USA, default is "M-d-Y h:i:s A".
a - "am" or "pm"
A - "AM" or "PM"
d - day of the month, 2 digits with leading zeros; i.e. "01" to "31"
D - day of the week, textual, 3 letters; i.e. "Fri"
F - month, textual, long; i.e. "January" -- uses short always
g - hour, 12-hour format without leading zeros; i.e. "1" to "12"
G - hour, 24-hour format without leading zeros; i.e. "0" to "23"
h - hour, 12-hour format; i.e. "01" to "12"
H - hour, 24-hour format; i.e. "00" to "23"
i - minutes; i.e. "00" to "59"
j - day of the month without leading zeros; i.e. "1" to "31"
l (lowercase 'L') - day of the week, textual, long; i.e. "Friday"
m - month; i.e. "01" to "12"
M - month, textual, 3 letters; i.e. "Jan"
n - month without leading zeros; i.e. "1" to "12"
s - seconds; i.e. "00" to "59"
w - day of the week, numeric, i.e. "0" (Sunday) to "6" (Saturday)
Y - year, 4 digits; i.e. "1999"
y - year, 2 digits; i.e. "99"
Generates an additional footer row below the subtotal band, and one additional footer above the grand totalDefault: The footerFn is defined as follows:
function MyFooterFn($group=false)
{
global $COL1, $COL2, $COL3;
If the $group is false, then it is the footer just above the grand total band. If $group contains a real group, then it is displaying a footer for the final band.
Normally to gather the values (eg. sums and averages) to generate a footer, you need to use multiple powerLens that captures the required values for $COL1, $COL2, $COL3.
For example below, the last line is a generated footer:
Branch
Staff
Sales
East
E1
4
2,000
E2
2
3,000
6
5,000
Admin Staff 3
Profit 2,400
Using the following function:
function FooterStaffSales($group=false)
{
global $DB;
$staff = $DB->GetOne("select staff from admintable where area=?",array($group));
$profit = $DB->GetOne("select profit from costing where area=?",array($group));
Change the type of a columnDefault: This is useful for changing datetime/timestamp columns to type date, or logical to integer. We use the ADODB type codes. If a number or integer is forced to date or timestamp, then we assume that the number is a Unix timestamp.
C: character and varchar types
X: text or long character (eg. more than 255 bytes wide).
B: blob or binary image
D: date
T: timestamp
L: logical (boolean)
I: integer
J: positive integer (>= 0)
N: numeric (float, double, money)
P: positive numeric (>= 0.0)
Note that we do not modify the field types in your database. This property is for display and data entry type checking purposes only.
Syntax # change col1 to type 'D' for date
$lens->forceType = 'col1^D';
If sql select statement returns no fields, then we get field info from these tablesDefault: Some databases do not return any field information if no records are returned (because the tables are empty). As a workaround, we have this property.
When sql select statement returns no fields, then we get field info from the fields of these tables (comma-separated).
Note: Make sure all tables listed have unique field names. Having order.id and product.id will confuse phpLens.
Syntax $lens->sql = 'select * from order, product where order.prod_id = product.id';
$lens->getFieldsFromTables = 'order, product';
Used to fix the main grid's height to a certain amountDefault: Default is that the grid height is determined by the Web browser. You can set this to a percentage (eg. 100%) or number of pixels (eg. 200), but it is not guaranteed to work because the browser can override your settings.
Syntax $lens->gridHeight='100%' // means 100% of the enclosing page/table's height
$lens->gridHeight=600; // means that the grid is 600 pixels tall
Sets fields cannot be modified when editMultiple is trueDefault: This lens sets which fields cannot be modified when editMultiple is true in the grid. Note that the gridReadOnlyLens property is additive with readOnlyLens, in that readOnlyLens also affects which fields cannot be modified when editMultiple is true also.
Allow end-users to configure what columns to display in the grid.Default: When dynamic editing is disabled ($lens->dynEdit = false), the end-user is allowed to click on the green "e" icon on the grid and select grid fields to hide/display.
All fields can be manipulated by the end-user, except for field names that begin with underscore ('_') which are hidden.
Each end-user can set his or her grid fields independantly. This is determined by the gridUserLens setting:
$lens->gridUserLens = 'USERID';
$lens->dynEdit = false; # for security
The above setting means use the $GLOBALS['USERID'] variable as to identify which grid settings phplens should load. If this global variable is empty, then we assume that no end-user has logged in and do not allow any modifications.
In this example, we will store the grid selections in the phplens table, with the "id" field being set to 's_'.$GLOBALS['USERID'].
Phplens does not delete this record, so if you want to clear this field, you can do so yourself. The last modification time of each record is stored in the "lastmod" field.
Syntax # use $USERID global variable
$lens->gridUserLens = 'USERID';
Group values that are similar in a vertical span in the gridDefault: Group similar values in a field in a vertical column span in the grid. This is a numeric value.
For example, setting groupByCol = 3 will group similar values for the first 3 columns in a grid. This will group all similar fields by rowspan for the first 3 columns in the grid. So in a grid containing 4 fields instead of seeing:
a b c 1
a b d 2
a c e 3
a c e 4
You will see
a b c 1
d 2
c e 3
4
Note that the raw value of the cell is used for grouping, before any powerLens is applied.
Field to display when groupLens is setDefault: The groupLens is used to sort and group data. Sometimes the groupLens field is a numeric or private field, and we want to display another field as the name of the group. Use this property to define the group name to display.
Group data but hide groupLens bandDefault: When the groupLens is defined, data is grouped by a specific field. Also a group band is displayed if the grouping field is not visible in the grid. This boolean variable hides this group band.
This is particularly useful when you are grouping by a hidden field that you do not want to display, but want to show the subtotal bands. Normally you will see in the grid:
Group Band 1
Data Data Data
Data Data Data
total total total
Group Band 2
Data Data Data
Data Data Data
total total total
When groupHideBand = true, then you will only see:
Data Data Data
Data Data Data
total total total
Data Data Data
Data Data Data
total total total
Syntax # to hide group band
$lens->groupHideBand = true;
Group rows by this data columnDefault: This Lens will order your data by a the specified column, and show a divider in the grid marking the beginning of all records in that group. You can also subTotal groups using the sumLens property.
For example you have a MEMBERS table which is a list of people who are members of a club. In the table there is a field called STATE indicating what US State the user resides in.
Setting $lens->groupLens='STATE'; will produce the following if the grouping field is visible:
Alaska
Abraham
Nielson
Joseph Ahmed
Boesky
Bill
Baggins
Arkansas
Mee
Siam
Maido
Bola
Billy
Clinton
and if the grouping field is hidden:
Alaska
Abraham
Nielson
Joseph Ahmed
Boesky
Bill
Baggins
Arkansas
Mee
Siam
Maido
Bola
Billy
Clinton
To disable the error message which appears if the groupLens column is missing, set the language file class variable $txtErrGroupLens to an empty string ("").
Group Display
New in phpLens 5, you can use groupLens to define the sort order of the group, and select a different field to display as the group name. See groupDisplay.
When true, hides the anchor name that is generated by phpLens so that we scroll down the browser to where the applet is locatedDefault: For example, if you have an applet called abc, then at the end of every url, we generate the following url hash:
#LensBM_abc
Then we place at the top of the applet the following anchor tag if hideAnchorName is false.
<a name="LensBM_abc">
Then every click on a link will scroll the browser screen to just above the applet.
Setting this property to true will disable the generation of this anchor tag, though the url hash will still be generated. This is useful if you want to generate the anchor name yourself in another position instead of the top of the screen, or if you want to disable this behaviour.
Creates a wysiwyg HTML editor for fields when editing recordsDefault: The WYSIWYG editor can be customized by modifying phplens/img/htmlarea/editor.js.
Thanks also to the people at interactivetools.com for releasing this software as a free component.
Determines which character fields to treat as native HTML/binary (no encoding)Default: Normally phpLens will convert all < characters to <. This disables HTML tags. Setting this will stop this conversion.
ID to identify this phpLens objectDefault: You might have multiple phpLens objects configured on different pages. Each copy of phpLens must be assigned a unique ID. This ID must be no longer than 38 characters, and must consist only of underscores, letters or numbers (_ A-Z and 0-9). If you are using phpLens 2.0.9 or later, it is 10 characters in length.
This is also part of the primary key into the phpLens table, and used in naming GET and POST variables.
Do not register any session variablesDefault: For advanced users. If this is enabled then saving, sorting in descending order, filtering and editing will not work.
Attributes in the <img> tag such as WIDTH or HEIGHTDefault: Since phpLens 2.1.1, if you have the exif extension installed and leave $lens->imageAttributes empty, phpLens will auto-calculate the img tag's height and width for you, using GetImageSize( ).
If you do not have exif installed and this property is not defined, we let the browser size the images.
Since 2.7, we autodetect the ^ symbol, and if we detect ^, we assume that the settings are on a column by column basis.
Syntax #If you want all images to be 100 pixels wide and 120 pixels high:
$lens->imageAttributes='WIDTH=100 HEIGHT=100';
#If you want to manage different sizes for different images:
Cache blobs stored in database in the imageTmpDir directory for imageCacheSecsDefault: Set to a positive number of seconds to cache images. The images are cached in the LENS_IMG_TEMP_DIR directory (PHP constant), or if that is not defined, in the $lens->imageTmpDir directory (phpLens property).
Requires the primary key to be defined with the keyCol property, because we generate temporary file names using the following algorithm:
Note that you will have to delete expired cache files yourself using cron or at.exe. PhpLens does not delete expired files. Also all files are saved with the gif extension, even if they are jpeg or png. All common web browsers ignore the file extension and auto-detect the file type correctly.
Caching
The following properties are used together to enable image caching:
imageCacheSecs: Number of seconds to cache image
imageTmpDirURL: The URL to use from the web browser to read the directory
and you have to modify the following constant in the file phplens/config/phplens.img.config.inc.php
LENS_IMAGETMPDIR: the path to where the images are cached
So suppose the web server root directory is /htdocs, and the images are in /htdocs/img, we set
$lens->imageTmpDirURL = '/img';
$lens->imageCacheSecs = 3600 * 24 * 30; // 30 days
And in phplens.img.config.inc.php
define('LENS_IMAGETMPDIR','/htdocs/img');
Whenever we upload a new image to the server, the cached image file is also deleted automatically.
Note: when cached images are generated, a .gif suffix is added to the filename even if the file uses some other format. This is to ensure that the images are cached by proxy servers.
Compatibility Note
For phpLens 2.8 and earlier, we do not use the phplens.img.config.inc.php file to store the location of the image directory. Instead you have to set the property
imageTmpDir: The location of the image temporary directory on the server
Then you have to modify the source code of phplens-img.php file, changing the hard-coded '/tmp' string with the correct path.
PhpLens icons and javascript are stored hereDefault: Normally, there is no need to modify, unless you have moved the phplens code outside the Web server directories.
Alternatively, define the global variable $PHPLENS_GRAPHICS_SERVER to the path to your images.
Directory to store temporary images and export excel/csv filesDefault: Important. This property is obsolete in phpLens 3.0.
Caching
The following properties are used together to enable image caching:
imageCacheSecs: Number of seconds to cache image
imageTmpDirURL: The URL to use from the web browser to read the directory
and you have to modify the following constant in the file phplens/config/phplens.img.config.inc.php
LENS_IMAGETMPDIR: the path to where the images are cached
So suppose the web server root directory is /htdocs, and the images are in /htdocs/img, we set
$lens->imageTmpDirURL = '/img';
$lens->imageCacheSecs = 3600 * 24 * 30; // 30 days
And in phplens.img.config.inc.php
define('LENS_IMAGETMPDIR','/htdocs/img');
Whenever we upload a new image to the server, the cached image file is also deleted automatically.
Note: when cached images are generated, a .gif suffix is added to the filename even if the file uses some other format. This is to ensure that the images are cached by proxy servers.
Compatibility Note
For phpLens 2.8 and earlier, we do not use the phplens.img.config.inc.php file to store the location of the image directory. Instead you have to set the property
imageTmpDir: The location of the image temporary directory on the server
Then you have to modify the source code of phplens-img.php file, changing the hard-coded '/tmp' string with the correct path.
File Naming for Temporary Images
The file name algorithm since 2.4 is:
where $blob is the actual image data, and $key is the primary key value, or false if not defined. Even if the file is not a gif, we append a gif to the end to ensure that proxy servers cache the file correctly. Normally browsers are able to auto-detect the image type correctly.
Maps the image cache directory to a URL so that we can generate img tags efficientlyDefault: The following properties are used together to enable image caching:
imageCacheSecs: Number of seconds to cache image
imageTmpDirURL: The URL to use from the web browser to read the directory
and you have to modify the following constant in the file phplens/config/phplens.img.config.inc.php
LENS_IMAGETMPDIR: the path to where the images are cached
So suppose the web server root directory is /htdocs, and the images are in /htdocs/img, we set
$lens->imageTmpDirURL = '/img';
$lens->imageCacheSecs = 3600 * 24 * 30; // 30 days
And in phplens.img.config.inc.php
define('LENS_IMAGETMPDIR','/htdocs/img');
Whenever we upload a new image to the server, the cached image file is also deleted automatically.
Note: when cached images are generated, a .gif suffix is added to the filename even if the file uses some other format. This is to ensure that the images are cached by proxy servers.
Compatibility Note
For phpLens 2.8 and earlier, we do not use the phplens.img.config.inc.php file to store the location of the image directory. Instead you have to set the property
imageTmpDir: The location of the image temporary directory on the server
Then you have to modify the source code of phplens-img.php file, changing the hard-coded '/tmp' string with the correct path.
Name of function to call when uploading data in PHP safe modeDefault: In PHP safe mode, you cannot access files outside the Web server root directory. In this case phplens cannot read uploaded files with some help from you.
You need to define a function which accepts one parameter, the PHP4 upload array and returns as a binary string the contents of the file to be uploaded. Set the name of this function to the imageUploadFunction property.
This array is equivalent to $HTTP_POST_FILES['tmpfile'].
function uploadimg($arr)
{
$fp = fopen($arr['tmp_name'],'r');
if (!$fp) return false;
$s = fread($fp,9999999);
fclose ($fp);
return $s;
}
$arr should contain:
$arr['name']
The original name of the file on the client machine.
$arr[type']
The mime type of the file, if the browser provided this information. An example would be "image/gif".
$arr['size']
The size, in bytes, of the uploaded file.
You can define a global inputLens using * that is applied to all input tags:
$lens->inputLens = '*^class=input-type';
You can also define an inputLens for a specific field, and then use the global inputLens for all other tags. The fruittype field will not use the global inputLens, but use class=fruit-css instead.
When editing, display checkbox, radio or submit buttons for selected fieldsDefault: You can display a field as a set of lists items (popups and multiple select), check boxes, radio buttons or submit buttons using this property. The mustFill property is ignored if this property is set.
First are numeric/boolean checkboxes where you can toggle the checkbox on or off.
Second are multiple selection checkboxes. This works in conjunction with lookupLens which provide the lookup options, and the displaySep property which controls how each selection is used. See an example below.
Checkbox options are saved as comma separated items in the field. To use some other separator, modify the displaySep field.
radio
Radio buttons allow you to select one item only. If you require this to be must fill field, use a popup instead.
submit
Submit buttons are useful for approval/workflow forms and surveys. Say you have an approval screen with 3 buttons, Approve, Decline and Send Back. We then have the following code:
Clicking on any of the buttons will save the "Approve", "Decline" or "Send Back" text into the database field status linked to these set of buttons.
combo
This is a combination of an input text field and a popup where values can be selected. Useful when you have a set of standard values (defined in the popup), but you want to be able to enter strings that do not match the standard values also.
readonly
The cell value is stored in an input text field that is read-only. This is useful when you have a searchLookupLens that changes multiple fields, but you do not want the user to modify the fields directly. This differs from the readOnlyLens where the value is stored as text, with no input field and no data is posted on form submission. Available since 3.1.
Number of Columns for Checkboxes and Radio-buttons
Since phpLens 2.7.0, you can determine the number of columns to display for check boxes and radio buttons. In this example, the 3 means use 3 columns.
$lens->inputTypeLens = 'Col1^checkbox^3';
Syntax # To select all the cities i like by clicking on multiple checkboxes
# and then save them into the Cities_I_Like column in the format:
# "Bukina Faso, London, Verona"
# to select only one city from 'NY,Moscow,Beijing':
$lens->lookupLens = 'Cities_I_Like^==NY/Moscow/Beijing';
$lens->inputTypeLens = 'Cities_I_Like^radio';
# to select only one US state, and you want the user to save time
# by not having to click on the default Save button (which is hidden).
# We show the state names, but save as state codes ( a mapping):
$lens->lookupLens = 'State^=AR/Arkansas/CA/California/NY/New York';
$lens->inputTypeLens = 'State^submit';
Clears the keyVariable if it not visible on pageDefault: The keyVariable is used to store the primary key of the currently selected record. If you dynamically change the sql, you normally want to clear the keyVariable when the selected record is not visible. This is the default behaviour.
If you want the keyVariable to be sticky, so that the record remains selected even if it is not visible, set this to false.
Mirror the primary key column value in a global variableDefault: This has been rendered obsolete by the keyVariable property.
This is useful when you want need to access the primary key of the currently selected record. The variable defined in keyColVariable is automatically set to the primary key value as soon as it becomes available.
Note that if a new record is inserted into a table, we are unable to set the keyColVariable correctly.
Syntax $lens->keyColVariable = 'ID';
$lens->Render();
print $ID; # now the global var $ID is set to the primary key value
Compound primary key fieldsDefault: Define compound primary key here as a ; delimited list of fields. If this is defined, then the keyCol property is ignored (phpLens will set keyCol to '*').
Note: the key (lens_p1) generated in phpLens url's for compound keys uses a special formula to combine all the fields into one value. See source code of LensPackKeys($keyarray) in phplens-common.inc.php.
Force primary key to be treated as a char if true, otherwise autodetectDefault: Sometimes the type autodetection of the key column fails, and a numeric key is actually a string. To force the key to be quoted, set this to true.
Selected record is based on primary key and not relative position in gridDefault: 0 = Show record based on relative record number in details. If no record found, show first row.
1 = Show record based on selected primary key in details, if no record found then hide details.
2 = Show only record based on selected primary key in details, if no record found then show first row in details.
Previously we highlight records based on record number. This caused problems when we want to show the same record in the details all the time because as records get added/deleted, the record number would point to a different record.
When keyRecNoSelect is set to 1, the same record is always selected as record selection is by primary key and not relative position in the recordset.
When set to 0 (the default), the record selected is based on the relative position from the beginning of the recordset. So if records are added to the beginning of the recordset (or deleted), a different record could be selected. T
On the first time a phpLens object is displayed, no record will be selected if keyRecNoSelect = 1, while if set to keyRecNoSelect=2, the first record will be auto-selected.
GET parameters
The required URL variables in your GET parameters becomes:
lens_no_mysql=0&p=$key
where the $recno is the record number you want to go to (it is ignored), and $key is the urlencoded primary key to select and display in the details.
Syntax $lens->keyRecNoSelect = 1; // select by primary key
The name of the table you are editing and creating records onDefault: Normally, phpLens can guess the name of the table for you unless you are using stored procedures.
You can define a dynamic key table by prefixing this property with '=' or '%'. See example below. Available in phplens 1.2.
Syntax $lens->keyTable = 'atable';
# the following allows you to have multiple tables
# holding postcodes for different countries, eg.
# MALAYSIA_POSTCODES, USA_POSTCODES, etc.
# and reuse the same phpLens object for different tables.
Sets a PHP global variable to the primary key of the currently selected recordDefault: Typically you will store the current record's primary key in a session variable in this way:
global $DB;
if (empty($_SESSION['ID])) $_SESSION['ID'] = false;
$ID =& $_SESSION['ID'];
# and in your applet code, set:
$lens->keyVariable = 'ID';
Managing ID's for New Records
Tracking keyVariable is a problem when we create a new records. We need a mechanism to update $ID after the record is created. In the above example, $ID is updated with the created record's serial id, if the database supports serial id's (mssql, mysql).
If the database does not support serial id's, then we provide a second parameter to keyVariable where you can define some sql to execute to obtain the primary key of the newly inserted record. In this example, we store the serial id in an oracle sequence:
$lens->keyVariable = "ID/select sequence1 from dual";
In the above example, you will still need to define an oracle trigger which sets 'table_id' to sequence1.nextval. Also note the seperator used is the option seperator (/) and not the field seperator (^).
Note that if $ID is already defined, then instead of showing the New Record form, we will be viewing or editing the record specified by $ID.
Limitations: Only works with numeric/integer primary keys currently.
Row Selection and Highlighting When this property is set, row highlighting and selection using javascript is enabled (since 3.5)
Holds the current language definitionsDefault: This is an instance of a php class. It is created by instantiating the class defined in phplens-lang-*.inc.php, where * represents the language, which can be:
Disable redirect to firstState when record is lockedDefault: When a table record is locked by a first user, the 2nd user will not be able to view the edit record form (unless they are using childLens or firstState == EDIT), but will be redirected back to the VIEW grid by default.
Setting this property to true disables the redirection, so you can view the edit record form even though the record is locked by someone else. The save and delete buttons/links are disabled.
Set this property to the global variable holding the USER ID for lockingDefault: To enable locking, set this property to the global variable to the user id of the person who is currently logged in. Then phpLens will keep track of the current user modifying the record, and prevent another user from editing the record. The variable data should be no more than 56 bytes long.
Supposed the userid is stored in $USERID, then set
$lens->lockRec = 'USERID';
PhpLens will transparently create the following table for storing lock records:
For MySQL and PostgreSQL:
CREATE TABLE phplens_lock (
LOCK_ID varchar(64) NOT NULL default '',
LOCK_TAB varchar(64) NOT NULL default '',
LOCK_BY varchar(96) default NULL,
LOCK_TIME datetime default NULL,
LOCK_SINCE datetime default NULL,
PRIMARY KEY (LOCK_ID,LOCK_TAB)
)
For Oracle:
CREATE TABLE PHPLENS_LOCK
(
LOCK_ID VARCHAR(64) NOT NULL,
LOCK_TAB VARCHAR(64) NOT NULL,
LOCK_BY VARCHAR(96),
LOCK_TIME DATE,
LOCK_SINCE DATE,
PRIMARY KEY (LOCK_ID,LOCK_TAB)
)
For maintenance, you should run a background job to scan the PHPLENS_LOCK table and delete all expired records, eg.
$sql = 'DELETE FROM PHPLENS_LOCK WHERE lock_time < '.
$DB->DBDate(time()-24*3600);
In-Record Locking
The above locking method does not require table structure modification. However if you want a higher performance locking solution, you should modify your table, adding the following fields:
LOCK_BY VARCHAR(96) LOCK_TIME DATETIME # or equivalent for your db
Then set in phpLens:
$lens->lockRec = 'USERID;LOCK_BY;LOCK_TIME';
The 2nd parameters is the table field that holds the userid of the person who is locking the record, and the 3rd parameter is the table field that holds the date and time that record was initially locked.
Timers used for locking recordsDefault: This is an array (not a string) with 2 elements (x,y). The 1st is the polling timer. Every x seconds, the browser client will poll the server based on this setting.
The 2nd element is the timeout. If the timer fails to contact the server after y seconds, then the server presumes that the browser client is disconnected.
So if x=30 and y=65, then this assumes that the browser client will poll the server twice (at 30 secs and 60 secs), and only if the server does not receive two polling messages do we assume that the browser has disconnected.
A lens to lookup descriptions of codes and create popups for <select> optionsDefault: There are 2 types of lookup values. The first is looking up the description of a code. For example mapping the country code ca to Canada, and uk for United Kingdom. The second is when you are editing a record and you want the user to be able to lookup all legal values for that field in a popup menu.
1. Lookup a Code
Sometimes you are using an abbreviation or a code in a column. You want to translate the abbreviation to something more natural. Use the Lookup Value feature to do so.
You can lookup a value by using a SQL select statement where the first column selected matches the code/abbreviation, and the second column matches the translated value to display. See example below.
SELECT col_id,col_to_display FROM table
To lookup the country names from a list of country codes, you could use:
SELECT Code, CountryName FROM CountryTable
Alternately you can text mappings. Make the first character equals (=), and follow it with your mappings. In the example below, we want to set "1" to lookup "one", "2" to lookup "two", etc.
=1/one/2/two/3/three/4/four
2. Lookup Popup Values
When editing or creating a record, you can give the user a list of legal values to select from by entering SQL of the form
SELECT DISTINCT colname FROM table
If you do not want to use SQL and want to perform text lookups, make use double equals(==) and follow it with your mappings. In the example below, we want legal values to be Yes or No:
==Yes/No
The double equals is available in phpLens 1.1 onwards.
Using variables in your SQL
In phpLens 1.2 onwards, you can access PHP global variables in your SQL statement provided you prefix the statement with a '%'. See examples in the syntax section.
Hot Update of Lookups
You might have a popup that is tied to the value of another field. For example, if you have a popup field taken from a SQL column called "states" that displays the US states, and a second popup field with data taken from SQL column "cities" that displays the cities in that state. When you change the state, the cities popup will change also. You can perform a hot update of the cities popup like this:
$lens->lookupLens="states^select name from states;";
$lens->lookupLens .= "cities^#select name from cities where state={states}";
$lens->phplensDatabase = 'oracledb'; // database defined in phplens.config.inc.php
Place a # as the first character of the select statement of the field that requires a hot update, and incorporate the controlling field (states here) as part of the sql statement as a {variable}.
This hot update uses javascript to make a request to phplens/phplens_remoteq.php. You might need to configure the above file so that the correct database connection settings are set. The remoteQuery and phplensDatabase properties might need to be configured also.
The phplensDatabase property is the name of the $PHPLENS_DATABASE entry (from config/phplens.config.inc.php) being used to connect to the database. Alternatively, you can modify the phplens_remoteq.php script yourself.
The remoteQuery property is the full http path to the phplens_remoteq.php file - you only need to set this property if you move this file out of the phplens directory.
The SQL used in the hot update is stored in the html generated by phpLens. As an extra security step, the SQL string is fingerprinted using MD5 with a salt. The salt can be configured using the constant PHPLENS_MD5_SALT in phplens.inc.php. The file phplens_remoteq.php detects if the SQL has been tampered with using the MD5 fingerprint and refuses to execute the SQL in this case.
This works in both Edit and Filter mode. Since phpLens 1.3.15.
Evaluating PHP Code in Normal Lookup
New to phpLens 1.9 is the ability to execute PHP code.
=% means evaluate PHP code, the PHP code must return values in a string of the form "1/one/2/two". Eg. $lens->lookupLens="col1^=%ReturnString()";
==% means evaluate PHP code, the PHP code must return values in a string of the form "1/2/3/4".
Evaluating PHP Code in Hot Update
Since 3.3, phpLens allows you to execute a function in a Hot Update. All source code must be placed in the APPLETS_DIR.'/remoteq' directory, typically found in phplens/builder/applets/remoteq. Remoteq stands for remote query.
For example, you have a field emp_name that hot updates based on changes in the company_id field. The hot update calls a PHP function called 'test', stored in the file 'remoteq/test.inc.php'. Then you define in the lookup:
The first character, #, indicates that it is a hot update. The parameter MetaPHP indicates that is is PHP code, and not SQL. The following parameter 'test.inc.php' is the name of the file located in the remoteq directory. Lastly, the function (defined in test.inc.php) and the parameters follow. The {company_id} means that company_id is passed as the function's first parameter, and is called every time the company_id changes.
The function must return an array, or false on failure. If string keys are defined, then they are the option values, while the values become the option text. One global variable is available to the function, $DB, which is the default database connection.
For example:
function test($co_id)
{
global $DB;
$co_id = (integer) $co_id; // sanitize param for security
$rs = $DB->Execute("select emp_no,emp_name from employees where company=$co_id");
Note: We use a very simple parser. Each parameter must be separated by a single space.
Conditional Lookups
There is a 3rd lookupLens parameter which allows you to define in which states (EDIT, VIEW, NEW, FILTER, FILTERVIEW) the lookupLens is evaluated.
The following means use 1st SQL lookup when editing, otherwise for all other states, use the second SQL stmt.
$lens->lookupLens='col1^select f from table^EDIT^select f2 from table2';
To simplify matters, two additional states were added: "+VIEW" means display this lookup in VIEW, FILTER and FILTERVIEW states. Conversely, we have "-VIEW" which means display this lookup in EDIT or NEW states.
$lens->lookupLens='col1^select fld from table^+VIEW^select fld2 from table2';
This means that the 1st select statement is used when VIEWing the data (VIEW/FILTER/FILTERVIEW states), otherwise the 2nd select statement is used.
$lens->lookupLens='col1^select f from table^VIEW^select f2 from table2^FILTER^select f3 from table3^FILTERVIEW^select f4 from table4';
In the above example, VIEW, FILTER and FILTERVIEW states have their own select statements. All other lookups use "select fld4 from table4".
Note that "-VIEW" and "!VIEW" mean the same.
Implementing a Single Checkbox
For boolean fields which require a single checkbox, try this (for the field checkcol, with a label 'Yes'):
Enables md5 fingerprinting of data entry fieldsDefault: This is a security feature that ensures that unauthorized users do not add addtional input fields to your form. MD5 fingerprinting is disabled when templates are used, or when this property is set to false.
Menu items in navigation bar to hideDefault: Legal values are 'DYNEDIT' 'SCROLL' 'NEW' 'FILTER' 'MENUS' 'ALL' 'TOP' 'BOTTOM' or a combination of the above.
DYNEDIT = hide dynamic editing "e" icon
SCROLL = hide all arrow icons
END = hide scroll to end icon.
NEW = hide new icon
FILTER = hide filter/search icon
MENUS = hide menus, but show caption
ALL = hide both top and bottom menus and captions
TOP = hide top menu and caption
BOTTOM = hide bottom menu and caption
Indicates fields that must be filled when editing/creating recordsDefault: In phpLens 1.1 and later, if the field is readonly but mustfill, then when the record is created, you can still enter a value in that field, but not after the record is created.
This 1.1 feature allows you to create records where the primary key is entered, but never allowed to change after creation.
Mark appended to the text label indicating that the field must be filled (for new records)Default: Only appears when creating new records. To disable, set
Sets the field titles for the grid, detail, edit, new record and search/filter screensDefault: This allows you to place additional instructions specific to a field when editing it, a different one when creating a new record, or to shorten or rephrase a column title.
To set a blank title, use {nbsp} to set a non-breaking space.
If a title is not defined, then the default $title is used.
Note: $filterTitle can only be set since phpLens 2.4.7, and $gridTitle since phpLens 2.5.5.
Executing PHP Code
Prefix title with = sign, and the title will be evaluated as PHP code. In the example below, $CURRENCY is a global variable.
$lens->nameLens = 'col^="Salary ".$CURRENCY';
Incorporating Field Values in titles for EDIT or NEW forms
Since 4.2.6, you can incorporate field values in titles for the EDIT or NEW record forms. For example, for the edit form, you have a field called NAME, and you want to incorporate a field called LEGALINFO in the title of field NAME.
Changing this property prevents the same applet on different pages from sharing session variablesDefault: This allows you to run the same applet in different pages with different settings (session variables). The value of the nameSpace is any valid string.
This lens sets which fields are shown when creating a new recordDefault: By default, the displayed fields are editable unless the newReadOnlyLens or readonlyLens properties are used. Use defaultLens for fields that have hard-coded default values.
Allow new records to be created in gridDefault: We preallocate a number of new record forms as hidden forms when we generate a web page. Clicking on the new icon will cause one of the preallocated forms to be revealed. The new form appears in the grid, and you can create
multiple new records by clicking on the New icon, without having to click on Save.
Set this property to the number of new records that are preallocated. Make sure that a primary key is defined for the applet (keyCol property) and editMultiple is set:
This lens sets which fields cannot be modified on creation, but are editable.Default: Use this for fields which have a hard-coded default value, but are editable subsequently. See also readOnlyLens. Note that readonly means that the user cannot change the value, but you can still set the value to insert into the database using the defaultLens property.
Convert newlines to <BR> tags in a field after on saving a recordDefault: If nl2br is true and htmlLens is set to true for a field, when you save then <BR> tags are added automatically for the field.
If nl2br is set to true, then all text areas will have their <BR> tags stripped automatically, whether or not htmlLens is set for that field.
One limitation is that <BR>'s can be generated in places where it is illegal, such as within <SCRIPT> and <TABLE> tags. Since these are normally banned tags, this is not an issue.
Do not trim column dataDefault: Normally phpLens will trim all field data and this is harmless. However if you are combining multiple data columns together to form one big text string (maybe there are limits to a varchar field for your database), you might not want any trimming to occur.
Determines what to display when no records foundDefault: 0 = old phplens behaviour, show new form (if key defined)
1 = Hide applet
2 = Show menubar, hide titles
3 = Show menubar, show titles
4 = Halt script (die) -- added in phpLens 5.0
Make certain data entry fields optional (readonly) based on data entered in an enabler fieldDefault: As an example, let's say that you have a field called "Type". When the value of type is "numeric", then we enable data entry for the field "decimal_places". When the value of "Type" is "char", then we enable the field "size" and disable "decimal places".
Currently, the enable field must be of type text/textarea or select (dropdown menu).
If you want to enable a field whenever a non-empty value is entered, use '*':
$lens->optionalCols = "size^type^*";
Lastly, optionalCols is synchronized with mustfill columns. If a mustfill column is disabled, then the mustfill property is also disabled (temporarily).
Set this to false to disable generation of overlib div tag and javascript includeDefault: Overlib requires some initialization. If this variable is not set to false, then phpLens will generate this:
Uses the overlib library to create a javascript popup in the grid for specific columnsDefault: To configure a javascript popup for the SHORT_NAME field that displays the DETAILS and MORE_DETAILS fields, you should do this:
Name of function used to modify globally phpLens settingsDefault: Sometimes you want to implement a function that modifies phpLens settings before the $lens object is rendered. This is typically for global settings on security, table colors, etc. But Generate PHP Code will always overwrite this global security function.
This property makes the global function part of the Generate PHP Code process, simplifying your code.
The function is called after the phpLens dynamic editor settings are retrieved, so it will override any settings configured using the dynamic editor.
Syntax $lens->overrideFunction = 'SetSecurity';
An Example
// remember $lens is by reference!
function SetSecurity(&$lens)
{
$global $isAdmin;
if ($isAdmin) {
$lens->dynEdit = 1;
$lens->menuHide = '';
} else {
$lens->dynEdit = 0;
$lens->menuHide = 'ALL';
}
}
$lens = new phpLens($db,'select * from table');
$lens->overrideFunction = 'SetSecurity';
// Now the next line is autogenerated by Generate PHP Code SetSecurity($lens);
$lens->Render();
$lens->Close();
Number of rows before issuing a CSS page break for printingDefault: The default of 0 means do not issue a page break.
The CSS page break when printing works on IE 5 or later. Testing with Mozilla 1.4 indicates there is a bug in Mozilla 1.4 that prevents it from supporting this page break.
The page break code also supports widow control when groupLens is enabled. This means that if a new group is to be started at the end of the page, we will move the group to the next page.
To display page numbers, use the following client-side javascript:
// at the beginning of the page
<script> var pagenum = 0;</script>
// And in one of the grid field titles, set:
<script>document.write("Page " + (++pagenum) )</script>
Password protect dynamic editingDefault: Before any dynamic editing can occur, the user will be prompted to enter this password if it is set. The default is not to prompt the user for a password.
For internal connections, use persistent connections if trueDefault: When phpLens connects internally to the database where the phpLens table resides, it will use this setting to determine whether to connect using persistent or non-persistent connections.
Also when you generate PHP source code or use the applets technology, this setting determines whether the code uses persistent connections or not.
Name of the index to $PHPLENS_DATABASESDefault: Allows you to define which database in $PHPLENS_DATABASES you are using. This is useful for hot updates of lookupLens. $PHPLENS_DATABASES is defined in phplens/config/phplens.inc.php.
For example, if the first element in $PHPLENS_DATABASE is selected:
Allows you to modify default input values when editing recordsDefault: This is similar to powerLens, but for input fields when editing or creating new records. See also defaultLens to modify default input values when creating records.
Read Only Fields
When you use powerEditLens with readonly fields, the fields would be updated when the user clicks on the save button. For example this field RO:
Dynamicly change contents of cellDefault: You can reformat a field's data before displaying it in a HTML grid cell using powerLens. For example, if you have 2 columns, say firstname and surname you can combine them using:
{firstname} {surname}
If you want to have the surname first, with the firstname in italics:
{surname}, {firstname}
If you want the surname in uppercase you can do so by executing PHP code.
To execute PHP code, set the first character to an equals (=). This will treat all following text as PHP code.
=strtoupper({surname}) . ", " .{firstname} . ""
You can access also global variables using the $GLOBALS[] array, or call PHP functions.
Empty Fields and NBSP
Sometimes you want to check if a field is empty/null. You cannot check by using if empty({column}) because all empty columns are set to & nbsp; for display purposes. Nor can you compare if ({column} == '& nbsp;') because semi-colon ; is used to separate column settings.
NOTE: Since phpLens 2.6.5, we allow you to disable conversion of empty strings to & nbsp;. You do this by prefixing a % or =% to your powerLens. Here are two examples:
Before 2.6.5, you had to use this technique: We also have defined a variable {NBSP} to represent & nbsp; . So you can check if a field is empty using if ({column} == {NBSP}).
Similarly for formatting, when you want to add additional spaces between words: Name: {NBSP} {Name}
Lastly if you need to display an ISO entity, for example the bullet symbol (•) • you can use the {SEMICOLON} variable:
"•{SEMICOLON}";
If the field you are accessing is numeric, we automatically format the number with the thousand's separator. So if you have a column called {kg} and you want the raw unformated data, the raw data is available in the $_flds array. Note that the _flds array might be indexed by number, not by field name (depends on the database).
More Examples
Description
PowerLens Code
1
Show column {c} as bold
{c}
2
Capitalize first character of each word in column {c} using the PHP ucwords function
=ucwords({c})
3
Combine 3 columns in one: {book}, {chapter}, {verse} so it will appear as
[Genesis 2:3]
Global Variables
The $GLOBALS[] array is accessible from a powerLens. For example, to access $PHP_SELF, use {$PHP_SELF}.
Record Numbers
This is new to phpLens 1.2. Each row is assigned a record number and this can be obtained using the special {_RECNO_} variable. Useful for switching detail grids dynamically. The currently selected row number is stored in {_HILITE_RECNO_}.
Complete List of Macro Variables
{NBSP} = ' '
{SEMICOLON} = ';'
{_RECNO_} = record number powerLens is acting on
{_HILITE_RECNO_} = currently selected record number
{_LENSID_} = the phpLens object's id
{_ODDC_} = the odd row color
{_EVENC_} = the even row color
{_SELECTC_} = the hilite color for the selected record
{_HASDETAILS_} = whether the detail grid is visible
Prefix powerLens code with the following PHP codeDefault: If your powerLens code is accessing a special array (eg. $HTTP_COOKIE_VARS), add this array to the global declaration above.
Allows formulas in sumLens totalsDefault: When using sumLens, at the end of every group of data, a total is displayed. However you might prefer to display a formula instead of a total. The formula must begin with a '=' to indicate that we are running in PHP mode (so any PHP function or operator can be called).
All fields used in the powerSumLens must be also be included in sumLens. Also the special variable {#} is used to indicate the number of records.
Formula
We want COL3 = total(COL1) / total (COL2)
Then we define:
$lens->sumLens = 'COL1;COL2;COL3';
$lens->powerSumLens = 'COL3^={COL1}/{COL2}';
Calculating Averages
This property is particularly useful for displaying averages.
In the example below, {SalesPercent} is the sum(SalesPercent) for the given group of data. Therefore {SalesPercent}/{#} means:
(Total SalesPercent) / (no of records) = Average SalesPercent
This lens sets fields which are readonly in both edit and new record modeDefault: See also newReadOnlyLens and gridReadOnlyLens.
Setting this property for a field means that the user cannot change the field value.
However when creating a new record, you can still set the value to insert into the database using the defaultLens property. Also if you have the mustFill property, then when creating the record the field will be editable.
If you require an input field that is read-only that is manipulated using javascript, see the inputTypeLens property, readonly type.
After successful insert new record, redirect to another pageDefault: We now add a GET parameter so you can access the primary key of the inserted record (since phpLens 2.4.1):
Redirect to url when user clicks on a row in the gridDefault: The redirect also adds the primary key of the selected record as part of the url, as a GET variable. If the applet's name is 'applet', then the GET variable's name will be id_applet.
Set the color of rows in the grid dynamicallyDefault: If the first character is = then the rest of the line is evaluated as PHP code. The default rowColorLens can be seen in the syntax example below:
The following example highlights the selected row, and all those below the selected row:
where {_RECNO_} is the current row, and {_HILITE_RECNO_} is the selected or highlighted record number.
This is not particularly useful. The following is the actual rowColorLens used by phpLens:
$lens->rowColorLens=
'=LensRowColor("#FF8080",{_LENSID_},{_RECNO_},'.
'{_RECNO_}=={_HILITE_RECNO_},{_SELECTC_},{_ODDC_},{_EVENC_},{_HASDETAILS_},{_URLPARAMS_})';
To change the default select color from pink, change the #FF8080 above.
You can also use column names. For example, to highlight all negative prices in pink:
Controls whether to show 1 or 2 fields per row in new, edit & detail grids.Default: Assume we display 4 fields, F1, F2, F3, F4 in that order. Then assume the following settings:
$lens->sameRowLens = 'F1^EDIT^VIEW^NEW;F3^EDIT';
The example above sets F1 to display the next field (F2) in the same row in the edit, new record and details grid (VIEW), while F3 and F4 are in the same row only when editing.
Additional save options when editing or creating recordsDefault: NEW
$lens->saveOptions = "NEW", a new icon is displayed when editing, allowing you to create new records from the edit form.
DELETE
$lens->saveOptions = "DELETE", a delete icon is displayed when editing, allowing to delete the current record.
HIDETOPBTNS
$lens->saveOptions = "HIDETOPBTNS", will hide the Save and Cancel buttons that appear at the top of the form.
SAMEROW1COLOR
$lens->saveOptions="SAMEROW1COLOR", and if the sameRowLens property is used to force two fields in the edit/new form to share the same row, then the title caption background of the 2nd field are also colored with the detailColor. This makes the color scheme simpler.
OCISEQUENCE
$lens->saveOptions = "OCISEQUENCE^seqname" allows you to name the oracle sequence you are using within a trigger to set the integer serial id column of the table.
The following feature is experimental, and not supported:
$lens->saveOptions = "COLLECTIONS" will cause all checkbox/multi-select fields to be mirrored in another table. For example, if $lens->keyTable = 'master', then the mirroring table will be called "master_collection", and the fields of the table will be all fields that allow multiple values to be entered, plus the column containing the primary key. From 3.3.2 onwards, you can define the table to save in, eg. $lens->saveOptions = "COLLECTIONS^NameOfTable"
The background color in the navigation bar for scroll linksDefault: If scrollLinks set then the default color is #EEEECC (light yellow shade) or if no scrollLinks are defined, then we set it to $lens->colorNav.
If you define this color, then it is applied whether or not scrollLinks is defined.
Display a set of scrolling page links in the navigation barDefault: Set this to a positive number to indicate the number of page links to display. As the current selected page will be centered in the display, it is best to use an odd number. For example:
$lens->scrollLinks = 3;
Will display 3 links in the navigation bar (and assuming that we are at page 5):
|< < 4 5 6 > >|
To set the scroll background color, see scrollBgColor.
Tip
When scrolling to the end of the grid is slow, consider setting scrollLinks > 0. This is because setting scrollLinks will force phpLens to perform a count of the recordset, so it has a better idea of what record to scroll to reach the end of the grid.
Read-only property that holds number of records in all pagesDefault: This is only set when scrollLinks > 0, and is generated before templates are rendered, so it is accessible from a Smarty template using {php} tags.
{php}
global $lens;
echo $lens->scrollMaxRecordCount;
{/php}
This property is read-only and should not be modified.
Dynamically search for record and store results in fields when editing or new recordDefault: Suppose you have an customer complaint form (accessing the COMPLAINT table) where you need to search for a customer name, and store the customer id and address in the fields from the CUSTOMER table.
However you don't want to use a normal lookupLens because your CUSTOMER table has over 100,000 records. So you want the user to enter a search string first to narrow down the list of records to return from the server, and then click on a search button. As shown below:
[ Enter Customer Name ] [SearchBtn]
After clicking on the search button, a list of records will be displayed, and the user can select one of the records.
To add a Search button to the name field, use the following. The {#} is replaced with the text the user enters:
$lens->searchLookupLens = "name^select name from customer where name like '{#}%' ";
Extracting Multiple Fields If you want to update multiple COMPLAINT fields (cname,custid,caddress) in the Edit/New form, extracting from the CUSTOMER fields (name,id,address) when the user selects a value, use:
$lens->searchLookupLens =
"name^select name,name,id,address from customer where name like '{#}%' ^cname^custid^caddress";
The column name is repeated twice in the select name,name because the first mention of name is the text to display, and the second is the field to copy from.
Lookup only When Creating Records
To allow searching only in the New record form and not the Edit record form, place after the SQL statement this option !NEW :
$lens->searchLookupLens = "name^select name from customer where name like '{#}%' ^!NEW";
Lookup Switches from New to Edit
Suppose you don't want to populate the New record form when you find a match, but switch to editing the selected record, set the 2nd field of the select statement to the primary key of the record to be edited (custid in this example), and place after the SQL statement this option !NEWEDIT :
$lens->searchLookupLens = "name^select name,custid from customer where name like '{#}%' ^!NEWEDIT";
Require a Minimum Number of Characters to be Searched
Now you might require the user enter at least 3 characters in the search field before you allow them to click on the Search button. To do so add a '>3' to the property:
$lens->searchLookupLens =
"name^select name,name,id,address from customer where name like '{#}%' ^name^custid^address^>3";
Advanced Lookups (combining lookupLens with searchLookupLens)
Creates section breaks for edit/new record formsDefault: For example, to create section breaks above the NAME and CREATED fields titled "User Info" and "Audit" for VIEW, EDIT and NEW states:
$lens->sectionLens = 'NAME^!VIEW!EDIT!NEW^User Info;CREATED^VIEW!EDIT!NEW^Audit";
Now for something more complex. Make the section breaks above NAME and CREATED have green backgrounds, and the CREATED field uses the "redfont" CSS class.
The !EDIT!NEW below indicates that the section breaks are for EDIT and NEW states.
$lens->sectionLens = 'NAME^!EDIT!NEW^User Info^green;"
. "CREATED^!EDIT!NEW^Audit^green^redfont";
Check referer on save record is same as host serverDefault: This feature is obsolete. Due to the popularity of personal firewalls today, this feature is no longer effective, and is disabled permanently, even if set to true.
Details
As an additional security check, we make sure that the New/Edit record page came from the current server.
This might cause problems when using a load-balancing server farm, so you can disable it here.
keep track of order of updates by storing a seq no for every New/EditDefault: This is useful because when phpLens is asked to update or create a record, phpLens will check whether the user is authorised to edit those columns. This prevents hackers from modifying private fields.
This feature cannot be modified in the Professional version, and is always enabled. Only Enterprise users can disable this feature.
This feature currently does not work if templates are used.
Show popup calendar when editing entriesDefault: We are using a shareware popup calendar by Liming Weng called PopCalendarXP. Please only enable this for evaluation purposes, or if you have purchased this software from Robin.
If you prefer your own calendar, we provide the ability to define the javascript function to call when the calendar icon is clicked. Instead of setting $lens->showCal = true or false, set $lens->showCal to the name of the javascript function. See below for an example. This is since 2.2.6.
Syntax $lens->showCal = true;
# Alternatively define your own js calendar function
# the example below is not a full calendar and is
# to help you understand the API.
$lens->showCal = 'MyCalendar';
<script>
// zdoc is the document object
// ctrlname is the name of the control, starting with zdoc
// range normally range is set to null
// is an array in this format
// [RangeBeginDate, RangeEndDate {,disabledDate}]
function MyCalendar(zdoc,ctrlname,range)
{
var od,om,oy,d
od = eval(ctrlname+'_d');
om = eval(ctrlname+'_m');
oy = eval(ctrlname+'_y');
alert(od.value + '/'+om.value+'/'+oy.value);
// now we have 3 text objects which hold y/m/d
//now call your calendar and set the values...
/*
// call calendar function and return date!
d = ShowCalendar();
0=hide, 1=show on left of grid, 2=show on right of grid, 3 = hide details and show on left, 4 = hide grid show details, 5 = show details below gridDefault: New to phpLens 1.3 is showRecNo = 3.
3 will hide details grid and show the record number. If the childLens is enabled, the editor in the details area will still appear.
New to phpLens 2.4.2 is showRecNo = 4
4 will hide the normal grid and show the details only.
Clicking on column titles will sort columnsDefault: First click will sort ascending, second click will sort descending, and third to revert back to sort ascending, etc.
