I built this class because i wanted to have a shortcut for all the times i need to insert or update rows into the database.
The main purpose of this class it to use it when you submit forms.

<?php
/**
* db.class.php
*/
class DB {
    /**
     * Inserts one item into a table
     *
     * @param string $table The name of the table
     * @param array $items Array of fields to insert (if some is not specified the default value is assumed)
     * @return int The new ID of the item
     */
    public function insertTableRow($table, $items) {
        $this->fixQuotes($items);
        $fields = $this->getTableFieldsInfo($table);
        $query = "INSERT INTO `$table` (";
        foreach ($fields as $info)
            $query .= '`'.$info->name.'`, ';
        $query = trim($query, ', ');
        $query .= ") VALUES(";
        foreach ($fields as $info) {
            if (isset($items[$info->name])) {
                if (strtoupper($items[$info->name]) == "NULL")
                    $val = "NULL, ";
                else
                    $val = "'".$items[$info->name]."', ";
            }
            else
                $val = "'".$items[$info->def]."', "; //
            $query .= $val;
        }
        $query = trim($query, ', ');
        $query .= ")";
        //print $query;
        $r = mysql_query($query);
        return mysql_insert_id();
    }
    /**
     * Updates a table
     *
     * @param string $table The name of the table
     * @param int $id The ID of the row to update
     * @param array $items Array of new values ($items['columnName']['value'])
     * @return TRUE on success or FALSE on error
     */
    public function updateTableRow($table, $id, $items) {
        $this->fixQuotes($items);
        $fields = $this->getTableFieldsInfo($table);

        $query = "UPDATE `$table` SET ";
        foreach ($fields as $info) {
            if (isset($items[$info->name])) {
                if ($items[$info->name] == 'NULL')
                    $query .= "`".$info->name."` = NULL, ";
                else
                    $query .= "`".$info->name."` = '".$items[$info->name]."', ";
            }
        }
        $query = trim($query, ', ');
        $query .= " WHERE ID='$id'";
        $r = mysql_query($query);
        //print $query;

        return $r;
    }
    //----------------------------------- PROTECTED -----------------------------------//
    /**
     * Returns an object containing all the fields info of a table
     *
     * @param strin $table The name of the table
     * @return object The fields info as an array of objects like:
     * 		blob:         $fieldInfo->blob
     *		max_length:   $fieldInfo->max_length
     *		multiple_key: $fieldInfo->multiple_key
     *		name:         $fieldInfo->name
     *		not_null:     $fieldInfo->not_null
     *		numeric:      $fieldInfo->numeric
     *		primary_key:  $fieldInfo->primary_key
     *		table:        $fieldInfo->table
     *		type:         $fieldInfo->type
     *		default:      $fieldInfo->def
     *		unique_key:   $fieldInfo->unique_key
     *		unsigned:     $fieldInfo->unsigned
     *		zerofill:     $fieldInfo->zerofill
     */
    protected function getTableFieldsInfo($table) {
        $query = "SELECT * FROM `$table`";
        $result = mysql_query($query);
        while ($meta = mysql_fetch_field($result)) {
            if ($meta)
                $fieldInfo[] = $meta;
        }
        mysql_free_result($result);
        return $fieldInfo;
    }
    /**
     * Fixes single quotes in a string (or array of strings) to store it into the DB without error
     * @param mixed $str The string or array of strings to fix (by reference)
     */
    protected function fixQuotes(&$str) {
        if (is_array($str)) {
            foreach ($str as $key=>$text)
                $str[$key] = str_replace("'", "''", $text);
        }
        else
            $str = str_replace("'", "''", $str);
    }
}
?>

Usage

Let’s see a very practical example to understand how this class can be very useful.

Assume we have a page with a form to submit contact information. First, we need a contact table with these fields:

• ID


• first_name


• middle_name


• last_name


• biography

CREATE TABLE `contact` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`middle_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`last_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`biography` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

Then, we need a file containing the form, for example form.php:

<?php
/**
* form.php
*/
include "db.class.php";
if (isset($_POST['submit'])) {
    $DB = new DB();
    $DB->insertTableRow('contact', $_POST);
}
else {
    echo '
		<form action="form.php" method="POST" target="_self">
			<p>First Name<br />
			<input type="text" name="first_name" value="" /></p>
			<p>Middle Name<br />
			<input type="text" name="middle_name" value="" /></p>
			<p>Last Name<br />
			<input type="text" name="last_name" value="" /></p>
			<p>Biography<br />
			<textarea name="biography"></textarea></p>
			<p><input type="submit" value="Submit" /></p>
		</form>
		';
}
?>

Explanation

The method DB::insertTableRow accepts 2 arguments:

1. the name of the table


2. an array of values where the keys must match exactly the name of the columns of the table, and the value can be anything

The nice thing is that:

1. you don’t have to fill the array with ALL the fields (if you don’t pass a field, the class will automatically detect the default value and store that instead)


2. the order of the array values doesn’t have to match the order of the fields in the table


3. the method DB::updateTableRow works the same way:

<?php

//Example 1:

/*
If we want to add another row, this time without a form (the form was only an example to show how easy it is to use the class with forms),
and we know only first name and last name of the person, we just call the method like this:
*/
$DB->insertTableRow ('contact', array ('first_name'=>'Robert', 'last_name'=>'De Niro'));

//---------------------//

//Example 2:
$contact['first_name'] = 'Robert';
$contact['last_name'] = 'De Niro';
$contact['address'] = 'Holliwood';

$DB->insertTableRow ('contact', $contact);

/*
NOTE:
our second example has a field 'address' that doesn't exist in our table!!
It doesn't matter, the class method will only care of those fields that have the same name of one of the columns, and ignore all the others!
*/

//-----------------------------------------------------------------//

//Example 3:
/*
If we want to update the contact table and change the first name of our contact we just need to
pass an array of 1 element where key is the name of the column we want to change and the value is the new name
*/
$DB->updateTableRow ('contact', array('first_name'=>'Peter');

?>