Rapid Application Development toolkit for building Administrative Web Applications

RADICORE for PHP - Functions, Methods and Variables

By Tony Marston

10th March 2006
Amended 1st July 2008

As of 10th April 2006 the software discussed in this article can be downloaded from www.radicore.org

Introduction

Abstract Table Class

- Sequence of Events

- Notes on Public Methods

- Notes on Internal (private) Methods

- Notes on Customisable Methods

- Class Properties

Non OO functions

Session Variables

Global Variables

Amendment History

Introduction

The RADICORE library consists of functions, methods and variables which exist in different 'include' files, such as the following:

In the following sections there are some elements which you can reference in your code while there are others which should only be referenced by the framework. The "framework only" elements have been given a different background colour, as follows:

This element is only to be referenced by the framework, not user code

If you try to use any of these elements in your code the results may be unexpected.

Abstract Table Class

Each database table within a RADICORE application has its own table class which contains specific information regarding that particular database table. This class inherits a great deal of common code from the abstract table class. It is the abstract table class that contains the methods which are accessed from all the controllers, and it is the abstract table class which communicates which the database through the data access object (DAO).

Each operation, which starts from the controller and may end in the database, contains a series of distinct steps or stages. Some of these steps in the abstract table class are in fact nothing more than empty methods, and as they contain no code they have no effect on that operation. If any of these empty methods is copied from the abstract table class (superclass) into an application table class (subclass) it will override the method in the superclass. This means that any method found in the subclass will be executed instead of a method with the same name that may be defined in the superclass. This then provides an opportunity to insert code into that empty method so that it can be executed at the appropriate stage of the operation.

These empty methods are known as "customisable methods" in RADICORE as they are predefined and waiting to be filled with custom code. The signature (API) and the return parameter cannot be changed, only what happens in between. Note that within any subclass it is possible to create additional methods other than those mentioned below, but these additional methods will not be executed unless they are called from one of the pre-defined customisable methods.

The following naming conventions are used for methods within the abstract table class:

In the following sections certain function/method arguments are defined as follows:

1. $rowdata/$fieldarray - an associative array of 'name=value' pairs containing fields from a single database occurrence. Note that this may also contain fields which have been obtained from other sources (e.g. a sql JOIN).
2. $rows - an indexed array containing multiple database occurrences, where the index is treated as the row/occurrence number, and each row contains $rowdata for that occurrence.
3. $where - a string that contains selection criteria in a format which can be used as the WHERE clause in an SQL query, such as:

   field1='value1' AND field2='value2' .....
   

Sequence of Events

In the following table the column labelled 'Entry Point' indicates the method which may be called from outside, either from a controller or another table class. The column labelled 'Sequence' shows other methods which may be called internally, and in what sequence.

Notes on Public Methods

boolean = $object->cascadeDelete ($where)

$where is a string in the format of a WHERE clause in an SQL query.

The steps in this operation are as follows:

• Call getData_raw() to retrieve all records which match the condition in $where.
• For each record call deleteRecord().

$this->errors will contain any error messages.

boolean = $object->cascadeNullify ($update_array, $where)

$update_array is an associative array which identifies the field(s) to be updated (usually 'foreign_key=NULL').
$where is a string in the format of a WHERE clause in an SQL query.

The steps in this operation are as follows:

• Call getData_raw() to retrieve all records which match the condition in $where.
• For each record call updateRecord() to make the changes supplied in $update_array.

$this->errors will contain any error messages.

$fieldarray = $object->clearEditableData (fieldarray)

$fieldarray (IN) is is an associative array containing the details of a single database occurrence.
$fieldarray (OUT) will be the input array with any amendments.

This will examine each field if $fieldarray, and if it is editable (i.e. the noedit attribute is not set), the current value will be nullified.

void $object->clearScrollArray ()

This is used to delete the contents of $this->scrollarray.

$errors = $object->commit ()

$errors will be an array of zero or more error messages.

This will call _examineWorkflow() and either issue a commit or rollback depending on the result. Note that this has no effect on tables which do not support database transactions, such as MyISAM tables.

$rows = $object->deleteMultiple ($rows)

$rows (IN) is an indexed array of database occurrences, and each occurrence is an associative array of name=value pairs.
$rows (OUT) is the same array.

For each record identified in $rows it will call the deleteRecord() method.

$this->errors will contain any error messages.

$this->numrows will contain the count of deleted records.

$this->messages will contain a message in the format 'N records were deleted from TABLENAME'.

$rowdata = $object->deleteRecord ($rowdata)

$rowdata (IN) is an associative array containing the details of a single database occurrence.
$rowdata (OUT) is the same array.

The steps in this operation are as follows:

• Call isPkeyComplete to verify that a single record has been selected via its primary key.
• Call _cm_pre_deleteRecord to perform any custom pre-processing.
• Call deleteRelations() to delete any child relations.
• Call _dml_deleteRecord() to update the database.
• Call _cm_post_deleteRecord to perform any custom post-processing.

$this->errors will contain any error messages.

Note that the deletion may have been verified beforehand by a call to validateDelete().

void $object->deleteRelations ($rowdata)

$rowdata is an associative array containing the details of a single database occurrence.

This will examine the contents of $this->child_relations for the database occurrence identified in $rowdata, and for each child record found belonging to that relationship it will perform the action indicated by its type:

• DELETE - call cascadeDelete().
• NULLIFY - call cascadeNullify().
• RESTRICTED - no action.

$this->errors will contain any error messages.

$where = $object->deleteScrollItem ($index)

$index is the index number an entry in $this->scrollarray.
$where will contain a string which identifies the next available occurrence.

This is used to delete an item from $this->scrollarray, and to return the primary key of the next available entry.

$msg = $object->deleteSelection ($selection)

$selection is a string in the format of a WHERE clause in an SQL query and may identify one or more database occurrences.
$msg will contain a message in the format 'N rows were updated'.

This passes control to _cm_deleteSelection() to perform the relevant processing for those records which match the selection criteria.

$this->numrows will contain the count of records which were deleted.

$rowdata = $object->eraseRecord ($rowdata)

$rowdata (IN) is an associative array containing the details of a single database occurrence.
$rowdata (OUT) is the same array.

The sequence of events is as follows:

• Call isPkeyComplete() to verify that the primary key is complete.
• Call _cm_pre_eraseRecord() to perform any pre-processing, such as to turn audit logging off..
• Call eraseRelations() to delete ALL child relations, even those marked as RESTRICTED.
• Call _dml_deleteRecord() to delete the specified record.
• Call _cm_post_eraseRecord() to perform any post-processing.

This will effectively delete the selected record, its children, the children of its children, and so on all the way down to the bottom of the relationship hierarchy.

$this->errors will contain any error messages.

void $object->eraseRelations ($rowdata)

$rowdata is an associative array containing the details of a single database occurrence.

