Rev 18 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"
>
<html>
<head>
<title>library.php Manual</title>
<meta name="GENERATOR" content="Quanta Plus">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<h3>
Administrative Interface
</h3>
<p>
The administrative interface allows you to do a "quick and dirty" admin interface to allow users to administer their site
</p>
<p>
The basics of creating an administrative interface is to do the following:
</p>
<ul>
<LI>
Create a database definition file (see sql2admin_hash.pl documentation)
</LI>
<li>
In an html file (say, admin.html), have the following two PHP commands execute
<ul>
<li>
<em>include_once 'library.php';</em>
</li>
<LI>
<em>doAdmin();</em>
</LI>
</ul>
</li>
</ul>
<p>
A database definition file requires one global variable, <em>$DATABASE_DEFINITION</em>, and can also have four contstants defined. The constants are <em>MAX_INPUT_FIELD_DISPLAY</em>,<em>IMAGE_DIRECTORY</em>,<em>EDIT_IMAGE_HEIGHT</em>, and <em>MAX_UPLOAD_FILE_SIZE</em>, which are defined in the table below
</p>
<table border="1">
<caption>
Constants
</caption>
<thead>
<tr>
<th>
Constant
</th>
<th>
Type
</th>
<th>
Use
</th>
<th>
Example
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
MAX_INPUT_FIELD_DISPLAY
</td>
<td>
Integer
</td>
<td>
Determines the display width of text entry fields
</td>
<td>
define ( MAX_INPUT_FIELD_DISPLAY, 80 );
</td>
</tr>
<tr>
<td>
MAX_TEXTAREA_HEIGHT
</td>
<td>
Integer
</td>
<td>
Determines the number of columns for Text fields
</td>
<td>
define ( DEFAULT_TEXTAREA_HEIGHT, 5 );
</td>
</tr>
<tr>
<td>
IMAGE_DIRECTORY
</td>
<td>
Text
</td>
<td>
Relative URL to images whose links are stored in database
</td>
<td>
define ( IMAGE_DIRECTORY, '/pictures/' );
</td>
</tr>
<tr>
<td>
EDIT_IMAGE_HEIGHT
</td>
<td>
Integer
</td>
<td>
Height (in pixels) of thumbnails in edit area
</td>
<td>
define ( EDIT_IMAGE_HEIGHT, 100 );
</td>
</tr>
<tr>
<td>
MAX_UPLOAD_FILE_SIZE
</td>
<td>
Integer
</td>
<td>
Maximum size of upload file allowed
</td>
<td>
define ( MAX_UPLOAD_FILE_SIZE, 1024*1024*10 );
</td>
</tr>
<tr>
<td>
CHILD_KEY_INSERT_TAG
</td>
<td>
String
</td>
<td>
This value is used to indicate an insert from an edit of a child table. See note in "children" for a fuller explaination.
</td>
<td>
define ( CHILD_KEY_INSERT_TAG, 'new');
</td>
</tr>
<tr>
<td>
CHILD_KEY_DELIMITER
</td>
<td>
String
</td>
<td>
This value is used to separate components of INPUT names in an edit of a child table. See note in "children" for a fuller explaination.
</td>
<td>
define ( CHILD_KEY_DELIMITER, '-' );
</td>
</tr>
<tr>
<td>
CHILD_KEY_TAG
</td>
<td>
String
</td>
<td>
This value is used to indicate an update from an edit of a child table. See note in "children" for a fuller explaination.
</td>
<td>
define ( CHILD_KEY_TAG, 'child_table' );
</td>
</tr>
</tbody>
</table>
<p>
<em>$DATABASE_DEFINITION</em> is a multi-level hash and must be defined as global. Since it is multi-level, this document will work on a per-layer basis
</p>
<p>
At the top level, <em>$DATABASE_DEFINITION</em> is a hash of table definitions within the database. Each table to be directly edited must have a separat entry. Thus, if we are insterested in the tables table1, table2 and table3, our <em>$DATABASE_DEFINITION</em> definition would like like this
</p>
<pre> global $DATABASE_DEFINITION;
$DATABASE_DEFINITION = array(
'table1' => array( <span style="font-style : oblique;">table1 definition here</span>),
'table2' => array( <span style="font-style : oblique;">table2 definition here</span>),
'table3' => array( <span style="font-style : oblique;">table3 definition here</span>)
);
</pre>
<p>
Each table definition is, as noted above, an addition hash. The table definition hash consists of the following keys
</p>
<table border="1">
<caption>
Table Definition Hash Keys
</caption>
<thead>
<tr>
<th>
Key
</th>
<th>
Type
</th>
<th>
Use
</th>
<th>
Example
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
table name
</td>
<td>
String
</td>
<td>
Name of current table
</td>
<td>
'table name' => '_global'
</td>
</tr>
<tr>
<td>
display name
</td>
<td>
String
</td>
<td>
Alternate text to display for table name
</td>
<td>
'display name' => 'Global'
</td>
</tr>
<tr>
<td>
key field
</td>
<td>
Text or Array of Text
</td>
<td>
List of key fields for table
</td>
<td>
'key field' => 'description',
</td>
</tr>
<tr>
<td>
display columns
</td>
<td>
Array of Text
</td>
<td>
List of columns to be displayed when selecting record to edit
</td>
<td>
'display columns' => array('value','description')
</td>
</tr>
<tr>
<td>
display query
</td>
<td>
SQL Query Sting
</td>
<td>
Query to be executed to display list when multiple rows of a table are displayed. Basis for 'display columns' above. <strong>Must include the keyfield</strong>
</td>
<td>
'display query' => 'select value,description from _global'
</td>
</tr>
<tr>
<td>
new record indicator
</td>
<td>
array of columns
</td>
<td>
Array of columns, any one of which would indicate that a new record template had data entered into it. This should be one or more of the required fields, and is used as an indicator in function updateData when processing child tables that the single row used for adding a new entry had been updated and should be processed.
</td>
<td>
'new record indicator' => array('value')
</td>
</tr>
<tr>
<td>
field info
</td>
<td>
Hash
</td>
<td>
Hash of Column Definitions for Table
</td>
<td>
'field info' => array(<em>Field Definition List</em>)
</td>
</tr>
<tr>
<td>
complex join
</td>
<td>
Hash
</td>
<td>
Hash of one or more 'complex join' relationship definitions. See the limited definition below
</td>
<td>
'complex join' => array(<em>complex join Definitions</em>)
</td>
</tr>
</tbody>
</table>
<p>
The term <em>complex join</em> is not used in the standard way here. It is simply the way I use to describe the layout where two tables are related via an intermediate table. This intermediate table stores a key field for each table being joined, so it should be a many-to-many relationship, but I was having a bad night when I wrote this. So, until I go through and change all the code, it is called complex join
</p>
<p>
The 'field info' hash contains information about each column or field in the table. Each field is signified by its name (the key to the hash), followed by its attributes as in:
<br>
<em>'fieldname' => array('attribute' => value, 'attribute' => value)</em>
<br>
See the following table
</p>
<table border="1">
<caption>
Field Definition Attributes
</caption>
<thead>
<tr>
<th>
Attribute
</th>
<th>
Type
</th>
<th>
Use
</th>
<th>
Example
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
type
</td>
<td>
String
</td>
<td>
Type of This column. Valid Types are shown in following table
</td>
<td>
'type' => 'string'
</td>
</tr>
<tr>
<td>
display name
</td>
<td>
String
</td>
<td>
In most cases, if this exists it will be displayed on edit screens instead of the actual column name
</td>
<td>
'display name' => 'Nice Label'
</td>
</tr>
<tr>
<td>
width
</td>
<td>
Integer
</td>
<td>
Maximum Width of Text Field, or number of columns for textarea
</td>
<td>
'width' => 64
</td>
</tr>
<tr>
<td>
height
</td>
<td>
Integer
</td>
<td>
Number of rows for textarea (<em>'type'=>'text'</em>)
</td>
<td>
'height' => 64
</td>
</tr>
<tr>
<td>
required
</td>
<td>
Boolean
</td>
<td>
If non-zero (or true), form is not processed unless this field has a value unless <em>default</em> below is defined
</td>
<td>
'required' => true
</td>
</tr>
<tr>
<td>
default
</td>
<td>
String
</td>
<td>
Default value for field if is is null or empty
</td>
<td>
'default' => 'No Entry Made'
</td>
</tr>
<tr>
<td>
keyfield
</td>
<td>
Boolean
</td>
<td>
If set to non-zero or true, this is a key field
</td>
<td>
'keyfield' => true
</td>
</tr>
<tr>
<td>
height
</td>
<td>
Integer
</td>
<td>
Height of textarea box, in lines.
</td>
<td>
'height' => 5
</td>
</tr>
<tr>
<td>
filetype
</td>
<td>
String
</td>
<td>
For use in File type only, defines the file type to be a picture
</td>
<td>
'filetype' => 'picture'
</td>
</tr>
</tbody>
</table>
<table border="1">
<caption>
Valid Data Types
</caption>
<thead>
<tr>
<th>
Data Type
</th>
<th>
Database Types
</th>
<th>
Use
</th>
<th>
Notes
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
string
</td>
<td>
varchar or char
</td>
<td>
Holds up to <em>'width'</em> characters of Data
</td>
<td>
'type' => 'string'
</td>
</tr>
<tr>
<td>
text
</td>
<td>
text or blob type 1
</td>
<td>
Holds unlimited text, with line returns, etc...
</td>
<td>
</td>
</tr>
<tr>
<td>
file
</td>
<td>
blob
</td>
<td>
Stores result of a file input field into the column
</td>
<td>
</td>
</tr>
<tr>
<td>
bool
</td>
<td>
char
</td>
<td>
On data entry, stores 1 or 0. Any non-zero value assumed to be true
</td>
<td>
</td>
</tr>
<tr>
<td>
int
</td>
<td>
int
</td>
<td>
Standard Integer
</td>
<td>
</td>
</tr>
<tr>
<td>
float
</td>
<td>
decimal
</td>
<td>
Floating Point Number
</td>
<td>
</td>
</tr>
<tr>
<td>
lookup
</td>
<td>
Integer
</td>
<td>
Field is an integer which is a foreign key into a different table.
</td>
<td>
</td>
</tr>
</tbody>
</table>
<p>
Foreign Keys have their own set of attributes. A lookup field type is assumed to be a 1:1 key into a separate table. As such, the following attributes are defined only for lookup data types, and these are the only locations these attributes are found.
</p>
<p>
Foreign Keys are displayed as Select boxes, with the value being the keyfield of the foreign table and the displayed value determined by the field definition (see table)
</p>
<table border="1">
<caption>
Foreign Key Attributes
</caption>
<thead>
<tr>
<th>
Attribute
</th>
<th>
Type
</th>
<th>
Use
</th>
<th>
Example
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
table
</td>
<td>
String
</td>
<td>
Target table of join
</td>
<td>
'table' => 'category'
</td>
</tr>
<tr>
<td>
keyfield
</td>
<td>
String
</td>
<td>
Column name of keyfield in remote table
</td>
<td>
'keyfield' => 'category_id'
</td>
</tr>
<tr>
<td>
display_field
</td>
<td>
text
</td>
<td>
Column Name whose value is displayed to user for selection
</td>
<td>
'display_field' => 'description'
</td>
</tr>
</tbody>
</table>
<p>
Many To Many Relationships (name here is one-to-many) are a special attribute of a table. In this case, the user may select many items for the current record to be related to. 'complex join' definitions must have the following underlying table structure
</p>
<ul>
<LI>
Foreign Table with single Primary Key Entry of type int
</LI>
<li>
Intermediary Table must contain a single int column relating to current table
</li>
<li>
Intermediary Table must contain a single int column relating to foreign table
</li>
<li>
Primary Key for intermediary table must consist solely of the two columns above
</li>
</ul>
<p>
The <em>complex join</em> entry is itself a hash, consisting of one or more separate relationships. The Key of the hash is the display name on the editing screens.
</p>
<pre> 'complex join' => array(
'Targets' => array(<span style="font-style : oblique;">Targets Definition</span>),
'Other' => array(<span style="font-style : oblique;">Other Definition</span>)
)
</pre>
<p>
Each definition contains three attributes in their hash; <em>null field</em>, and two hashes <em>values table</em> and a <em>join table</em>. The following table shows each attribute and its sub-attributes.
</p>
<table border='1'>
<caption>
'complex join' entry Attributes
</caption>
<thead>
<tr>
<th>
Parent Attribute
</th>
<th>
Attribute
</th>
<th>
Type
</th>
<th>
Description
</th>
<th>
Notes
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
null field
</td>
<td>
</td>
<td>
String
</td>
<td>
Unknown what this function is at this time
</td>
<td>
</td>
</tr>
<tr>
<td>
values table
</td>
<td>
table name
</td>
<td>
string
</td>
<td>
name of table that holds values to be displayed
</td>
<td>
</td>
</tr>
<tr>
<td>
values table
</td>
<td>
join condition
</td>
<td>
string
</td>
<td>
Not currently used
</td>
<td>
This was originally used to define the join from the intermediary table to the target table, but is no longer implemented
</td>
</tr>
<tr>
<td>
values table
</td>
<td>
display field
</td>
<td>
string
</td>
<td>
name of field to be displayed on edit
</td>
<td>
Do Not include the table name, ie do not use tablename.columnname here
</td>
</tr>
<tr>
<td>
values table
</td>
<td>
key field
</td>
<td>
string
</td>
<td>
Name of key field which is used to join to intermediary table
</td>
<td>
Do Not include the table name, ie do not use tablename.columnname here
</td>
</tr>
<tr>
<td>
join table
</td>
<td>
table name
</td>
<td>
String
</td>
<td>
Name of table used to join current table to values table
</td>
<td>
Note: This is the intermediary Table name
</td>
</tr>
<tr>
<td>
join table
</td>
<td>
values link
</td>
<td>
String
</td>
<td>
Name of column in intermediate table (join table) that links to other table (values table)
</td>
<td>
Do Not include the table name, ie do not use tablename.columnname here
</td>
</tr>
<tr>
<td>
join table
</td>
<td>
my link
</td>
<td>
String
</td>
<td>
Name of column in intermediate table (join table) that links to this column in this table
</td>
<td>
Do Not include the table name, ie do not use tablename.columnname here
</td>
</tr>
<tr>
<td>
join table
</td>
<td>
join condition
</td>
<td>
string
</td>
<td>
Not currently used
</td>
<td>
This was originally used to define the join from the intermediary table to the target table, but is no longer implemented
</td>
</tr>
</tbody>
</table>
<h3>children</h3>
<p>children is the way to define one or more child tables. A child table is similar to a lookup table, but it is more complex in that it may have multiple display columns, may be joined to other tables, etc... However, a child table is a direct descendant of the current table, ie it will not join to another row in the current table.</p>
<p>children only has two values; the name of the table which is a child, and the column in the child table that is used for the join. If the latter is missing, it is assumed to be the same as the primary key of the current table.</p>
<p>During edit, all rows in the child table belonging to the current table are displayed in tabular form, using the 'display query' defined in that table. This can cause a more complex setup as we have multiple rows in an HTML table containing data on the same column, but each HTML <INPUT> should have a unique name in order to be processed.</p>
<p>To fix this, the library will give a unique name to each item, based on the column name, a constant (global constant CHILD_KEY_TAG), the table name from which the data is drawn, and the unique row-id (ie, a 'key field' entry) for the row being edited. These values are separated by a delimiter (global constant CHILD_KEY_DELIMITER).</p>
<p>Additionally, a "blank" row is created in these circumstances to allow the user to enter a new record if they desire. This will take the same form as the previous paragraph, except that the row-id is replaced by a special indicator (global constant CHILD_KEY_INSERT_TAG)</p>
<p>The name of the edit for an existing key is therefore calculated as:<br>
<em>fieldname</em> . CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . <em>tablename</em> . CHILD_KEY_DELIMITER . <em>primary_key_value_of_row</em> <br>
and the name for the single "blank" record is calculated as:<br>
<em>fieldname</em> . CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . <em>tablename</em> . CHILD_KEY_DELIMITER . CHILD_KEY_INSERT_TAG
</p>
<p>
This gives rise to entries on the HTML form as follows, the first being an edit of the username field of a child table <em>login</em> whose row id is 5, and the second being a blank record to add a new row.
</p>
<ul><LI> <td><input type='text' name='<em>username</em>-child_table-<em>login</em>-<em>5</em>' value='jane@example.com'></li><li>
<td><input type='text' name='<em>username</em>-child_table-<em>login</em>-<em>new</em>' value=''>
</LI></ul>
<p>The above example assumes the following definitions in yoru database definition file</p>
<pre>
define ( CHILD_KEY_INSERT_TAG, 'new');
define ( CHILD_KEY_DELIMITER, '-' );
define ( CHILD_KEY_TAG, 'child_table' );
</pre>
<p><strong>Note:</strong> You are responsible for ensuring the CHILD_KEY_DELIMITER is not found within field or table names. If you tend to use underscores in your field or table names (like I do), you can not use them as the delimiter. This needs to be a delimiter which is legitimate as the name of an HTML entity and is not found in the table name, column name or key field value</p>
<h3>Example</h3>
<p>Following example is based upon a real life application that stores knowledge Computer Asset Management Program. This program is set up to have multiple sites per client, with each site having multiple pieces of equipment (devices). Each device can have multiple attributes.</p>
<pre>
define ( MAX_INPUT_FIELD_DISPLAY, 40 ); // this is the maximum input field size
define ( IMAGE_DIRECTORY, '/pictures/' ); // relative URL where pictures are stored
define ( EDIT_IMAGE_HEIGHT, 100 ); // height for thumbnail of pictures
define ( MAX_UPLOAD_FILE_SIZE, 1024*1024*10 ); // 10 meg
define ( CHILD_KEY_INSERT_TAG, 'new');
define ( CHILD_KEY_DELIMITER, '-' );
define ( CHILD_KEY_TAG, 'child_table' );
define ( DEFAULT_TEXTAREA_HEIGHT, 5 ); // number of rows a textarea will default to
define ( DEFAULT_TABLE, 'client'); // the table that will show up in doAdmin by default
global $DATABASE_DEFINITION;
$DATABASE_DEFINITION = array(
'client' => array(
'table name' => 'client',
'key field' => 'client_id',
'display columns' => array('client_id','name'),
'display query' => 'select client_id,name from client',
'field info' => array(
'client_id' => array('keyfield' => 1 , 'required' => true , 'readonly' => true , 'type' => 'int' , 'width' => 10),
'name' => array('type' => 'string' , 'width' => 64),
'notes' => array('type' => 'text', 'width'=> 80 ),
'internal_notes' => array('type' => 'text', 'width'=> 80 ),
'added_date' => array('type' => 'date', 'default' => date('Y-m-d H:i:s')),
'removed_date' => array('type' => 'date', 'default' => 'null')
)
),
'site' => array(
'table name' => 'site',
'key field' => 'site_id',
'display columns' => array('site_id','name'),
'display query' => 'select site_id,name from site',
'field info' => array(
'site_id' => array('keyfield' => 1 , 'required' => true , 'readonly' => true , 'type' => 'int' , 'width' => 10),
'client_id' => array( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'client', // remote table name
'keyfield' => 'client_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name' // this is the column displayed to the user
),
'name' => array('type' => 'string' , 'width' => 64),
'notes' => array('type' => 'text', 'width'=> 80 ),
'added_date' => array('type' => 'date', 'default' => date('Y-m-d H:i:s') ),
'removed_date' => array('type' => 'date', 'default' => 'null' )
)
),
'device' => array(
'table name' => 'device',
'key field' => 'device_id',
'display columns' => array('name'),
'display query' => 'select device_id,name from device',
'field info' => array(
'device_id' => array('keyfield' => 1 , 'required' => true , 'readonly' => true , 'type' => 'int' , 'width' => 10),
'name' => array('required' => true , 'default' => '' , 'type' => 'string' , 'width' => 255),
'part_of' => array( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'device', // remote table name
'keyfield' => 'device_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name', // this is the column displayed to the user
'null_ok' => true
),
'device_type_id' => array( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'device_type', // remote table name
'keyfield' => 'device_type_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name' // this is the column displayed to the user
),
'site_id' => array( 'type' => 'lookup', // this is the field type, and must be ints
'query' => "select site_id,concat(client.name, ' - ', site.name) from site join client on site.client_id = client.client_id order by client.name,site.name",
'table' => 'site', // remote table name
'keyfield' => 'site_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name' // this is the column displayed to the user
),
'notes' => array('type' => 'text', 'width'=> 80 ),
'name' => array('required' => true , 'default' => '' , 'type' => 'string' , 'width' => 64),
'added_date' => array('type' => 'date', 'default' => date('Y-m-d H:i:s')),
'removed_date' => array('type' => 'date', 'default' => 'null')
),
'child tables' => array (
'device_attrib' => array( 'table name' => 'device_attrib' )
)
),
'device_attrib' => array(
'display name' => 'Device Attributes',
'table name' => 'device_attrib',
'key field' => 'device_attrib_id',
'new record indicator' => array('value'),
'display columns' => array('name'),
'display query' => 'select device_attrib_id,attrib.name, device_attrib.value from device_attrib join attrib using (attrib_id)',
'field info' => array(
'device_attrib_id' => array('keyfield' => 1 ,
'required' => true ,
'readonly' => true ,
'type' => 'int' ,
'width' => 10,
'display name' => 'ID'
),
'device_id' => array ( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'device', // remote table name
'keyfield' => 'device_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name', // this is the column displayed to the user
'null_ok' => false,
'display name' => 'Device'),
'attrib_id' => array( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'attrib', // remote table name
'keyfield' => 'attrib_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name', // this is the column displayed to the user
'null_ok' => false,
'display name' => 'Attribute'
),
'value' => array('type' => 'text', 'required' => true, 'width'=> 40, 'height' => 1, 'display name' => 'Value' ),
'added_date' => array('type' => 'date', 'default' => date('Y-m-d H:i:s'), 'display name' => 'Date Added'),
'removed_date' => array('type' => 'date', 'default' => 'null', 'display name' => 'Date Removed')
)
)
);
</pre>
<p>Modification required for update to new naming convention. In old source code, do the following search and replaces:</p>
<ul><LI>s/foreignkey/lookup/</li>
<li>s/one to many/complex join/
</LI></ul>
</body>
</html>