Using PHP Objects to Access Your Database Tables (Part 2)
By Tony Marston2005-04-07
Using the Data Dictionary/Repository
|
|
Having this information available is just the start. What you really need is some code to make it work for you.
Data Validation
Having a list of all the characteristics of each field means that after obtaining data from the user and before sending it to the database it can be validated according to a standard set of rules. There needs to be two separate ways of calling the validation routine - one for an insert and another for an update.
When performing an insert it is necessary to validate every field on the table even though it may not have been supplied in the input array. The $fieldspec array can be used to identify the list of fields.
function std_fieldValidation_insert ($fieldarray)When performing an update it is only necessary to validate those fields which are supplied in the input array.
// Validate contents of $fieldarray against $fieldspecs.
// Errors are returned in $errors array.
// NOTE: for INSERT all required fields contained in $fieldspec must be present.
{
$this->errors = array();
// this is a copy of $fieldarray that will be passed to the database
$this->data_unformatted = array();
// step through each fieldspec entry and compare with input data
foreach ($this->fieldspec as $field => $spec) {
if (isset($fieldarray[$field])) {
$value = $fieldarray[$field];
} else {
$value = NULL;
} // if
$value = $this->std_fieldvalidation($field, $value, $spec);
// transfer to array which will be passed to the database
if (strlen($value) > 0) {
$this->data_unformatted[$field] = $value;
} // if
} // foreach
return $fieldarray;
} // std_fieldvalidation_insert
function std_fieldValidation_update ($fieldarray)Here is the code that will validate the data one field at a time:
// validate contents of $fieldarray against $fieldspec.
// errors are returned in $errors array.
// NOTE: for UPDATE only a subset of fields may be supplied.
{
$this->errors = array();
// this is a copy of $fieldarray that will be passed to the database
$this->data_unformatted = array();
// step through input data and compare with fieldspec
foreach ($fieldarray as $field => $value) {
// get specifications for this field
$spec = $this->fieldspec[$field];
$value = $this->std_fieldvalidation($field, $value, $spec);
// transfer to array which will be passed to the database
// (allow null values as field may have been cleared)
if (strlen($value) > 0) {
$this->data_unformatted[$field] = $value;
} else {
$this->data_unformatted[$field] = NULL;
} // if
} // foreach
return $fieldarray;
} // std_fieldValidation_update
function std_fieldvalidation ($fieldname, $fieldvalue, $fieldspec)My integer validation function can cope with with field sizes from int1 all the way up to int8, both signed and unsigned. It checks the input value against maximum and minimum values which are fixed depending on the field size, but this range can be reduced by additional keywords in the $fieldspec array.
// standard function for validating database fields
{
global $dateobj;
// trim any leading or trailing spaces
$fieldvalue = trim($fieldvalue);
if ($fieldspec['type'] == 'enum') {
// get enum array for this field
$enum = $this->getValRep($fieldname);
// replace index number with text value
$fieldvalue = $enum[$fieldvalue];
} // if
if ($fieldspec['type'] == 'boolean') {
// result from radiogroup may be varied, so convert to TRUE or FALSE
// (where actual values are defined within $fieldspec)
if (is_True($fieldvalue)) {
$fieldvalue = $fieldspec['true'];
} else {
$fieldvalue = $fieldspec['false'];
} // if
} // if
if (strlen($fieldvalue) == 0) {
// field is empty - is it allowed to be?
if (isset($fieldspec['required'])) {
$this->errors[$fieldname] = "$fieldname cannot be blank";
} // if
if ($fieldspec['type'] == 'date' AND isset($fieldspec['infinityisnull'])) {
$fieldvalue = '9999-12-31';
} // if
} else {
// field is not empty - check field size
if (isset($fieldspec['size'])) {
$size = (int)$fieldspec['size'];
if (strlen($fieldvalue) > $size) {
$this->errors[$fieldname] = "$fieldname cannot be > $size characters";
} // if
} // if
if (isset($fieldspec['uppercase'])) {
// value in this field must be uppercase
$fieldvalue = strtoupper($fieldvalue);
} // if
if (isset($fieldspec['lowercase'])) {
// value in this field must be lowercase
$fieldvalue = strtolower($fieldvalue);
} // if
if ($fieldspec['type'] == 'string' OR $fieldspec['type'] == 'multiline') {
// escape any suspect characters in string fields
$fieldvalue = addslashes($fieldvalue);
} // if
if (isset($fieldspec['password'])) {
// passwords must have a 'hash' specification
if (isset($fieldspec['hash'])) {
switch($fieldspec['hash']){
case 'md5':
$fieldvalue = md5($fieldvalue);
break;
case 'sha1':
$fieldvalue = sha1($fieldvalue);
break;
case 'custom':
break;
default:
$this->errors[$fieldname] = "$fieldname: specification for 'hash' is invalid";
} // switch
} else {
$this->errors[$fieldname] = "$fieldname: specification for 'hash' is missing";
} // if
} // if
if ($fieldspec['type'] == 'date') {
// value must be a date
if (!$internaldate = $dateobj->getInternalDate($fieldvalue)) {
$this->errors[$fieldname] = "$fieldname: " .$dateobj->getErrors();
} else {
// set date to internal format
$fieldvalue = $internaldate;
} // if
} // if
// perform validation if field type = integer
$fieldvalue = $this->std_validateInteger($fieldname, $fieldvalue, $fieldspec);
} // if
return $fieldvalue;
} // std_fieldvalidation
function std_validateInteger ($field, $value, $spec)You will notice that any error messages are added to another class variable called $this->errors. This is an associative array where the fieldname is the key. This array is retrieved by the calling script so that it can associate each error with the relevant field when the HTML output is produced.
// if $spec identifies $field as an integer then check that $value is within range.
{
$pattern = '(int1|tinyint|int2|smallint|int3|mediumint|int4|integer|int8|bigint|int)';
if (preg_match($pattern, $spec['type'], $match)) {
// test that input contains a valid value for an integer field
$integer = (int)$value;
if ((string)$value <> (string)$integer) {
$this->errors[$field] = "Value is not an integer";
return $value;
} // if
// set min/max values depending of size of field
switch ($match[0]){
case 'int1':
case 'tinyint':
$minvalue = -128;
$maxvalue = 127;
break;
case 'int2':
case 'smallint':
$minvalue = -32768;
$maxvalue = 32767;
break;
case 'int3';
case 'mediumint':
$minvalue = -8388608;
$maxvalue = 8388607;
break;
case 'int':
case 'int4':
case 'integer':
$minvalue = -2147483648;
$maxvalue = 2147483647;
break;
case 'int8':
case 'bigint':
$minvalue = -9223372036854775808;
$maxvalue = 9223372036854775807;
break;
default:
$this->errors[$field] = "Unknown integer type ($match)";
return $value;
} // switch
// adjust min/max values if integer is unsigned
if ($spec['unsigned']) {
$minvalue = 0;
$maxvalue = ($maxvalue * 2) +1;
} // if
if (isset($spec['minvalue'])) {
// override with value provided in $fieldspec
$minvalue = (int)$spec['minvalue'];
} // if
if ($integer < $minvalue) {
$this->errors[$field] = "Value is below minimum value ($minvalue)";
} // if
if (isset($spec['maxvalue'])) {
// override with value provided in $fieldspec
$maxvalue = (int)$spec['maxvalue'];
} // if
if ($integer > $maxvalue) {
$this->errors[$field] = "Value is above maximum value ($maxvalue)";
} // if
if (isset($spec['zerofill'])) {
while (strlen($value) < $spec['size']){
$value = '0' .$value;
} // while
} // if
} // if
return $value;
} // std_validateInteger
Tutorial Pages:
» Intended Audience
» Extending the MySQL SELECT statement
» Adding the features of a Data Dictionary/Repository
» Using the Data Dictionary/Repository
» Changing Candidate Keys
» Deleting Rows
» Custom Processing
» Virtual Database Tables
» Summary
|