This will iterate through the contents of $this->child_relations and perform the following on any related occurrence:

• If the related table is in another subsystem then do nothing.
• If the relationship type is NULLIFY then call cascadeNullify().
• If the relationship type is DELETE or RESTRICTED then retrieve all relevant child records using getData_raw() and call eraseRecord() for each one.

$this->errors will contain any error messages.

$rowdata = $object->fetchRow ($resource)

$resource is a database resource created by getData_serial().
$rowdata will contain an associative array of all the fields from a single database occurrence.

This is used to return database records one at a time instead of in multiples. After each iteration the resource pointer will be advanced to the next occurrence, and $rowdata will return FALSE when end-of-file is reached.

The actual steps are as follows:

• Call _dml_fetchRow() to fetch the next record.
• Call _cm_post_fetchRow() to perform any actions before the record is passed to the next process.

$output_name = $object->fileUpload ($input_name, $temp_file)

$input_name is the name of the file being uploaded from the client.
$temp_file is a copy of the file in the temporary directory.
$output_name is the file name to be used on the server.

This is used to deal with the file being uploaded from the client before it is written to the server, for example changing it's name into something more meaningful. A temporary copy of the file is available in $temp_file so that its contents may be checked, such as verifying that an image file has the correct dimensions.

This is a wrapper for _cm_fileUpload().

$rowdata = $object->formatData ($rowdata)

$rowdata (IN) is an associative array containing the details of a single database occurrence.
$rowdata (OUT) is the same array after any values have been formatted.

This is called when the data is being transferred to the XML file for output to the user. It will take the contents of $rowdata and, using the field specifications in the $this->fieldspec array, will format any values, such as dates and numbers, before they are displayed to the user. For example, a date such as '2006-01-02' will be converted into '02 Jan 2006'.

It will also call _cm_formatData() to perform any custom formatting.

This is the opposite of unformatData().

$result = $object->free_result ($resource)

$resource is a database resource created by getData_serial().
$result will return either TRUE or FALSE.

This is used to free a resource created by getData_serial() and processed by fetchRow().

$name = $object->getClassName ()

$name will contain the name, in lower case.

This will return the name of the current class so that it can be used as the identity of the current database table when adding data to an XML document. Where the current class is a subclass with a numerical suffix, as in _s01, this will be removed. For example:

• mnu_task will return mnu_task.
• mnu_task_s01 will return mnu_task.
• mnu_task_jnr will return mnu_task_jnr.

A numerical suffix is used when the purpose of the subclass is to contain some different business logic. A non-numeric suffix is used when data from the same database table needs to be inserted into multiple zones within the same XSL stylesheet, in which case the data for each zone comes from an object with a different class name, such as mnu_task_snr and mnu_task_jnr. This is described in Using subclasses to provide alias names

$count = $object->getCount ($where)

$where is a string which identifies what needs to be counted.
$count will contain the count of entries.

If the $where string is in the format column='value' the following statement will be constructed and executed:

$count = $this->getCount("column='X'");
  will execute:
SELECT count(*) from $this->tablename WHERE column='X'

If the $where string begins with 'SELECT ' it will be assumed to be a complete SQL statement and will be executed without any modification, as with the following examples:

$count = $this->getCount("SELECT MAX(seq_no) FROM table27 WHERE column='X'");

$count = $this->getCount("SELECT SUM(quantity) FROM order_item WHERE order_id=42");

$rows = $object->getData ($where)

$where is a string in the format of a WHERE clause in an SQL query and may identify one or more database occurrences.
$rows will be an indexed array of different database rows (occurrences) and each row will be an associative array of field names and values.

This is called from within each page controller to process any selection or search criteria passed down by the parent screen, or from a child SEARCH screen, before constructing an SQL query to retrieve data from the database. Because this uses strings in the $where clause which are constructed by the framework it may not be suitable for $where clauses which are constructed manually inside any object. In these cases the getData_raw() method may be more appropriate.

The following optional variables are also used to construct the SQL query:

• $this->sql_select
• $this->sql_from
• $this->sql_where
• $this->sql_groupby
• $this->sql_having
• $this->sql_orderby
• $this->sql_search
• $this->rows_per_page
• $this->pageno

The steps in this operation are as follows:

• Call _cm_pre_getData() to perform any custom pre-processing.
• if $this->checkPrimaryKey is TRUE then call ispkeycomplete to verify that a full primary key has been supplied.
• Call _sqlAssembleWhere() to make any adjustments to the variables that will be used to construct the sql SELECT statement.
• Call _dml_getData() to retrieve records from the database.
• Call _processInstruction() to process any instructions created by the previous task.
• Call _cm_post_getData() to perform any custom post-processing.

$this->numrows will contain the count of records retrieved from the database.

$rows = $object->getData_raw ($where)

$where is a string in the format of a WHERE clause in an SQL query and may identify one or more database occurrences.
$rows will be an indexed array of different database rows (occurrences) and each row will be an associative array of field names and values.

This calls _dml_getData() without adjusting any related object properties beforehand, and without formatting the data afterwards.

When retrieving another table's data from within a table object this method should be used instead of getData() where the reformatting of the $this->sql_select, $this->sql_from and $where variables inside _sqlAssembleWhere() may cause problems, especially with complex combinations of AND, OR and NOT.

The following optional variables are also used to construct the SQL query:

• $this->sql_select
• $this->sql_from
• $this->sql_where
• $this->sql_groupby
• $this->sql_having
• $this->sql_orderby
• $this->sql_search
• $this->rows_per_page
• $this->pageno

$this->numrows will contain the count of records retrieved from the database.

$resource = $object->getData_serial ($where)

$where is a string in the format of a WHERE clause in an SQL query and may identify one or more database occurrences.
$resource will be a resource that can be processed by the fetchRow() method, and subsequently released by the free_result() method.

This is similar to getData(), but instead of returning an array of occurrences it returns a resource from which individual occurrences can be obtained one at a time using fetchRow(). This is useful for batch jobs in which an unknown number of database occurrences need to be processed as it could cause problems if all those occurrences were retrieved and loaded into an array before they were processed. With this method it is possible to fetch and process a row before fetching the next row.

The following optional variables are also used to construct the SQL query:

• $this->sql_select
• $this->sql_from
• $this->sql_where
• $this->sql_groupby
• $this->sql_having
• $this->sql_orderby
• $this->sql_search
• $this->rows_per_page
• $this->pageno

The steps in this operation are as follows:

• Call _cm_pre_getData() to perform any custom pre-processing.
• Call isPkeyComplete() if $this->checkPrimaryKey is TRUE.
• Call _sqlAssembleWhere() to make any adjustments to the variables that will be used to construct the sql SELECT statement.
• Call getData_serial() to obtain a resource from the database.

$this->numrows will contain the count of records retrieved from the database.

Here is some sample code:

