dbGrid Extreme - documentation

What is dbGrid Extreme?

dbGrid Extreme is my first attempt at a PHP Class! My aim was to create an object that would take an SQL Statement and turn it into a well formed and fully functional data grid.

There were many such classes on the PHP Classes site, but none that had all the functionality that I required, and none that I was able to extend easily (though this may have been my inexperience with classes).

Of course since I started this some weeks ago it has grown into a beast that I am finding hard to tame. and there are some shortfalls which I shall list here:

• The Insert/Update forms contain ALL the fields of the underlying table regardless of how many from this table are in the main SQL Grid.
• The Insert/Update forms show these fields in the order in which they are defined in the database.
• Update/Insert/Edit relies on one primary key/unique field either being passed from the calling page or defined int he table.
• These actions do not YET cater for multiple fields formin the primary or unique key.

Functionality

• Paging (optional)
• inteligent ID column handling (see above to decide if you agree with my 'inteligent' definition!)
• Navigation (optional)
• Column sorting (optional)
• Inteligent handling of 'Blob' fields
• Alternate row coloring (optional)
• # Rows per page settable by programmer
• Actions (View/Edit/Insert/Delete/Show children) can all be turned off OR directed to a different page for handling
• Form Validation controlled by programmer
• CAN BE LINKED TO A 'child' Grid to have master-detail scenarios
• rowCounter(Optional)
• Modifyable classes, so each grid can have different styles

Usage:

BASIC

the Grid can be called in one line:
$objGrid1 = new dbGrid($GridName,$Connection_object,$database,$SQL,debug(true or false));
So for example:

	$hostname 	= "localhost";
	$username 	= "root";
	$password 	= "";

	$Conn 			= mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR); 
	
	$GridName 	= 'MyGrid2';
	$database 	= "test";
	$GridSQL 	= 'SELECT newsitems.id, category.name,author.short_name,title, body, status, posted';
	$GridSQL 	.= ' FROM newsitems, category, author WHERE newsitems.category = category.id' AND newsitems.Author=Author.id';
	$objGrid1 	= new dbGrid($GridName,$Conn,$database,$GridSQL,false); // Instatiate Object
	

May display something like this:

newsitems

Sort By:

Go To Page  

Pages: 1 | 2 | 3 | 4 | 5

Refresh Page Insert Record

           Records 1 to 5 of 21      

#	Action	Name	Short Name	Title	Body	Status	Posted
1	Edit | Delete | View	General	Sarah	Plaza Plaza	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sapien.......more	1	2006-03-06
2	Edit | Delete | View	General	Red	A boring title	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sapien.......more	1	2006-03-05
3	Edit | Delete | View	Martial Arts	Jet	anotherStupid title	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sapien.......more	1	2006-03-04
4	Edit | Delete | View	General	Sarah	My mth title	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sapien.......more	1	2006-03-03
5	Edit | Delete | View	Books	Red	Running out of titles 2	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sapien.......more	1	2006-03-03

           Records 1 to 5 of 21      

ADVANCED

The preferred method to call this object is to instantiate the object, set certain parameters, and then execute it's main function (called incredibly 'execute')
as in the following code:

	$hostname 	= "localhost";
	$username 	= "root";
	$password 	= "";

	$Conn 			= mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR); 

	$GridName 	= 'MyGrid1';
	$database 	= "test";
	$GridSQL 	= 'SELECT *';
	$GridSQL 	.= ' FROM author';
	$GridSQL 	.= ' ORDER BY short_name ';
	
	$objGrid1 	= new dbGrid();						
	
	$objGrid1->setMyName($GridName); 				// Set the Name
	$objGrid1->setConn($Conn);					// Pass the connection
	$objGrid1->setDb($database);					// Pass the database name
	$objGrid1->setSQLMain($GridSQL);				// Pass the SQL Statement
	
	## Optional Parameters
	$objGrid1->setMainDbTable('author');				// Main Table to be used for Insert /Edit - DEFAULTS to first table in SQL Statement
	$objGrid1->setFieldNameList('ID, Author, Full Name');// Defaults to none	 - Column Headings
	$objGrid1->setTitle('Authors');					// Defaults to MainDbTable	-Title - Above the GridSQL	

	$objGrid1->setChildren('id=newsitems.author');			// Defaults to none	 	- For childGrids syntax: '=.'
	$objGrid1->setActionSeeChildrenText('News Items');		// Defaults to 'Display ' - this is the text display in the action column for viewing 'children'. If not set the text will be 'Display '

	$Validation 	="'short_name','#q','0',Field \'short_name\' is required'|'long_name','#q','0','Field \'long_name\' is required.'";

	$objGrid1->setValidation($Validation);				// the Validation for update/insert See here for explanation of how to use this string;
	$objGrid1->setRowsPerPage(2);					// Defaults to 20			- How many Rows per page (invalid if HasPaging is false)
	$objGrid1->setBlobNumWords(12); 				// Defaults to 25			- How many words to display whern a clumn is 'Blob' Data type	
	$objGrid1->setstrDateFormat('d-m-Y H:i');			// Defaults to 'Y-m-d'	- this affects the display of datatime  or Date data - but insert & update forms still show the YYY-MM-DD format required by mysql
									// See here for explanation of how to use dateformats(from the mysql manual);
	
	

