Simplifying MySQL statements using PHP

MySQL statements can be complicated, and writing them can sometimes be quite tedious. Today I want to talk about a simple technique to help improve readability and cut down modifications for MySQL statements in PHP, such as, INSERT and UPDATE. This method requires some knowledge of PHP and MySQL. We will be using an array of data and PHP’s implode function to help cut down on the need to write out the entire statement. 

Implode is an awesome function. It allows us to transform an array into a string and separate each value by a specific divider.

Ok, lets begin!

First thing is to instantiate all of the variables into an array. Ensure that all of the keys match the column names of the database table.  

Example:

// An object could be used as well, instead of an array.

$customer = array(
    "id" => "100",
    "first_name" => "Gene",
    "last_name" => "Vakhroushev",
    "employer" => "Eyesore Inc"
);

INSERT – Here we will construct an insert statement using the array we previously formatted.

    $sql  = "INSERT INTO customers";
    // use (array)$customer, if $customer is an object.
    $sql .= " (`".implode("`, `", array_keys($customer))."`)";
    $sql .= " VALUES ('".implode("', '", $customer)."') ";

Source: http://php.net/manual/en/function.implode.php

UPDATE – Here we will construct an update statement in the same way. This is not quite as clean as the insert, since we need to add in the WHERE statement.

$sqlArray = array();
$sql = "UPDATE customers SET";
foreach($customer as $column => $value){
   $sqlArray[] = " '$column' = '$value' ";
}
$sql .= implode(', ', $sqlArray);
$sql .= "WHERE id = '{$customer['id']}'";

This code eliminates three things:

1. The need to modify the insert statement.
2. The need to check if the value and columns are in the same sequence.
3. The excess lines for the insert statement.

Thank you for reading, and have a great day.