$where = "workitem_status='EN' AND deadline <= '$now'";
$workitem_result = $workitem->getData_serial($where);
while ($row = $workitem->fetchRow($workitem_result)) {
    ... process contents of $row ...
} // while
$result = $workitem->free_result($workitem_result)

$array = getExpanded ()

$array will contain a list of nodes which have been expanded.

When viewing a tree structure it is possible to expand a node so that its children will appear in the display. This will identify those nodes which have been expanded so that the system will retrieve its children.

$array = getExtraData ($input)

$input can be either a string (in the format of a WHERE clause) or an associative array. A string will be converted into an array.
$array will be the same as $input, but after any changes have been made.

This retrieves data that may be of use during the transaction which would not otherwise be made available, such as for INSERTS. The contents of $input may be a WHERE string, or it may be an array of database records. The sequence of steps is as follows:

• For each entry in $input call getForeignData().
• Call _cm_getExtraData().
• Call _cm_changeConfig().

$array = $object->getfieldspec ()

$array will be an associative array of field names and their specifications.

This will return the current contents of the $this->fieldspec. Note that it may have been amended during the processing of the current task.

$array = $object->getfieldspec_original ()

$array will be an associative array of field names and their specifications.

This will return the contents of the $this->fieldspec array after it has been reloaded from the <tablename>.dict.inc file which was exported from the Data Dictionary. This is done immediately prior to any INSERT or UPDATE operations to remove any changes made in custom code as these changes may be out of step with the physical database structure and could cause a fatal error.

$rowdata = $object->getForeignData ($rowdata)

$rowdata (IN) is an associative array containing the details of a single database occurrence.
$rowdata (OUT) will be the same array, but after any changes have been made.

This will first call _cm_getForeignData() to perform any custom processing.

Then it will examine the contents of $this->parent_relations. For each relationship it will perform the following:

• If the value of parent_field for the relationship is not empty, and
• The parent_field field does not exist in the data for the current record, and
• All the foreign key fields to access the parent exist in the data for the current record, then
• Retrieve the specified record from the foreign (parent) table and merge its data with that for the current (child) record.

Here is an example of the code:

    foreach ($this->parent_relations as $reldata) {
        if (isset($reldata['parent_field'])) {
            // may be more than one parent_field, so turn it into an array of separate field names
            $parent_fields = extractFieldNamesIndexed($reldata['parent_field']);
            // construct WHERE clause to read from parent table
            $where = '';
            foreach ($reldata['fields'] as $fldchild => $fldparent) {
                if (strlen($fldchild) < 1) {
                    // 'Name of child field missing in relationship with $tblchild'
                    $this->errors[] = getLanguageText('sys0110', strtoupper($tblchild));
                    break;
                } // if
                if (!isset($fieldarray[$fldchild]) or strlen($fieldarray[$fldchild]) == 0) {
                    // foreign key field is missing, so stop further processing
                    $where = '';
                    break;
                } // if
                if (empty($where)) {
                    $where = "$fldparent='" .addslashes($fieldarray[$fldchild]) ."'";
                } else {
                    $where .= " AND $fldparent='" .addslashes($fieldarray[$fldchild]) ."'";
                } // if
            } // foreach
            if (empty($where)) {
                // $where is empty, so set foreign field(s) to empty
                foreach ($parent_fields as $ix => $parent_field) {
                    $fieldarray[$parent_field] = null;
                } // foreach
            } else {
                if (isset($fieldarray[$parent_fields[$ix]]) AND !empty($fieldarray[$parent_fields[$ix]])) {
                    // (last) parent field is already there, so do nothing
                } else {
                    $tblparent = $reldata['parent'];
                    // instantiate an object for this table
                    if (array_key_exists('subsys_dir', $reldata)) {
                        $dir = $_SERVER['DOCUMENT_ROOT'] .getParentDIR() .'/' .$reldata['subsys_dir'] .'/';
                    } else {
                        $dir = NULL;
                    } // if
                    if (!class_exists($tblparent)) {
                        require_once $dir ."classes/$tblparent.class.inc";
                    } // end
                    $parentobj = new $tblparent;
                    $parentobj->sql_select = $reldata['parent_field'];
                    $parent_data = $parentobj->getData($where);
                    if (count($parent_data) > 0) {
                        // copy specified parent field(s) into $fieldarray
                        foreach ($parent_fields as $ix => $parent_field) {
                            $fieldarray[$parent_field] = $parent_data[0][$parent_field];
                        } // foreach
                    } else {
                        // not found, so set foreign key(s) to empty
                        foreach ($reldata['fields'] as $fldchild => $fldparent) {
                            $fieldarray[$fldchild] = null;
                        } // foreach
                    } // if
                } // if
            } // if
        } // if
    } // foreach

Click on the following hyperlink for a description of getLanguageText().

$array = $object->getInitialData ($input)

$input is either a string in the format of a WHERE clause, or an associative array.
$array will be an associative array containing initial values.

This is used with INPUT tasks to supply any initial values before the screen is displayed to the user. Without it all the fields would be blank.

The steps in this operation are as follows:

• If $input is a string then convert it into an associative array.
• Any field is this array will have the noedit option set in $this->fieldspec so that its value cannot be changed in the screen.
• Load contents of $this->initial_values into the array.
• Call _cm_getInitialData() to perform any custom processing.
• If any field has the autoinsert or autoupdate option set it will also have the nodisplay option set so that it will not appear in the screen.
• If any field (except a date or time) has a default value set this will be loaded as its initial value.
• Any fields which exist in the table specifications but are not yet defined in the current transaction will have their values set to null so that they appear in the XML document and the resulting input screen.

$array = $object->getInitialDataMultiple ($input)

$input can be either a string (in the format of a WHERE clause) or an associative array. A string will be converted into an array.
$array will be the same as input, but after any initial values have been added.

This is a wrapper for _cm_getInitialDataMultiple().

This is used in the ADD4 transaction pattern to create the records that will be inserted by calling insertMultiple().

This is also used in the ADD5 transaction pattern to create a number of blank records that will be displayed in the screen into which the user can then enter data.

$index = $object->getLastIndex ()

$index is the highest index number in $this->scrollarray.

$array = $object->getNodeData ($expanded, $where)

$expanded is either an indexed array which identifies the nodes which are to be expanded, or a string containing 'ALL' to signify all available nodes.
$where is a string which is used as selection criteria.
$array will be an indexed array of different database occurrences, and each occurrence will be an associative array of field names and values.

The steps in this operation are as follows:

• Call _cm_getNodeData() to perform any custom processing.
• Call _processInstruction() to process the contents of $this->instruction.

$array = $object->getPkeyArray ($rowdata=null, $next_task=null)

$rowdata is an optional associative array of name=value pairs for a single database occurrence.
$next_task is as associative array with details of the next task
$array will be the same as the input array, but will only contain those fields which exist within the primary key.