The Above code would display something like this:

Authors

Sort By:

Go To Page  

Pages: 1 | 2

Refresh Page Insert Record

           Records 1 to 2 of 3      

#	Action	Author	Full Name
1	Edit | Delete | View | Newsitems	Jet	Jet Li
2	Edit | Delete | View | Newsitems	Red	Redfern Reid-Pitcher

           Records 1 to 2 of 3      

All Settable Properties:

The validation user the validation javascript from Yaromat
My rules state that:

• Entries must start with the name of the element - in this case it would be the name of the field
• Each entry must be separated by a '|'

So, before the above strings you would put the name of the element (quoted) and after the above strings you would put the error message to dsiplay

For example:

'USB','Portnum','2','If you check the checkbox \'USB\' then fill in the port field too.'

'Portnum','1200_1500','1','Port Number must be between 1200 and 1500'

another example:
'email_address', '#S', '2', 'Email Address Must be given and must be valid'

These 3 examples above should be put together in a string and separated with '|' giving:

$validation_str=" 'USB','Portnum','2','Please fill in the port field too.'|'Portnum','12_15','1','Port # must be 12 to 15'|'email_address', '#S', '2', 'Valid Email Address Required'";


DATE FORMATTING
format character
Description
Example returned values
a
Lowercase Ante meridiem and Post meridiem
am or pm
A
Uppercase 
Ante meridiem and Post meridiem
AM or PM
B
Swatch Internet time
000 through 999
d
Day of the 
month, 2 digits with leading zeros
01 to 31
D
A textual representation of a day, three 
letters
Mon 
through Sun
F
A full textual representation of a month, such as January or 
March
January through December
g
12-hour format of an hour without leading 
zeros
1 
through 12
G
24-hour format of an hour without leading zeros
0 through 23
h
12-hour 
format of an hour with leading zeros
01 through 12
H
24-hour format of an hour with leading zeros
00 through 23
i
Minutes 
with leading zeros
00 to 59
I (capital i)
Whether or not the date is in daylights savings 
time
1 if 
Daylight Savings Time, 0 otherwise.
j
Day of the month without leading zeros
1 to 31
l (lowercase 'L')
A full textual representation of the day of the week
Sunday through Saturday
L
Whether it's a leap year
1 if it is a leap year, 0 otherwise.
m
Numeric 
representation of a month, with leading zeros
01 through 12
M
A short textual 
representation of a month, three letters
Jan through Dec
n
Numeric representation of a month, 
without leading zeros
1 through 12
O
Difference to Greenwich time (GMT) in hours
Example: +0200
r
RFC 822 formatted date
Example: Thu, 21 Dec 2000 
16:01:07 +0200
s
Seconds, with 
leading zeros
00 through 59
S
English ordinal suffix for the day of the month, 2 
characters

           st, nd, rd or             th.  Works well with j          
t
Number of days in the given month
28 through 31
T
Timezone 
setting of this machine
Examples: EST, MDT ...
U
Seconds since the Unix Epoch (January 1 1970 
00:00:00 GMT)
See also time()
w
Numeric representation of the day of the week
0 (for Sunday) 
through 6 (for Saturday)
W
ISO-8601 week number of year, weeks starting on 
Monday (added in PHP 4.1.0)
Example: 42 (the 42nd week in the year)
Y
A full numeric representation of a year, 4 
digits
Examples: 1999 or 2003
y
A two digit representation of a year
Examples: 99 or 03
z
The day 
of the year
0 through 366
Z
Timezone offset in seconds. The offset for timezones west of 
UTC is always           negative, and for those east of UTC is always 
positive.
-
43200 through 43200
	
Unrecognized characters in the format string will be printed as-is.
	
You can prevent a recognized character in the format string from being expanded by escaping it with a preceding backslash. If the character with a backslash is already a special sequence, you may need to also escape the backslash.