Time Saving Database Functions

10 March, 2011

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:


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:
[php]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); }

The above are the three main functions that we would use and despite the initial development time in creating these functions the time saved later down the line far out ways the time spent creating functions like these!

14 Comments

Adam
16th October 2017 at 9:16am

Good post, I have used similar before, my only comment would bet that in your update, I would favour to keep the $where_clause as an array also to keep the parameters similar and also cut out the string check for the WHERE keyword.

Could be achieved with:

$i = 1;
foreach($where_clause as $key => $value) {
$whereSQL .= "`$key`= '$value'";
if ($i

Post reply
James
16th October 2017 at 9:16am

Great post!

Tell me ... is there any simplified code to represent a MySQL table search facility, via a Php form search box, using a specific13 digit number (unique such as a social security number) as the keyword?

The result should display something like:

Peter | Sharp| Male | 6120458745120 | prd@tidcom.com | 555 33253 | Denver

Post reply
Lineshjose
16th October 2017 at 9:16am

This is Great! :) .

Post reply
Bg
16th October 2017 at 9:16am

Great Post!!!! Saved me from my higher officials!!!! Thanks Mike

Post reply
Lookbook
16th October 2017 at 9:16am

Thanks, I use these on my polish fashion website. You are have good tips!

Post reply
kashif
16th October 2017 at 9:16am

Nice post. like it. very useful....:)

Post reply
Anil
16th October 2017 at 9:16am

Nice Post Mike

Post reply
bismark
16th October 2017 at 9:16am

that's cool, thanks this is better than the one I have.

Post reply
cisci
16th October 2017 at 9:16am

I am new to using databases. I love the function so far and yes it has made things easy. but now to take information stored how do I use the function to loop through the data and display it tables?

Post reply
Nowsath
16th October 2017 at 9:16am

Very Nice. These are all really time saving functions.......

Post reply
Mudasar wahla
16th October 2017 at 9:16am

Very Very Excelent Job......Grate

Post reply
Rohatash Rawat
16th October 2017 at 9:16am

Very Very Much Thanks For this , This is Very Usefull And helpfull Artical , Again Thanks..........

Post reply
mk
16th October 2017 at 9:16am

How to do error handling in this

Post reply
wase muhe
16th October 2017 at 9:16am

i have the following code its connection to database is correct but it cant update me the account is there any problem in the code please help

function updateAccount($username, $password, $id){
$sql="UPDATE users SET username='".$username."' AND password='".$password."' WHERE uid=".$id." ";

$query=$this->link->query($sql);
//return $this->link->error;

$count=$this->link->affected_rows;

return $count;

}

Post reply

Leave a comment

Replying to: - Cancel