TIME SAVING DATABASE FUNCTIONS

this post is from 

http://www.evoluted.net/thinktank/web-development/time-saving-database-functions

We’ve all been there, making an awesome form nicely laid out on the page and arranged pixel perfect to fit all the fields required; name, address, email, telephone etc… everything is going great until you come to the PHP.

Suddenly you realise that you have to process and add every item from this massive beautiful form in to the database! D’OH!

Normally you would have to create a SQL query similiar to the one below (assuming the variables had been sanitised first):

mysql_query(
"INSERT INTO my_table(first_name, last_name, email, address1, address2, address3, postcode, tel, mobile, website, contact_method, subject, message, how_you_found_us, time)
VALUES('$first_name', '$last_name', '$email', '$address1', '$address2', '$address3', '$postcode', '$tel', '$mobile', '$website', '$contact_method', '$subject', '$message', '$how_you_found_us', ".time().")
");

Thankfully there is an easier way, with some short hand database functions. Imagine being able to create the above query with just:

dbRowInsert('my_table', $form_data);

How much simpler is that? How about when updating a record or deleting one? How much easier would it be to have a simliar set of functions like:

dbRowUpdate('my_table', $form_data, "WHERE id = '$id'");
dbRowDelete('my_table', "WHERE id = '$id'");

HOW IT’S DONE

The key for these functions to work is the format for the $form_data variable. The variable is an array and should be arranged so the key of each element in the array is the column name for the data in the value part of the array. For the table described in the INSERT statement above, our array would look something similiar to this:

$form_data = array(
    'first_name' => $first_name,
    'last_name' => $last_name,
    'email' => $email,
    'address1' => $address1,
    'address2' => $address2,
    'address3' => $address3,
    'postcode' => $postcode,
    'tel' => $tel,
    'mobile' => $mobile,
    'website' => $website,
    'contact_method' => $contact_method,
    'subject' => $subject,
    'message' => $message,
    'how_you_found_us' => $how_you_found_us,
    'time' => time()
);

With the array formatted in this way it allows us to use the array_keys() php function to retrieve the field columns and then implode the array itself to build the centre part of the ‘insert’ query so we can then prepend and append the relevant data to construct the full query. Here is how we do it:

function dbRowInsert($table_name, $form_data)
{
    // retrieve the keys of the array (column titles)
    $fields = array_keys($form_data);

    // build the query
    $sql = "INSERT INTO ".$table_name."
    (`".implode('`,`', $fields)."`)
    VALUES('".implode("','", $form_data)."')";

    // run and return the query result resource
    return mysql_query($sql);
}

Simples! While this is totally functional, one thing to remember when doing this to make sure any data passed in to the function (be it table name or the actual form data) is passed through a sanitising function first. At the very minimum, data should be passed through mysql_real_escape_string() before going into a query, this helps to prevent SQL injections.

FURTHER FUNCTIONS

Now we have the insert query, what about the other types? Here is how we would create a delete function:

// the where clause is left optional incase the user wants to delete every row!
function dbRowDelete($table_name, $where_clause='')
{
    // check for optional where clause
    $whereSQL = '';
    if(!empty($where_clause))
    {
        // check to see if the 'where' keyword exists
        if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE')
        {
            // not found, add keyword
            $whereSQL = " WHERE ".$where_clause;
        } else
        {
            $whereSQL = " ".trim($where_clause);
        }
    }
    // build the query
    $sql = "DELETE FROM ".$table_name.$whereSQL;

    // run and return the query result resource
    return mysql_query($sql);
}

Now for an update function:

// again where clause is left optional
function dbRowUpdate($table_name, $form_data, $where_clause='')
{
    // check for optional where clause
    $whereSQL = '';
    if(!empty($where_clause))
    {
        // check to see if the 'where' keyword exists
        if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE')
        {
            // not found, add key word
            $whereSQL = " WHERE ".$where_clause;
        } else
        {
            $whereSQL = " ".trim($where_clause);
        }
    }
    // start the actual SQL statement
    $sql = "UPDATE ".$table_name." SET ";

    // loop and build the column /
    $sets = array();
    foreach($form_data as $column => $value)
    {
         $sets[] = "`".$column."` = '".$value."'";
    }
    $sql .= implode(', ', $sets);

    // append the where statement
    $sql .= $whereSQL;

    // run and return the query result
    return mysql_query($sql);
}