If $rowdata is not supplied then $this->fieldarray will be used instead.

The processing sequence is as follows:

• Call getPkeyNames() to obtain the list of field names which make up the primary key of the current table.
• Call _cm_getPkeyNames() in case this list of primary key fields needs to be temporarily adjusted.
• For each row in $rowdata it will then remove those fields which are not in the list of primary key fields.

The remaining fields will then be used to construct a string in the format of the WHERE clause of an sql SELECT statement.

$array = $object->getPkeyNames ()

$array will be an indexed array of field names.

This is used to obtain a list of all the fields which comprise the primary key in the current table. It will obtain the relevant information from $this->primary_key.

$index = $object->getScrollIndex ()

$index is the current pointer into $this->scrollarray.

$where = $object->getScrollItem (&$index)

$index (IN) is a key to an entry in $this->scrollarray.
$index (OUT) will contain an index number which may be different.
$where will contain the primary key of the entry indicated by $index.

The processing sequence is as follows:

• Ensure that $index points to an entry in $this->scrollarray.
• Call addJump() to ensue that $index does not point to an entry between two jump points.
• Call array2where() to convert the entry into a $where string.

$size = $object->getScrollSize ()

$size is the current count of items in $this->scrollarray.

$output = $object->getWhere ($next_task)

$output is the $where string from the object.
$next_task is an associative array which contains details of the child task.

This is called when a navigation button is pressed in order to extract the $where string from the main object in the current task so that it can be passed to the next task.

The _cm_getWhere() method is called in case this string needs to be customised.

See also: Appendix I: Passing context between objects.

$array = $object->getValRep ($item, $where)

$item is the name of the list to be returned as there may be more than one.
$where is an optional string containing selection criteria.
$array will contain the output as an associative array.

This is used to obtain the contents of a dropdown list or radio group. The term "ValRep" stands for "Value/Representation" where "Value" is what is held internally and "Representation" is what is shown to the user. The list may either be hard-coded, or extracted from a database table. The implementation details are defined in _cm_getValRep().

$where = $object->initialise ($where, $selection)

$where (IN) is a string in the format of a WHERE clause in an SQL query.
$selection is an optional string similar to $where
$where (OUT) is the version of $where (IN) after any amendments have been applied.

This is used in all forms to perform any initialisation when the form is first activated. It does the following:

• Call _getInitialValues()
• If the task pattern is LIST then call _getInitialWhere()
• Call _cm_initialise()
• Call _cm_changeConfig()
• Call _cm_filterWhere()
• Call _cm_setJavaScript()
• Call _examineWorkflowInstance()

Note that the $selection string is only available with tasks of type ADD2, which is called from a LIST2 task. The $where string will identify the parent entity, and if anything was selected from the child entity in will be identified in the $selection string.

All INPUT screens will also use the getInitialData() method.

If $mode is equal to "search" then the following actions will be taken:

• Any field which is passed down in $input will have the noedit option set in the field specifications so that its value cannot be changed in the search screen.
• If any field has the nosearch option set then the nodisplay option will also be set so that the field will be excluded from the search screen.
• If any field has the autoinsert or autoupdate option set it will be excluded from the search screen.
• If any previous search criteria exists for the current table then this will be loaded into the search screen.
• If the current table contains fields called "start_date" and "end_date" it will add an extra field called "curr_or_hist" (current or historic) to the output. This will have to be included in the screen structure file in order to be visible. This will provide a quick method of defining the following selection criteria:
  • current - WHERE start_date <= now() AND end_date >= now()
  • historic - WHERE end_date < now()
  • future - WHERE start_date > now()

void $object->initialiseFileDownload ($where)

$where is a string in the format of the WHERE clause in an SQL query.

This is used in a file download transaction to specify default values for the following:

• $this->download_filename - the name of the file to be downloaded.
• $this->download_mode - 'inline' or blank. With 'inline' the file can only be viewed, not saved.

The processing sequence is as follows:

• Call getData_raw() to obtain a database occurrence which matches $where.
• Call _cm_initialiseFileDownload() to perform any custom processing.

void $object->initialiseFilePicker ()

This is used in a file picker transaction to specify default values for the following:

• $this->picker_subdir - the source directory from which files will be displayed for picking.
• $this->picker_filetypes - an array of valid file types, such as gif, jpg, etc.

The _cm_initialiseFilePicker() method is called to perform any custom processing.

void $object->initialiseFileUpload ($where)

$where is a string in the format of a WHERE clause in an SQL query, as passed down by the previous screen.

This is used in a file upload transaction to specify default values for the following:

• $this->upload_subdir - the destination directory for the uploaded file.
• $this->upload_filetypes - an array of valid MIME Media Types, such as image/gif, image/jpg, etc. If any type of image is allowed then the string value image can be specified instead.
• $this->upload_maxfilesize - the maximum size allowed, in bytes, for the uploaded file.

The _cm_initialiseFileUpload() method is called to perform any custom processing.

Once the user identifies the file to be uploaded and presses the SUBMIT button control is passed to the fileUpload() method.

$rows = $object->insertMultiple ($rows)

$rows (IN) is an indexed array containing the details of each row to be inserted.
$rows (OUT) is the same array after it has been processed, but may contain changes.

This uses data which is typically created with the getInitialDataMultiple() method.

The processing sequence is as follows:

• Call _cm_pre_insertMultiple() for any custom pre-processing.
• Call insertRecord() for each occurrence.
• Call _cm_post_insertMultiple() for any custom post-processing.

$this->errors will contain any error messages.

$this->numrows will contain the count of inserted records.

$this->messages will contain a message in the format 'N records were inserted into TABLENAME'.

$rowdata = $object->insertRecord ($rowdata)

$rowdata (IN) is an associative array containing the details of a single row to be inserted.
$rowdata (OUT) is the same array after it has been processed, but may contain changes.

The steps in this operation are as follows:

• Load contents of $this->initial_values into $rowdata.
• Call _cm_getInitialData() to obtain any initial values for a new record.
• Call _cm_pre_insertRecord() to perform any custom pre-processing.
• Call _validateInsert() to validate the contents of $rowdata.
• Call _cm_commonValidation() and _cm_validateInsert() to perform any custom validation.
• If any field has the autoinsert or autoupdate option set in $this->fieldspec it will be removed from $rowdata.
• Call _dml_insertRecord() to update the database.
• Call _cm_post_insertRecord() to perform any custom post-processing.

$this->errors will contain any error messages.

$array = $object->insertOrUpdate ($array)

$array (IN) may be either an indexed array containing data for several database rows, or an associative array containing data for a single database row.
$array (OUT) is the same array after it has been processed, but may contain changes.

The steps in this operation are as follows:

