Rev 1 | Blame | Last modification | View Log | 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>
</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>
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>Example</h3>
<p>Following example is based upon a real life application that stores knowledge base articles. Articles can be associated with one or more categories, and the categories table is hierachial in that each entry has a "parent node" column which links it to another entry in the same table</p>
<p>Note the foreign key definition in table categories, and the many-to-many relationship in articles, going into categories.</p>
<pre>
define ( MAX_INPUT_FIELD_DISPLAY, 80 ); // 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 pictuers
define ( MAX_UPLOAD_FILE_SIZE, 1024*1024*10 ); // 10 meg
global $DATABASE_DEFINITION;
$DATABASE_DEFINITION = array(
'article' => array(
'table name' => 'article',
'key field' => 'article_id',
'display columns' => array('article_id','title'),
'display query' => 'select article_id,title from article',
'field info' => array(
'article_id' => array('keyfield' => 1 , 'required' => true , 'readonly' => true , 'type' => 'int' , 'width' => 10),
'summary' => array('type' => 'string' , 'width' => 255),
'content' => array('type' => 'text', 'width'=> 80 ),
'title' => array('type' => 'string' , 'width' => 255)
),
'complex join' => array(
'professionals' => array(
'null field' => 'article_category.article_id',
'values table' => array( // table that holds the values
'table name' => 'category',
'join condition' => 'article_category.category_id = category.category_id',
'display field' => 'name',
'key field' => 'category_id'
),
'join table' => array( // table that 'joins' this table to values table
'table name' => 'article_category',
'values link' => 'category_id',
'my link' => 'article_id',
'join condition' => 'article_category.article_id = article.article_id' )
) // professionals
) // complex join
),
'article_category' => array(
'table name' => 'article_category',
'key field' => array('article_id','category_id'),
'display columns' => array('category_id','article_id'),
'display query' => 'select category_id,article_id from article_category',
'field info' => array(
'category_id' => array('keyfield' => 1 , 'required' => true , 'default' => '0' , 'type' => 'int' , 'width' => 10),
'article_id' => array('keyfield' => 1 , 'required' => true , 'default' => '0' , 'type' => 'int' , 'width' => 10)
)
),
'category' => array(
'table name' => 'category',
'key field' => 'category_id',
'display columns' => array('name'),
'display query' => 'select category_id,name from category',
'field info' => array(
'parent_category' => array( 'type' => 'lookup', // this is the field type, and must be ints
'table' => 'category', // remote table name
'keyfield' => 'category_id', // keyfield in remote table, ie category.category_id
'display_field' => 'name' // this is the column displayed to the user
),
'category_id' => array('keyfield' => 1 , 'required' => true , 'readonly' => true , 'type' => 'int' , 'width' => 10),
'name' => array('required' => true , 'default' => '' , 'type' => 'string' , 'width' => 64)
)
)
);
</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>