Creating, Editing and Locking Records

Creating and Editing Records

To modify records, you need the Advanced or Enterprise version of phpLens. Using phpLens to generate forms is a productivity boost because

Input fields are autogenerated for you.
Must fill fields are autodetected on the browser (using Javascript) and also by the server (when Javascript is disabled on the client).
The maximum field length and text box width is set for you automatically based on the field size.
Long text columns are autodetected and displayed as textarea input fields.
Field validation using Visual Basic style input masks or Javascript regular expressions are supported.
Popups, check boxes and radio buttons are automatically created based on lookupLens and inputTypeLens settings.
Image (gif, png, jpeg) display and upload is supported if the field is of type BLOB (MySQL, Oracle) or BYTEA(PostgreSQL).
Better security as phpLens prevents unauthorized fields from being accessed using a fake input form.
Template support to separate presentation from logic in Advanced and Enterprise versions.
High level abstraction of forms makes design and implementation of Web sites faster and easier.

Before phpLens can modify records, it needs to know the name of the table and the primary key of the table it is modifying. The primary key is a number or string that allows you to uniquely identify the record. For example, for a person the primary key could be the driver's license, the social security number or the identity card number. If you are using a simple select statement, phpLens can automatically identify the table. The primary key can be a single field or, since phpLen 2.7, a compound key (multiple fields).

To configure editing from the dynamic editor, click on the phpLens global settings icon. Scroll past the color settings until you see the following:

In the above example, phpLens could not auto-detect the primary key. So you have to enter the primary key from the popup list on the left. Once selected, you can click the following checkboxes:

Then when you are looking on the phpLens grid, you can click on the New or Edit buttons to create or update records. Similarly for deleting records.

Compound Keys

For compound keys, leave the primary key field blank, and enter the list of fields that make up the compound key, delimited by ; in the Compound Key field like this:

keyone;keytwo;keythree

Tips

You can configure the following things when creating records:

How Do I...	Solution	Properties modified
Decide on which fields to display	Click on the green icon to see the New Record Columns. Then move the fields from left to right to display, and right to left to hide.	newLens
Change the field title	Click on the white icon for the field you want to modify and change that field's title.	nameLens
Use a popup instead of a text field	Click on the white icon for the field you want to modify and set the lookup values (options) you want for the popup.	lookupLens
Use a set of radio buttons instead of a text field.	Click on the white icon for the field you want to modify and set the lookup values (options) you want for the popup and click on the Use Radio Buttons setting. New to phpLens 1.2.	lookupLens, inputTypeLens
Have a set of save buttons. Each save button sets a different value for a field. Useful for approval and workflow forms.	Click on the white icon for the field you want to modify and set the lookup values (options) you want for the popup and click on the Use Submit Buttons setting. This will hide the default Save button. New to phpLens 1.2.	lookupLens, inputTypeLens
I do not want the user to be able to cancel	Set the showCancel property to false programmatically.	showCancel
Use a textarea field instead of text input field	Click on the gray icon next to each column to go to the Edit Column screen. Set Input Dimensions, Rows to a value greater than 1.	textSizeLens
Change the textarea or input field dimensions	Click on the gray icon next to each column to go to the Edit Column screen. Set Input Dimensions, Columns and Rows.	textSizeLens
Set default values	Click on the white icon for the field you want to modify and enter the default value.	defaultLens
Prevent users from modifying default values	Click on the green icon to see the New Record Columns screen. From the top of the screen, select from the Choose popup the Read Only Columns option.Then move the fields from left to right to make them read-only, and right to left to make them modifiable.	readOnlyLens
Control the SQL used to insert the data	Requires the Advanced or Enterprise version of phpLens. Configure the spNew property.	spNew
Execute some PHP code before SQL insert is run	Configure the eventPreInsertSQL property programmatically.	eventPreInsertSQL
Execute some PHP code after SQL insert is run	Configure the eventPostInsertSQL property programmatically.	eventPostInsertSQL
Define an input field that phpLens should ignore	Use templates. Requires Advanced or Enterprise version of phpLens.	templateNew
Validate the data	Set the field as a must fill, or define a validation expression. Click on the green icon to see the New Record Columns screen. From the top of the screen, select from the Choose popup the Must Fill Columns option. For more control, a validation expression can be defined as a Visual Basic style input mask or a Javascript regular expression.	mustFill, validation
Use the WYSIWYG HTML editor for a field	Click on the green icon to see the Edit Columns screen. Check WYSIWYG Editor. You do not have to check Do No Convert Special Chars.	htmlEditLens
After creating a record, sometimes I cannot see it in the grid	You can force phpLens to sort descending on a field such as creation date or an autoincrementing key after a record is created. This will cause the newly created record to rise to the top. Configure the sortDescOnNew property programmatically.	sortDescOnNew
Store attachments such as Word documents in a record?	Use the attachment property. This will show a download link, and a text field where the original file name and file size are shown.	attachment