• Call _cm_pre_insertOrUpdate() for any custom pre-processing.
• Call getPkeyNames () to obtain the names of the primary key field(s) for this table.
• Examine each row entry in $array and see if a record with that primary key currently exists in the database. This will then:
  • Either: call insertRecord() if the record does not exist.
  • Or: call updateRecord() if the record does exist.
• Call _cm_post_insertOrUpdate() for any custom post-processing.

$this->errors will contain any error messages.

$output = $object->outptCSV ($resource)

$resource is a database resource created by getData_serial().
$output will be a string containing all the retrieved data in CSV (comma separated values) format.

The steps in this operation are as follows:

• Call _cm_pre_output() for any custom pre-processing.
• Call fetchRow() to obtain database occurrences one at a time.
• Call formatData() to perform any data formatting.
• The first line in $output will contain the field labels.
• All remaining lines in $output will be the values from each database occurrence.
• Call _cm_post_output() for any custom post-processing.

$string = $object->outputPDF_DetailView ($resource, $structure)

$resource is a database resource created by getData_serial().
$structure is the contents of the report structure file.
$string is the PDF output if 'destination' is set to 'string'.

This method is used to produce output in PDF format (detail view). It has the following steps:

• Instantiate an instance of std.pdf.class.inc which is a subclass of TCPDF.
• Read in the contents of the report structure file.
• Call _cm_pre_output() for any custom pre-processing.
• Call $PDF->detailView() to process all records in $resource.
• Call $PDF->output() to produce the PDF output.
• Call startTransaction() to start a database transaction.
• Call _cm_post_output() for any custom post-processing.
• Call commit() or rollback() to end the database transaction.
• Send the completed PDF document to the specified destination.

$string = $object->outputPDF_ListView ($resource, $structure)

$resource is a database resource created by getData_serial().
$structure is the contents of the report structure file.
$string is the PDF output if 'destination' is set to 'string'.

This method is used to produce output in PDF format (list view). It has the following steps:

• Instantiate an instance of std.pdf.class.inc which is a subclass of TCPDF.
• Read in the contents of the report structure file.
• Call _cm_pre_output() for any custom pre-processing.
• Call $PDF->listView() to process all records in $resource.
• Call $PDF->output() to produce the PDF output.
• Call startTransaction() to start a database transaction.
• Call _cm_post_output() for any custom post-processing.
• Call commit() or rollback() to end the database transaction.
• Send the completed PDF document to the specified destination.

$where = $object->popupCall ($popupname, $where, $script_vars, $fieldarray, $settings)

$popupname identifies the popup screen which is to be processed.
$where (IN) is the WHERE clause being processed by the current screen.
$script_vars is the set of variables for the current screen that will be saved in the $_SESSION array.
$fieldarray is an associative array containing the data for the current database occurrence.
$settings is a string containing optional settings for the popup screen.
$where (OUT) is the WHERE clause that will be passed to the popup screen.

If any screen contains a popup button, and the user presses this button, this method will be called to define the parameters which are to be passed to the popup form, which will then be activated. The current form will be suspended while the popup form has control, and will be automatically reactivated as soon as the popup form terminates (refer to popupReturn()).

Although a value may be brought forward in $where it may not be applicable in the popup form, so by default the output value will be empty. This behaviour may be customised in _cm_popupCall().

Currently the default value in $settings is select_one=true which will cause the SELECT column to be displayed as a radio group so that only one entry can be selected at any one time. This can be reversed by specifying select_one=false which will then cause the SELECT column to be displayed as a series of checkboxes.

$rowdata = $object->popupReturn ($rowdata, $return_from, $selection)

$rowdata (IN) is an associative array containing the current data for a database occurrence.
$return_from is the name of the popup form which has just been processed.
$selection is a string which identifies what was selected in the popup or filepicker form.
$rowdata (OUT) will contain the input array adjusted to include whatever was selected.

If any screen contains a popup button, and the user presses this button, this method will be called after the user makes a selection in the popup form and presses the CHOOSE button to return to the current form. This is so that the selected item can be merged with the existing data for the current form.

If the selection was a filename then this name will be added to $rowdata, _cm_popupReturn() will be called in case any custom processing is required, and the function will exit.

If the selection was a database occurrence then $selection will contain the primary key (which may be a compound key) of that occurrence. The selection will be passed to _cm_popupReturn() for any custom processing before it is added to $rowdata, then passed to _cm_getInitialData(), getForeignData() and _cm_post_popupReturn().

The steps in this operation are as follows:

• _cm_popupReturn() - perform any custom processing on the returned value. If this was a file name then processing will stop here.
• _cm_getInitialData() - perform any initialisation (if mode = 'insert') using the returned value.
• getForeignData() - retrieve data from any foreign tables using the returned value.
• _cm_post_popupReturn() - perform any custom processing after the foreign data has been retrieved.

void = $object->reset ($where)

$where is a string in the format of a WHERE clause in an SQL query.

This is used when the RESET button is pressed. This button is available in those transactions where the user can modify the selection and sorting criteria to alter how the current set of data is displayed. By pressing this button the user will remove any additional selection and sorting criteria and return the transaction to its original state. This is done by performing the following:

• $this->setSqlSearch() - set $this->sql_search and $this->sql_search_origto null.
• $this->setOrderBy() - set $this->sql_orderby to $this->default_orderby.
• $this->setOrderBySeq() - set $this->sql_orderby_seq to null.
• $this->initialise($where) - perform object initialisation.

boolean = $object->rollback ()

If a database update fails this will rollback (undo) any changes made since the previous call to startTransaction(). Note that this has no effect on tables which do not support database transactions, such as MyISAM tables.

$array = $object->setFieldArray ($fieldarray)

$fieldarray is an array which can either contain a single associative array representing a single database row, or an indexed array of associative arrays representing several database rows.

This inserts the array into the specified object, replacing any array which currently exists.

$array = $object->setInstruction ($instruction)

$instruction is an array which identifies particular rows and the action to be taken on each of those rows.

This takes any instructions passed back by a child form which has just terminated, and loads them into $this->instruction for the current object so that they may be processed in a subsequent call to $this->_processInstruction().

$array = $object->setLookupData ($input)

$input is either an associative array, or a WHERE string which will be converted into an array.
$array will contain the input data, but as an associative array.

This is used to re-obtain any lookup data, such as will be used as the contents of any dropdown lists or radio groups.

The steps in this operation are as follows:

• Call _cm_changeConfig() to make any modifications to $fieldspec.
• Call _cm_getExtraData() to get any extra data, such as lookup lists.

$childData = $object->setParentData ($parentData)

$parentData is the data from the parent object.
$childData will contain the data from the child object, possibly amended by $parent data.

This is used in tasks with the pattern ADD5 or MULTI4 where both the parent (outer) and child (inner) areas are amendable. In these cases it may be useful to have any changes in the parent object made available to the child object so that they can be incorporated into the child's data.

