A Flexible Method of Storing Control Data

By Tony Marston
2006-08-29


From screen to database
After the user has changed any values he presses the "submit" button to send those changes to the server for processing. Everyone knows that user input should never be trusted, and should be "cleansed" or "filtered" before being written to the database, and this common task can be performed automatically by the framework using the information contained with the modified structure. This will ensure that:

• Any field marked as "required" is not empty.
  
• All string fields contain strings which do not exceed their maximum size.
  
• All numeric fields contain numbers which do not exceed their maximum size, or fall below their minimum value.
  
• All boolean fields contain a value which is either TRUE or FALSE.

After this validation has been performed the data can be written to the database using the following code:

    function _cm_updateSelection($fieldarray, $replace)
    // update multiple rows in a single operation.
    {
        $errors = array();
        
        // set $fieldspec to the database view
        $this->fieldspec = $this->getFieldSpec_original();

        // get array of fieldnames in the primary key
        $pkeynames = $this->getPkeyNames();

        // now turn the array of columns into an array of rows
        $rowdata = array();
        $rownum = 0;
        foreach ($updatearray as $fieldname => $fieldvalue) {
            $rowdata[$rownum]['record_id']   = 'system';
            $rowdata[$rownum]['field_id']    = $fieldname;
            $rowdata[$rownum]['field_value'] = $fieldvalue;
            // construct 'where' clause from primary key
            $where = array2where($rowdata[$rownum], $pkeynames);

            // find out if this record currently exists or not
            $count = $this->getCount($where);
            if ($count == 0) {
                // record does not exist, so create it
                $rowdata[$rownum] = $this->insertRecord($rowdata[$rownum]);
            } else {
                // record already exists, so update it
                $rowdata[$rownum] = $this->updateRecord($rowdata[$rownum]);
            } // if

            if (!empty($this->errors)) {
                // ignore 'name' and extract 'value' from $this->errors
                // as 'name' may not be the same as $fieldname
                $errors[$fieldname] = array_shift($this->errors);
            } // if
            $rownum = $rownum + 1;
        } // foreach

        $this->errors = $errors;

        return $fieldarray;

    } // _cm_updateSelection

The getFieldSpec_original() method is used to replace the modified structure with the original structure. It then steps through the
input array and extracts each field which it then treats as a separate database
row. This row is then inserted or updated, as appropriate.

Tutorial Pages:
» Introduction
» A flexible approach
» Implementation
» From database to screen
» From screen to database
» Summary

Related Tutorials: » Installing MySQL on Windows » Implementing High Availability in MySQL » Stored Procedures are EVIL » MySQL Database Handling in PHP » Exploring MySQL CURDATE and NOW. The Same But Different. » Creating a PostgreSQL and MySQL driver