And these are the settings for editing records:

How Do I...	Solution	Properties modified
Decide on which fields to display	CClick on the green icon to see the Edit Columns screen. Then move the fields from left to right to display, and right to left to hide.	editLens
Change the field title	Click on the white icon for the field you want to modify and change that field's title.	nameLens
Use a popup instead of a text field	Click on the white icon for the field you want to modify and set the lookup values (options) you want in the popup.	lookupLens
Use a textarea field instead of text input field	Click on the gray icon next to each column to go to the Edit Column screen. Set Input Dimensions, Rows to a value greater than 1.	textSizeLens
Change the textarea or input field dimensions	Click on the gray icon next to each column to go to the Edit Column screen. Set Input Dimensions, Columns and Rows.	textSizeLens
Prevent users from modifying visible values	Click on the green icon to see the Edit Columns screen. From the top of the screen, select from the Choose popup the Read Only Columns option. Then move the fields from left to right to make them read-only, and right to left to make them modifiable.	readOnlyLens
Control the SQL used to insert the data	Requires the Advanced or Enterprise version of phpLens. Configure the spEdit property programmatically.	spEdit
Execute some PHP code before SQL update is run	Configure the eventPreUpdateSQL property programmatically.	eventPreUpdateSQL
Execute some PHP code after SQL update is run	Configure the eventPostUpdateSQL property programmatically.	eventPostUpdateSQL
Define a field that phpLens should ignore	Use templates. Requires Advanced or Enterprise version of phpLens.	templateEdit
Validate the data	Set the field as a must fill, or define a validation expression. Click on the green icon to see the Edit Columns screen. From the top of the screen, select from the Choose popup the Must Fill Columns option. For more control, a validation expression can be defined as a Visual Basic style input mask or a Javascript regular expression.	mustFill, validation
Allow image upload	Make sure that the database field is of type BLOB or IMAGE, and that the temporary upload directory property imageTmpDir (which defaults to /tmp) is set correctly. If you do not have read-write access to the /tmp directory, you will also need to modify the $TMP_DIR value in the phplens-img.php file. This file is used to display any images extracted from a database.	imageTmpDir
Selectively define some records to be editable or deletable.	Define two columns in your SQL statement, one called _CANEDIT_ and the other called _CANDELETE_. If the fields are non-zero, then the record is editable or deletable respectively. See the canEdit and canDelete properties for examples.	canEdit, canDelete
Create links to edit and create new records	See our FAQ at http://phplens.com/lens/faq/faq.php?#402
Use the WYSIWYG HTML editor for a field	Click on the green icon to see the Edit Columns screen. Check WYSIWYG Editor. You do not have to check Do No Convert Special Chars.	htmlEditLens
When editing, it takes a long time to load because I need to lookup on a field with over a 100,000 records.	Use a searchLookupLens. Instead of displaying a popup menu, it displays a text field where you key in a few characters to start with. Then you click on the search button. All lookup records that match the search will then be shown as a popup menu. See ex919 and ex920 for examples.	searchLookupLens

Child Editor in Detail Grid

You can also edit and create records in the detail grid. See the new drop-down list labelled Child Editor in Detail Grid just above the Key Table setting and select one of the settings.For example, have a look at example 320, phplens.com.

Multiple Record Edit in Grid

Since phpLens 2.0 you can edit multiple records in one grid. This is easy to configure, set:

 $lens->multipleEdit = true;

You can also configure this in a checkbox in phpLens settings. Every field defined in $lens->editLens and $lens->gridLens will be editable in the grid.
Fields that are only defined in $lens->gridLens will be readonly. The detail grid on the right remains read-only.

When you click on the Save button, every row of the grid is treated as a separate SQL insert statement. The phpLens event*SQL properties are called multiple times, once for every row.

Performance When Editing in Grid

For good performance, you should cache your popup lists using the cacheLookups property and combine it with the ADODB_CACHE_DIR constant.

$lens->cacheLookups = 600; // cache for 600 seconds
define('ADODB_CACHE_DIR','/tmp'); // and store cache in this directory

Record Locking

Since version 3.9, phpLens has offered the ability to lock records when editing them. Other users accessing that record will see a message indicating that the record is locked, and by whom. Record locking requires you to define a PHP global variable that holds the user id of the person locking the record. Then set the $lens->lockRec property (in this example, $USERID holds this info):

$lens->lockRec = 'USERID';

We provide 2 ways of locking. (a) locking using the phplens_lock table, and (b) locking by modifying the record directly.