This is a wrapper for the _cm_setParentData() method.

$where = $object->setScrollArray ($where)

$where (IN) is a string in the format of a WHERE clause in an SQL query.
$where (OUT) will contain the primary key of the first entry in the newly-constructed array.

This is used in a MULTI1 transaction pattern to create $this->scrollarray, which is an array of primary keys for records which may or may not currently exist in the database. After the array has been created using _cm_setScrollArray() the display will start at the first occurrence, and the user will be able to step through all the occurrences using the standard scrolling mechanism. An UPDATE operation will perform an INSERT if the selected occurrence does not currently exist in the database.

boolean = $object->startTransaction ()

This will call _cm_getDatabaseLock() to issue any database locks before telling the database that a new transaction has started. This will terminate with the next commit or rollback.

$rowdata = $object->unFormatData ($rowdata)

$rowdata (IN) is an associative array containing the data from a single database occurrence.
$rowdata (OUT) will contain the input data after any reformatting has been performed.

This will take any data which has been formatted for the user, such as dates and numbers, and convert them back into internal format. For example, a date such as '02 Jan 2006' will be converted into '2006-01-02'.

It will also call _cm_unformatdata() to perform any custom unformatting.

This is the opposite of formatData().

$rows = $object->updateLinkData ($rows, $postarray)

$rows (IN) is an indexed array which contains the current data for several database occurrences.
$postarray is an array which shows which occurrences in $rows were SELECTED or NOT SELECTED.
$rows (OUT) will contain the updated data for those database occurrences.

This is used in the LINK1 transaction pattern to update the link table in a many-to-many relationship. The contents of $postarray will be processed to ensure that a database occurrence exists where SELECTED=TRUE and does not exist where SELECTED=FALSE.

The steps in this operation are as follows:

• Call _cm_pre_updateLinkData ($rows, $postarray)
• for each row, one of:
  • Call insertRecord ($rowdata)
  • Call updateRecord ($rowdata)
  • Call deleteRecord ($rowdata)
• Call _cm_post_updateLinkData ($rows, $postarray)

$this->errors will contain any error messages.

$rows = $object->updateMultiple ($rows, $postarray)

$rows (IN) is an indexed array containing the data for several database occurrences.
$postarray (optional) contains any changes.
$rows (OUT) will contain the updated data for those database occurrences.

The steps in this operation are as follows:

• Merge the contents of $postarray with the contents of $rows.
• Call _cm_pre_updateMultiple() to perform any pre-processing.
• for each row: call updateRecord()
• Call _cm_post_updateMultiple() to perform any post-processing.

$this->errors will contain any error messages.

$this->messages will contain a message in the format 'N records were updated on TABLENAME'.

$this->numrows will contain a count of the records which were updated.

$rowdata = $object->updateRecord ($rowdata)

$rowdata (IN) is an associative array containing the data for a single database occurrence.
$rowdata (OUT) will contain the updated data for that database occurrence.

The steps in this operation are as follows:

• Call _cm_pre_updateRecord to perform any custom pre-processing.
• Call _validateUpdate() to validate the contents of $rowdata.
• Call _dml_readbeforeupdate() to re-read the original database record so that any changes can be identified.
• Call _cm_commonValidation to perform any custom validation.
• Call _cm_validateUpdate to perform any custom validation.
• If any field has the autoinsert or autoupdate option set in $this->fieldspec it will be removed from $rowdata.
• Call _dml_updateRecord() to update the database.
• Call _cm_post_updateRecord to perform any custom post-processing.

$this->errors will contain any error messages.

$msg = $object->updateSelection ($selection, $replace)

$selection a string in the format of a WHERE clause in an SQL query.
$replace a string in the format of a SET clause in an SQL query.
$msg will contain a message in the format 'N records were updated on TABLENAME'.

This is intended to issue an SQL query in the format:

UPDATE <tablename> SET $replace WHERE $selection

The actual processing will be performed within _cm_updateSelection() to allow customisation.

$this->numrows will contain a count of the records which were updated.

$rowdata = $object->validateDelete ($rowdata, $parent_table=null)

$rowdata (IN) is an associative array containing the data for a single database occurrence.
$parent_table is empty by default, but during cascade deletes it identifies the table from which the delete has been initiated.
$rowdata (OUT) will contain the updated data for that database occurrence.

This will check that the database record identified in $rowdata can be deleted without causing problems.

The steps in this operation are as follows:

• Call _cm_validateDelete() to perform any custom processing.
• For each entry in $this->child_relations look for associated records and take action according to the relationship type:
  • RESTRICTED - if any related records exist then an error message will be generated and the deletion will be prevented.
  • DELETE - although it is safe to delete from this child table, there may be restricted links on the children of this child, so the validateDelete() method is called recursively for each child table. This will be repeated for every lower level in the current relationship hierarchy.
  • NULLIFY - no action.

$this->errors will contain any error messages.

$postarray = $object->validateSearch ($postarray)

$postarray (IN) an associative array containing input data.
$postarray (OUT) the same array after any changes have been made.

This will the input data from a SEARCH screen to be processed before it is passed back to the previous task for use in the WHERE clause of an sql SELECT statement.

This will call _cm_validateSearch() to perform any custom processing.

Notes on Internal (private) Methods

void $PDF->detailView ($this, $resource)

$this is the database object from which this method was called.
$resource is a database resource created by getData_serial().

This method will perform the following steps:

• Repeatedly call fetchRow() to retrieve the next database record from $resource. If not EOF do the following:
  • Call formatData() to perform any formatting.
  • Write the record to the current PDF document, starting on a new page.
  • Call _cm_output_multi() to append any additional data.

void $PDF->listView ($this, $resource)

$this is the database object from which this method was called.
$resource is a database resource created by getData_serial().

This method will perform the following steps:

• Repeatedly call fetchRow() to retrieve the next database record from $resource. If not EOF do the following:
  • Call formatData() to perform any formatting.
  • Call _cm_ListView_pre_print() to see if there are any additional lines to be printed before or after the current database row.
  • Write the record to the current PDF document, starting on a new line.
• Call _cm_ListView_header() just before each page title is printed.
• At EOF call _cm_ListView_total() to print any totals.

$string $PDF->output ($name, $destination)

$name is the name of the PDF file.
$destination is the place where the document will be sent.
$string will contain the output if $destination = 'S'.

If $name is empty then the output will be sent to the browser ($destination = 'I') with the name doc.pdf.

$destination can have one of the following values:

• I - inline, to the browser.
• D - send to he browser, but force a download.
• F - save to a local file.
• S - return the document as a string.

void $this->_dml_deleteRecord ($rowdata)

$rowdata is an associative array containing the data for a single database occurrence.

This will use the contents of $rowdata and the list of primary keys in the $this->fieldspec array to build a WHERE clause, then construct and execute a query in the following format:

DELETE FROM $tablename WHERE $where

