AusImage Realm Code Projects

In hacking a database interface, I had trouble getting the standard interfaces to work for me. So I created the following functions to aid me in doing database calls using the PEAR DB interface. I designed a single interface to INSERT, UPDATE, or DELETE rows in a table.

Quote Field Handler

// Quote string value to include in query.
function quoted($value) {
        return (QT.$value.QT);
}

Fetch Data Handler

// With a SELECT query return a data table array.
function db_getarray($sql) {
        global $db;
        $db->setFetchMode(DB_FETCHMODE_ASSOC); $data =& $db->getAll($sql);
        if (PEAR::isError($data)) {
                trigger_error ($data->getMessage(),E_USER_ERROR);
                return FALSE;
        } else {
                // Echo SQL statment if DBSQL is defined
                if (DBSQL) echo "<div><b>DBSQL: </b>".$sql."</div>";

                return $data;
        } }

Modify Data Handler

// Execute a query passing to parsing function.
function db_exec($func,$table,$fieldset=array(),$whereset=array()){
        global $db;
        // Verify function exists, then call within query execution.
        if (in_array($func,array('insert','update','delete'))) {
                $func = "db_create".$func;
                $data =& $db->query($func($table,$fieldset,$whereset));
                // Check for SQL errors
                if (PEAR::isError($data)) {
                        trigger_error ($data->getMessage(),E_USER_WARNING);
                        return FALSE;
                } else {
                        return TRUE;
                } } }

INSERT Query Processor

// Create INSERT statement from passed array.
function db_createinsert($table,$fieldset,$whereset){
        // Iterate array to create field clause.
        foreach($fieldset as $field=>$value){
                $sqlfields .= quoted($field).", ";
                $sqlvalues .= "'".addslashes(trim($value))."', ";
        }
        // Fill in SQL statement
        $sql = sprintf("INSERT INTO %1\$s (%2\$s) VALUES (%3\$s);",
        quoted($table),rtrim($sqlfields,', '),rtrim($sqlvalues,', '));
        // Echo SQL statment if DBSQL is defined
        if (DBSQL) echo "<div><b>DBSQL: </b>".$sql."</div>";
        return $sql;
}

Update Query Processor

// Create UPDATE statement from passed array.
function db_createupdate($table,$fieldset,$whereset){
        // Iterate array to create field clause.
        foreach($fieldset as $field=>$value){
                $sqlset .= quoted($field)." = '".addslashes(trim($value))."', ";
        }
        // Iterate array to create WHERE clause.
        foreach($whereset as $field=>$value){
                $sqlwhere .= quoted($field)." = '".addslashes($value)."', ";
        }
        // Fill in SQL statement
        $sql = sprintf("UPDATE %1\$s SET %2\$s WHERE %3\$s LIMIT 1;",
        quoted($table),rtrim($sqlset,', '),rtrim($sqlwhere,', '));
        // Echo SQL statment if DBSQL is defined
        if (DBSQL) echo "<div><b>DBSQL: </b>".$sql."</div>";

        return $sql;
}

DELETE Query Processor

// Create DELETE statement from passed array.
function db_createdelete($table, $fieldset, $whereset){
        // Iterate array to create WHERE clause. //
        foreach($whereset as $field=>$value){
                $sqlwhere .= quoted($field)." = '".addslashes($value)."', ";
        }
        // Fill in SQL statement
        $sql = sprintf("DELETE FROM %1\$s WHERE %2\$s LIMIT 1;",
        quoted($table),rtrim($sqlwhere,', '));
        // Echo SQL statment if DBSQL is defined
        if (DBSQL) echo "<div><b>DBSQL: </b>".$sql."</div>";

        return $sql;
}

Contact with Questions or Feedback: Send email to tjaustinbardo AT gmail DOT com