(a) Locking with the phplens_lock table

The phplens_lock table looks like this:

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)
)

When a record is locked, we add a new row to the phplens_lock table, indicating that the record in question is locked, by whom, which applet, and when it was locked. Every 30 seconds (this is tunable using the lockTimer property), the browser will make a remote call to the server to update this phplens_lock row, informing others that the user is still editing the record. If this phplens_lock row is not updated within 65 seconds, then we assume that the user has quit the browser or is on another page, and the record is now unlocked.

The remote query is made to the phplens/phplens_remoteq.php script.

(a) Locking within the record

If you want a higher performance locking solution, you should modify your table (the keyTable), 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';

Whenever a record is locked in that table, these fields will be used for identifying who locked, and at when was it locked.

Locking Details

The user id variable is stored in the LOCK_BY field and should be no longer than 56 bytes. The remaining 40 bytes is used internally by phpLens.

By default, when the user tries to edit a record that is locked, we redirect back to the original page. However sometimes you want the user to view the record details in the edit form, so you can disable this redirection by setting:

$lens->lockNoRedirect = true;

The lockTimer property controls how long locking occurs. It is an array (not a string). Let's say we want to update phpLens every 60 seconds, and timeout if no update occurs for 185 seconds:

$lens->lockTimer = array(60, 185);

The default is (30,65). In general, update secs < timeout.

Saving Multiple Applets Simultaneously

Since phpLens 4.2.2, you can modify the behaviour of phpLens to save multiple applets when you click on the Save button of any of these applets. The coding is a bit complicated, so we will illustrate with an example.

Suppose you want to have applets named appA and appB saved together when either Save button is clicked. Add the following property saveBtnOnClickJS to both appA and appB applets; the javascript defined in this property will be executed when you click on the Save button of either applet:

	$lens->saveBtnOnClickJS = 'post_all_applets()';

Now the HTTP standard allows us to post only one form, but we have data in two forms. This means we have to extract all the input fields from both appA and appB forms, perform validation, then post the gathered data from a third invisible form, document.post_all_data, shown below:

	 <form name=post_all_data method=post action='{$_SERVER["PHP_SELF"]}'>
	 <textarea name=postdata rows=5></textarea>
	 </form>

The data from the two applets to be merged will be raw urlencoded and stored in the document.post_all_data.postdata field. In the example above, postdata is a textarea for easier debugging. In reality, you would use a hidden field.

Now here's the javascript:

<script>
	function post_all_applets()
	{
	var i, ele, eq, cr, postdata,fm;
	
	 eq = '=';
	 cr = '&';
	 
	 postdata = '';
	 
	 //---------------------------------------------------
	 
	 fm = document.phplens_appA_edit  // appA
	 if (fm) {
		 if (!PHPLENS_Validate_appA()) return false; // appA
		 for (i = 0; i<fm.elements.length; i++) {
		 	ele = fm.elements[i];
			postdata += escape(ele.name) + eq + escape(ele.value) + cr 
		 }
	 }
	 //---------------------------------------------------
	 fm = document.phplens_appB_edit  // appB
	 if (fm) {
		 if (!PHPLENS_Validate_appB()) return false; // appB
		 for (i = 0; i<fm.elements.length; i++) {
		 	ele = fm.elements[i];
			postdata += escape(ele.name) + eq + escape(ele.value) + cr 
		 }
	 }
	 
	 // data all validated and complete!
	 document.post_all_data.postdata.value=postdata
	 document.post_all_data.submit();
	 return false;
	}

</script>

In phplens, the javascript validation function is called PHPLENS_Validate_$appletname(), and the form name is called phplens_{$appletname}_edit. The one exception is when you are using a child lens, when the form name is phplens_{$appletname}_child_lens_edit and the javascript function is called PHPLENS_Validate_{$appletname}_child_lens().

So if the appA form has a field "lens_FC_A1" containing "ABC" and appB form has two fields "lens_FC_B1" having "=1" and "lens_FC_B2" having "DEF", the following postdata will be generated:

	lens_FC_A1=ABC&lens_FC_B1=%3D1&lens_FC_B2=DEF

To process the post on the server-side, we convert the postdata into multiple $_POST fields. Make sure this is done before the applets are called:

	if (isset($_POST['postdata'])) {
		$eq = '=';
	 	$cr = '&';
		$arr = explode($cr, $_POST['postdata']);
		foreach($arr as $pair)  {// eg. ABC=123  => $_POST['ABC'] = '123'
			$arr2 = explode($eq,$pair);
			if ($arr2[0] != 'lens_cancel')
				$_POST[rawurldecode($arr2[0])] = @rawurldecode($arr2[1]);
		}
	}

We don't add the lens_cancel as that will wreck havoc with the logic.