If the audit flag is turned on for this table it will also write the details out to the audit database.

$count = $this->_dml_deleteSelection ($selection)

$selection is a string in the format of a WHERE clause in an SQL query, and may identify multiple database occurrences.
$count will identify how many records were actually processed.

This will construct and execute a query in the following format:

DELETE FROM $tablename WHERE $selection

If the audit flag is turned on for this table it will also write the details out to the audit database.

$rowdata = $this->_dml_fetchRow ($resource)

$resource is a database resource created by _dml_getData_serial().
$rowdata will contain an associative array of data from the next available row, or FALSE if no more rows exist.

This is used after a call to getData_serial() to return the database records one at a time.

$rows = $this->_dml_getData ($where)

$where is a string in the format of a WHERE clause in an SQL query.
$rows will contain a two-level array of retrieved data. The first level will be indexed by row number, and each row will contain an associative array of name=value pairs.

As well as using the $where string this method will also use the following optional strings:

1. $sql_select
2. $sql_from
3. $sql_groupby
4. $sql_having
5. $sql_orderby

The first step is to construct and execute a query in the following format:

SELECT count(*) FROM $from_str $where_str $group_str $having_str

If the returned count is zero then the function immediately exits, otherwise the value is used with the $pageno and $rows_per_page variables to construct the LIMIT and OFFSET portions of the subsequent SELECT statement.

If $lock_rows is set then $lock_str will be set as follows:

• 'SH' - 'LOCK IN SHARE MODE'
• 'EX' - 'FOR UPDATE'

A complete SQL query will be constructed from the various component parts, some of which may be NULL, as follows:

$this->query = "SELECT $select_str FROM $from_str
                       $where_str
                       $group_str 
                       $having_str
                       $sort_str 
                       $limit_str 
                       $lock_str"
if (!$result = <DBMS>_query($this->query)) {
    trigger_error($this, E_USER_ERROR);
} // if     

If you wish to view the SQL statements that are constructed and issued take a look at $GLOBALS['log_sql_query'].

All resulting data will be converted into a two-level array - the first level will be indexed by row number, and each row will contain an associative array of name=value pairs. All column names will be converted into lower case. This is done using code similar to the following:

$array = array();
while ($row = DBMS_fetch_assoc($result)) {
    $array[] = array_change_key_case($row, CASE_LOWER);
} // while
return $array;

$resource = $this->_dml_getData_serial ($where)

$where is a string in the format of the WHERE clause in an SQL query.
$resource will be a database resource which can be used to access all the retrieved rows.

This is similar to _dml_getData(), but instead of returning an array of occurrences it returns a resource from which individual occurrences can be obtained using fetchRow().

$rowdata = $this->_dml_insertRecord ($rowdata)

$rowdata (IN) is an associative array containing the data for a single database occurrence which is be be inserted.
$rowdata (OUT) is the same data after it has been inserted, which may now include any auto-generated values.

This will remove any field from the array which does not exist in the table, such as the SUBMIT button which exists in the $_POST array created by the web server.

If any field has the autoinsert option set it will insert a value according to the data type:

• date - the current date.
• time - the current time.
• datetime - the current date and time.
• string - the current user_id from the logon screen.
• other - nothing.

If any field in the primary key has the auto_increment option set then ensure that is has no value in $rowdata otherwise no new value will be generated. If there is no auto_increment option then perform a lookup with the supplied primary key to see if a duplicate record already exists. If it does then generate an error message and terminate the insert.

If the table contains any candidate (unique) keys then perform a lookup on each one to check for duplicates. If any is found then generate an error message and terminate the insert.

It will then construct and execute a query in the following format:

INSERT INTO $tablename SET field1='value1', field2='value2',...

If any field in the primary key has the auto_increment option set then obtain the value which has just been generated and insert it into the output array.

If the audit flag is turned on for this table it will also write the details out to the audit database.

$rowdata = $this->_dml_ReadBeforeUpdate ($where, $reuse_previous_select)

$where is a string in the format of the WHERE clause of an SQL query.
$reuse_previous_select is boolean, taken from $this->reuse_previous_select.
$rowdata will contain a two-level array of retrieved occurrences - the first is indexed by row number, and each row will contain an associative array of name=value pairs.

If $reuse_previous_select is FALSE (the default setting) this will construct and execute a query in the following format:

SELECT * FROM $tablename WHERE $where FOR UPDATE

If $reuse_previous_select is TRUE this will construct and execute a query using the current contents of $this->sql_select, $this->sql_from, et cetera. This is for those cases where the current SELECT statement contains a field from a JOINed table, and this field must be included in the re-read so that it is available in the $originaldata arguments of the _cm_commonValidation() and _cm_validateUpdate() methods.

This read is done for the following reasons:

• To verify that the record to be updated still exists.
• To provide a set of original values which may be used for comparison with the changes.
• To provide a set of original values when writing details in the audit database.

In theory the contents of $where should identify the primary key of an existing occurrence, therefore $rowdata should contain no more than one record. If $fieldarray contains a field called 'rdcversion' (Radicore Version Number) then this will be included in the $where string. This special field (an INTEGER with AUTO-UPDATE set) will be incremented with each update, so can be used to prevent simultaneous updates of the same record. Please refer to FAQ 70 for more details.

$rowdata = $this->_dml_updateRecord ($updatearray, $originaldata)

$updatearray contains an associative array of updated details for a single database occurrence.
$originaldata contains an associative array of original details for the same database occurrence, as provided by _dml_ReadBeforeUpdate().
$rowdata will contain the data after it has been updated.

This will remove any field from the array which does not exist in the table, such as the SUBMIT button which exists in the $_POST array created by the web server.

If any field has the autoupdate option set it will insert a value according to the data type:

• date - the current date.
• time - the current time.
• datetime - the current date and time.
• string - the current user_id from the logon screen.
• other - nothing.

This will use the contents of $updatearray and the list of candidate keys in the $this->fieldspec array to perform a lookup to check for duplicates. If any is found then it will generate an error message and terminate the update.

This will use the contents of $updatearray and the list of primary keys in the $this->fieldspec array to build a WHERE clause, then construct and execute a query in the following format:

UPDATE $tablename SET field1='value1', field2='value2',... WHERE $where

$count = $this->_dml_updateSelection ($selection, $replace)

$selection is a string in the format of a WHERE clause in an SQL query, and may identify multiple database occurrences.
$replace is a string in the format of a SET clause in an sql UPDATE statement.
$count will identify the number of records which were processed.

This will construct and execute a query in the following format:

UPDATE $tablename SET $replace WHERE $selection

$errors = $this->_examineWorkflow ($input)

$input may be an associative array containing the details of a single database occurrence, or a WHERE string which will be converted into an associative array.
$errors will contain an array of error messages, and will be empty if no errors are detected.

This is performed whenever a task (user transaction) completes successfully in order to find out one of the following:

• Is the task a workitem in an existing case? If the answer is 'yes' then the following will be performed:
  • The wf_workitem record will have its status updated to 'finished'.
  • Find all wf_token records which exist on inward arcs and update their status to 'consumed'.
  • Create new wf_token records on places that exist on outward arcs.
• Does the task start a new workflow case? If the answer is 'yes' then the following will be performed:
  • A record will be added to the wf_case table using the current primary key as the value for context.
  • A record will be added to the wf_token table for the start place of this workflow.

For further details please consult An activity based Workflow Engine for PHP.

$fieldarray = $this->_getInitialValues ()

$fieldarray is an associative array of name=value pairs.

This function will look for records on the INITIAL_VALUE_USER table using the current task and user identity. If none are found it will look on the INITIAL_VALUE_ROLE table using the current task and role identity. These records provide initial values for fields within the current task.

If any are found the details are added to $fieldarray. This is loaded into $this->initial_values during the initialise() method, and how it is handled after that depends on the behaviour of the current task:

• If it uses the _cm_getInitialData() method prior to inserting a new record then the values will be added to $fieldarray. Note that any field which already has a non-empty value in $fieldarray will not be overwritten.
• If it is a LIST 1 task then the values will be added to the $where string before any data is retrieved from the database. If the is_noedit field is set then the user will not be able to modify this value.

$where = $this->_getInitialWhere ($where)

$where (IN) is a string in the form of a WHERE clause in an SQL query.
$where (OUT) is the input string after it has been amended.

This will turn the $where string into an array, merge it with the contents of $this->initial_values (without overwriting any non-null values), then convert the result back into a string.

void $this->_examineWorkflowInstance ($where)

$where is a string in the format of the WHERE clause in an SQL query, and should contain the primary key of a single database occurrence.

This is performed at the start of a task (user transaction) to find out if it is a workitem within a workflow instance. If it is it will perform the following:

• Check that the workitem has been assigned to the same role as the current user. If it has not then an error message will be generated and the current task will be terminated.
• Check that the workitem has not been assigned to a different user. If it has then an error message will be generated and the current task will be terminated.
• Update the workitem to indicate that it has been assigned to the current user.

For further details please consult An activity based Workflow Engine for PHP.

$rows = $this->_processInstruction ($rows)

$rowdata (IN) is an indexed array containing the data for a number of database occurrences.
$rowdata (OUT) is the same array after any modifications have been made.

This looks for any instructions set by the previous task and adjusts the current set of data accordingly. At present the only instructions available are:

1. The setting of the SELECT checkbox in a LIST or TREE VIEW screen.
2. The expanding of a node in a TREE VIEW screen.

For examples of how to set values please refer to $this->instruction.

$where = $this->_sqlAssembleWhere ($where, $where_array)

$where (IN) is a string in the format of the WHERE clause in an SQL query.
$where_array is the same as $where (IN) but in the form of an associative array.
$where (OUT) will be a string containing any adjustments.

This will also use the following class variables:

• $this->is_link_table
• $this->link_item
• $this->tablename
• $this->fieldspec
• $this->parent_relations
• $this->sql_select
• $this->sql_from
• $this->sql_having
• $this->sql_search
• $this->sql_search_table
• $this->sql_sort_table
• $this->default_orderby

This will look at the various components that will be used in the construction of the sql SELECT statement to see if they need adjustment, as follows:

• If this is a LINK1 transaction (i.e. if $is_link_table is TRUE) then call _sqlAssembleWhereLink().
• If no additional tables are specified in $this->sql_from then remove any field names in $where which do not belong in this table. This will eliminate problems by caused by a $where clause passed down from the previous transaction which contains field names which are invalid in the current transaction.
• If $this->sql_from is empty then call _sqlForeignJoin().
• If no additional tables are specified in $this->sql_from then remove any field names in $this->sql_search which do not belong in this table.
• The $where variable is checked to ensure that all column names are qualified with the correct table name. This will prevent conflicts if the same field names exist on other tables involved in any JOINs.
• The $where and $this->sql_search variables are checked to ensure that any column name which is contained in $this->sql_select as an alias (as in "<expression> AS alias") is moved from $where to $this->sql_having. This will prevent an invalid sql statement from being constructed.
• If this is a LINK1 transaction and $this->sql_search is not empty then replace selected=TRUE/FALSE with $fieldname IS NOT NULL/IS NULL (where $fieldname is the foreign key on the LINK table which joins to the INNER table).
• If $this->sql_search is not empty ensure that each field name is properly qualified using one of the table names in $this->sql_from to ensure that an ambiguity does not arise in the final SQL query if the same field name(s) exist on more than one table. The result is then merged with the contents of $where.
• If additional tables are specified in $this->sql_from then ensure that any unqualified field names in $this->default_orderby are qualified with the current table name. This will prevent conflicts if the same field name(s) exist on other tables involved in any JOINs.
• Merge the contents of $where and $this->sql_where.

$where = $this->_sqlAssembleWhereLink ($where, $where_array)

$where (IN) is a string in the format of the WHERE clause in an SQL query.
$where_array is the same as $where (IN) but in the form of an associative array.
$where (OUT) will be a string containing any adjustments.

In a LINK1 transaction three table names are provided:

1. OUTER - from which a single occurrence will be read using the primary key passed down from the previous transaction.
2. INNER - from which all occurrences will be read.
3. LINK - which lies between the OUTER and INNER tables.

This information will be used to construct an sql SELECT statement similar to the following:

SELECT outer.outer_id, inner.inner_id, inner_desc, 
       CASE WHEN link.inner_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM outer 
CROSS JOIN inner 
LEFT JOIN link ON (outer.outer_id=link.outer_id AND inner.inner_id=link.inner_id)
WHERE outer.outer_id='x' 
[AND link.inner_id IS NULL/IS NOT NULL]

$sql_from = $this->_sqlForeignJoin (&$sql_select, $sql_from, $parent_relations)

$sql_select (IN) is a list of field names in the same format as the SELECT clause in an sql query.
$sql_from (IN) is a list of table names in the same format as the FROM clause in an sql query.
$parent_relations is an array from $parent_relations.
$sql_select (OUT) will contain any amendments.
$sql_from (OUT) will contain any amendments.

This will use the information contained in $parent_relations to amend the contents of $sql_select and $sql_from. The processing sequence is as follows:

• If $sql_select is empty then set it to either <table>.*, or <table>.field1, <table>.field2, ... (if $this->alt_language_table is not empty).
• if $sql_from is empty set it to <tablename>.
• If $this->alt_language_table is not empty then move details to $new_relations.
• For each entry in $parent_relations call the _sqlProcessJoin() method.
• If $new_relations is not empty call the _sqlSelectAlternateLanguage() method.

For more details please refer to Using Paren