Dynamically Insert and Update Values In a MySQL Database Using OOP

Stop writing insert and update SQL statements and cut the time you spend writing simple SQL in half while focusing on the more complicated things. Leave it up to OOP to help you out. We will make a class that goes out and looks for the values for us and builds a SQL statement on the fly. All we have to do is make sure the column names in the database correspond with the field names in the HTML form. Believe me when I say it saves TONS of time. I never write applications that don’t use it.

Time Is of the Essence

So you have a new project. It requires simple add, edit, and delete actions. Simple enough, but think of all the time you spend writing simple, time-consuming insert and update SQL statements, and praying to God that you didn’t mess up the column sequence, forget a comma, or make some other silly type-o that you spend precious coding time looking for and correcting.

Think of the following: in a 25 or 50 field form, you have 50 or 100 pieces of data to enter into the SQL statement:

[code]
mysql_query("INSERT INTO myTBL (col1,col2,col3,…col30,col31) VALUES ('".$val1."', '".$val2."', '".$val3."', …'".$is_this_val30."', '".$please_be_val31."'");
[/code]

It’s confusing, and it makes your code look and feel like crap. It gets even worse when you have to add or delete a field, then you have to go through the SQL again and find that one column amidst the sea of endless columns and then go find that darned variable in that myriad of weird names you made up. Argh! I can hear your frustrations now. Well there is a solution to our long-SQL-statement-woes. We will make a class that goes out and looks for the values for us and builds a SQL statement on the fly. All we have to do is make sure the column names in the database correspond with the field names in the HTML form. Believe me when I say it saves TONS of time. I never write applications that don’t use it.

Enough chit-chat, let’s start coding.

{mospagebreak title=Essential Connection}

This article assumes you have basic knowledge of what objects are. If you don’t know what objects are, you might want to check out icarus’s article entitled Back To Class. Don’t worry if you are not an expert at OOP yet since the OOP used in this article is quite basic.

So let’s create our class and add two methods to it, a method to connect to a database, and one to disconnect from a database.

[code]
class MyDatabase
{
 // The var that stores the last
 // used SQL statement
 var $SQLStatement = ""; 

 

 // The var that stores the error
 // (if any)
 var $Error = "";

 

 function MyDatabase()
 {
  // Config for the database
  // connection
  $this->DBUser = "tmp_usr";
  $this->DBPass = "super#secret@pass";
  $this->DBName = "c_test";
  $this->DBHost = "localhost";
 }

 

 function Connect()
 {
  //Connect to a mysql database
  $this->db = mysql_connect($this->DBHost,
  $this->DBUser, $this->DBPass) or
  die("MYSQL ERROR: ".mysql_error());
  // Select the database
  mysql_select_db($this->DBName,
  $this->db) or die("MYSQL ERROR:
  ".mysql_error());
 }

 

 // Disconnect from the MYSQL database
 function Disconnect()
 {
  mysql_close($this->db) or die("MYSQL
  ERROR: ".mysql_error());
 }
}
[/code]

The class is named MyDatabase simply because this is what I named it about two years ago. It’s a corny name that stuck. I can think of many variables, files, and “other” that got irrationally named something ridiculous but still remain the same name today. Anyway, what I was trying to get at was you can name it whatever fits your fancy.

[code]
var $SQLStatement = "";
var $Error = "";
[/code]

$SQLStatement - Stores the SQL statement that will be dynamically created later.

$Error - Will store an error message should any problems arise.

[code]
function MyDatabase()
{
 $this->DBUser = "tmp_usr";
 $this->DBPass = "super#secret@pass";
 $this->DBName = "c_test";
 $this->DBHost = "localhost";
}
[/code]

The first method is relatively simple. It is named MyDatabse (the class name) so that it is executed when the class is instantiated, and all it does is set the username, password, database name, and host for the MYSQL database.

[code]
function Connect()
{
 // Connect to a mysql database
 $this->db = mysql_connect($this->DBHost,
 $this->DBUser, $this->DBPass) or
 die("MYSQL ERROR: ".mysql_error());
 // Select the database
 mysql_select_db ($this->db,
 $this->{$this->DBConnection}) or
 die("MYSQL ERROR: ".mysql_error());
}
[/code]

This method takes the properties set by MyDatabase and uses them to connect to the MySQL server, and then select the database.

[code]
function Disconnect()
{
 mysql_close($this->db) or
 die("MYSQL ERROR: ".mysql_error());
}
[/code]

This method simply closes the MySQL connection.

We will use these in just a minute, but for now let’s create the AddToDB method so we can start populating our database.

{mospagebreak title=Adam Up}

Every time I meet a guy named Adam, I ask him if he has a brother named Subtract’em. The name of this method is AddToDB. Once again, a corny name that stuck. Let’s quickly discuss the logic behind what we are about to code.

When an HTML form that is using the POST method is submitted, the field names and values are stored in the superglobal variable $_POST. When data from such a form is submitted, this AddToDB method should pull the column names from a table in the database, then compare those names with the names of the $_POST variables. For each name that matches, insert that name and value into two different arrays. Then we’ll implode those arrays into as SQL statement that can be executed. If you are confused, then this code should help you out. First, we’ll look at the method as a whole, and then break her up.

[code]
// Method that dynamically adds
// values to a MYSQL database
// table using the $_POST vars
function AddToDB($tbl)
{
 // Set the arrays we'll need
 $sql_columns = array();
 $sql_columns_use = array();
 $sql_value_use = array();

 

 // Pull the column names from the
 // table $tbl
 $pull_cols = mysql_query("SHOW COLUMNS
 FROM ".$tbl) or die("MYSQL ERROR:
 ".mysql_error());

 

 // Pull an associative array of the
 // column names and put them into a
 // non-associative array
 while ($columns =
 mysql_fetch_assoc($pull_cols))
  $sql_columns[] = $columns["Field"];

 

 foreach( $_POST as $key => $value )
 {
  // Check to see if the variables
  // match up with the column names
  if ( in_array($key, $sql_columns)
  && trim($value) )
  {
   // If this variable contains the
   // string "DATESTAMP" then use MYSQL
   // function NOW()
   if ($value == "DATESTAMP")
   $sql_value_use[] = "NOW()";
   else
   {
    // If this variable contains a
    // number, then don't add single
    // quotes, otherwise check to see
    // if magic quotes are on and use
    // addslashes if they aren't
    if ( is_numeric($value) )
    $sql_value_use[] = $value;
    else
     $sql_value_use[] =
     ( get_magic_quotes_gpc() ) ?
     "'".$value."'" : "'"
     .addslashes($value)."'";
   }
   // Put the column name into the array
   $sql_columns_use[] = $key;
  }
 }

 

// If $sql_columns_use or $sql_value_use
// are empty then that means no values
// matched
if ( (sizeof($sql_columns_use) == 0) ||
(sizeof($sql_value_use) == 0) )
{
 // Set $Error if no values matched
 $this->Error = "Error: No values were
 passed that matched any columns.";
 return false;
}
else
{
 // Implode $sql_columns_use and
 // $sql_value_use into an SQL insert
 // sqlstatement
 $this->SQLStatement = "INSERT INTO
 ".$tbl." (".implode(",",$sql_columns_use).
 ") VALUES (".implode(",",$sql_value_use).
 ")";

 

 // Execute the newly created statement
 if ( @mysql_query($this->SQLStatement) )
   return true;
 else
 {
 // Set $Error if the execution of the
 // statement fails
 $this->Error = "Error: ".mysql_error();
   return false;
  }
 }
}
[/code]

Don’t panic! It looks like too much to handle at first, but if you take a closer look, it’s really quite simple.

[code]
function AddToDB($tbl)
[/code]

The AddToDB method accepts $tbl, which is the name of the database table into which we will execute our SQL statement.

[code]
$sql_columns = array();
$sql_columns_use = array();
$sql_value_use = array();
[/code]

$sql_colums – The array that will store all the column names from the database.

$sql_columns_use – After we have compared the column names to the variable names, the names that matched will be stored in this array.

$sql_value_use – Same as $sql_columns_use, but instead of the names, these are the values for each name.

[code]
$pull_cols =
mysql_query("SHOW COLUMNS FROM
".$tbl) or die("MYSQL ERROR:
".mysql_error());

 

while ($columns =
mysql_fetch_assoc($pull_cols))
 $sql_columns[] =
 $columns["Field"];
[/code]

We pull the column names from the database and put them into the $sql_columns array.

[code]
foreach( $_POST as $key => $value )
{
 if ( in_array($key, $sql_columns) && trim($value) )
{
[/code]

For each $_POST variable, see if its name ($key) exists in the $sql_columns array AND if it holds a value after white space is removed.

[code]
if ($value == "DATESTAMP")
 $sql_value_use[] = "NOW()";
else
{
 if ( is_numeric($value) )
  $sql_value_use[] = $value;
 else
  $sql_value_use[] = (
  get_magic_quotes_gpc() ) ?
  "'".$value."'" :
  "'".addslashes($value)."'";
}

 

$sql_columns_use[] = $key;
[/code]

If the name of the variable matches one of the column names in $sql_columns, then we go through with adding its value to $sql_value_use and name to $sql_column_use. Notice how it checks to see if the value is set to “DATESTAMP,” and if it is, then it uses the MySQL time stamp function. You can add as many of these conditions as you like, but this is one of the most common ones.

If the value is a number, the single quotes aren’t used, but if it’s a string then it checks to see if magic quotes are turned on so we know whether to add slashes or not. This is pretty minimum security, so if you are a paranoid security nut – like me – then you will add quite a bit more validation. For now, validation before the variables get to this point is assumed.

So now we have two arrays. One has column/variable names and the other has the values for each one of those names. Now all we have to do is slap them together and execute the statement.

[code]
if ( (sizeof($sql_columns_use)
== 0) || (sizeof($sql_value_use)
== 0) )
{
 // Set $Error if no values matched
 $this->Error = "Error: No values
 were passed that matched any columns.";
 return false;
}
[/code]

First we check to see if the arrays have any values in them. If they don’t, then that means that no column names matched the $_POST names so it returns an error.

[code]
$this->SQLStatement =
"INSERT INTO ".$tbl."
(".implode(",",$sql_columns_use).")
VALUES (".implode(",",$sql_value_use).")";

 

if ( @mysql_query($this->SQLStatement) )
 return true;
else
{
 $this->Error = "Error: ".mysql_error();
 return false;
}
[/code]

Next we create the SQL statement by imploding the two arrays. Just think if you had to type all the columns and values out; that’d be unheard of. All that’s left is to execute the statement. If it fails, then an error is returned.

Two quickies: we need to create two small methods that can return any errors and the last SQL statement used.

[code]
function GetError()
{
 return $this->Error;
}

 

function GetLastSQL()
{
 return $this->SQLStatement;
}
[/code]

Next we’ll take a look at how we use our new class.

{mospagebreak title=If You POST It, It Will Go}

So now all you have to do is name your form fields the same as your database columns and you are (as we say in Kentucky,) “in bidnus.” Let’s create an example MySQL table:

[code]
CREATE TABLE `my_tbl` (
  `id` int(5) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `age` int(3) NOT NULL default '0',
  `gender` enum('MALE','FEMALE') NOT NULL default 'MALE',
  `phone` varchar(50) NOT NULL default '',
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
[/code]

Now we just create an HTML form using the POST method. We name the fields the same names as the columns in my_tbl.

[code]
<form action="<?php echo $_SERVER['PHP_SELF']; ? >" method="post" name="myForm">
  <p>Name: <input name="name" type="text"></p>
  <p>Age: <input name="age" type="text"></p>
  <p>Gender:<br>
  &nbsp;&nbsp;&nbsp;<input type="radio" name="gender" value="MALE"> Male<br>
  &nbsp;&nbsp;&nbsp;<input type="radio" name="gender" value="FEMALE"> Female</p>
  <p>Phone Number: <input name="phone" type="text"></p>
  <p><input name="action" type="submit" value="AddToDB"></p>
</form>
[/code]

The rest is really simple.

[code]
// Include the file that contains
// the MyDatabase class
require('database.class.php');

// If the form was submitted...
if($_POST['action'])
{
 // Instantiate the class
 $SQL = new MyDatabase;
 // Connect to the database
 $SQL->Connect();
 
 // To get a datetime stamp in the
 // date_added column
 $_POST['date_added'] = 'DATESTAMP';
 
 // Add values to my_tbl
 if(!$SQL->AddToDB('my_tbl'))
  die( $SQL->GetError() );

 // Disconnect from the database
 $SQL->Disconnect();
}
[/code]

Check to see if the form has been submitted, instantiate the class, connect to the database, execute AddToDB passing the table name to it, and finally disconnecting from the database.

[code]
if(!$SQL->AddToDB('my_tbl'))
 die( $SQL->GetError() );
[/code]

Notice that this will check to see if AddToDB returns false. If it does, it kills the execution of the script and displays the error that can be gotten from the GetError method.

But you didn’t think we’d stop there, did you? No, no my blue friend, there is always an update to be done.

{mospagebreak title=Updateagenessly}

How many suffixes can you add to a word before it loses its meaning and sanity? How many mile-long SQL statements can you write before you lose your meaning and sanity? Ok, just checking. The UpdateDB class is almost identical to AddToDB, but with one less array and two new properties. First, the big blob:

[code]
// Method that dynamically updates
// values in a MYSQL database table
// using the $_POST vars
function UpdateDB($tbl, $id, $id_name)
{
 // Set the arrays we'll need
 $sql_columns = array();
 $sql_value_use = array();

 

 // Pull the column names from the
 // table $tbl
 $pull_cols = mysql_query("SHOW COLUMNS FROM ".$tbl) or die(  "MYSQL ERROR: ".mysql_error() );

 

 // Pull an associative array of
 // the column names and put them
 // into a non-associative array
 while ($columns = mysql_fetch_assoc($pull_cols))
  $sql_columns[] = $columns["Field"];

 

 foreach($_POST as $key => $value)
 {
  // Check to see if the variables
  // match up with the column names
  if ( in_array($key, $sql_columns)
  && isset($value) )
  {
   // If this variable contains the
   // string "DATESTAMP" then use
   // MYSQL function NOW()
   if ($value == "DATESTAMP")
    $sql_value_use[] = $key."=NOW()";
   else
   {
    // If this variable contains a
    // number, then don't add single
    // quotes, otherwise check to see
    // if magic quotes are on and use
    // addslashes if they aren't
    if ( is_numeric($value) )
     $sql_value_use[] = $key."=".$value;
    else
     $sql_value_use[] = (
     get_magic_quotes_gpc() ) ?
     $key."='".$value."'" : $key."=
     '".addslashes($value)."'";
   }
  }
 }

 

 // If $sql_value_use is empty then
 // that means no values matched
 if ( sizeof($sql_value_use) == 0 )
 {
  // Set $Error if no values matched
  $this->Error = "Error: No values
  were passed that matched any columns.";
  return false;
 }
 else
 {
  // Implode $sql_value_use into an
  // SQL insert sqlstatement
  $this->SQLStatement = "UPDATE ".$tbl."
  SET ".implode(",",$sql_value_use)."
  WHERE ".$id_name."=".$id;

 

  // Execute the newly created
  // statement
  if ( @mysql_query($this->SQLStatement) )
   return true;
  else
  {
   // Set $Error if the execution of the
   // statement fails
   $this->Error = "Error: ".mysql_error();
   return false;
  }
 }
}
[/code]

And now a breakdown into smaller, less blobby-like chunks of snippet-goodness.

[code]
function UpdateDB($tbl, $id, $id_name)
[/code]

This time, instead of just $tbl, we have two other properties, $id and $id_name.

$id = The actual id number of the record you seek.
$id_name = The NAME of the id column in the database table.

[code]
$sql_columns = array();
$sql_value_use = array();
[/code]

These perform the same way as in AddToDB. Notice how $sql_columns_use is not there any more. Why? Because we are making an UPDATE SQL statement, which doesn’t require placing the column names on one side and the values on the other.

From here on it’s pretty much the same until we hit this baby:

[code]
if ( is_numeric($value) )
 $sql_value_use[] = $key."=".$value;
else
 $sql_value_use[] = ( get_magic_quotes_gpc() ) ? $key."='".$value."'" :  $key."='".addslashes($value)."'";
[/code]

The only difference is that we are storing the $key and $value into the same array, whereas they are in two different arrays in the AddToDB method. Again, we do this because, frankly, UPDATE SQL statements are more fun.

That about does it for UpdateDB. Now we get to see her in action.

{mospagebreak title=UpdateDB in Action}

This will be similar to how we used AddToDB earlier, only this time, we want to query out all the people in the database and update the one we choose. First, let’s output those names to the screen.

[code]
<?php

// Instantiate the class
$SQL = new MyDatabase;
// Connect to the database
$SQL->Connect();

 

? >
<html>
<head>
<title>Using UpdateDB</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
</head>

 

<body>
<table width="500" border="0"
cellspacing="0" cellpadding="3">
 <tr bgcolor="#CCCCCC">
  <td width="121">Name</td>
  <td width="30">Age</td>
  <td width="79">Gender</td>
  <td width="246">Phone</td>
 </tr>
<?php

 

$results = mysql_query("SELECT
id,name,age,gender,phone FROM my_tbl
WHERE (1) ORDER BY name") or
die("MYSQL ERROR: ".mysql_error());

 

if($row=mysql_fetch_assoc($results))
{
 do
 {
  ? >
   <tr>
   <td><a href="<?php echo
   $_SERVER['PHP_SELF']; ?>?id=<?php
   echo $row["id"]; ?>"><?php echo
   $row["name"]; ?></a></td>
   <td><?php echo $row["age"]; ?></td>
   <td><?php echo $row["gender"]; ?></td>
   <td><?php echo $row["phone"]; ?></td>
   </tr>
  <?php
 }
 while ($row=mysql_fetch_assoc($results));
}
else
{
 ? >
  <tr>
  <td colspan="4">No data</td>
  </tr>
 <?php
}

? >
</table><br>
[/code]

I trust that the above code needs no in-depth explanation. It’s simply connecting to the database using the MyDatabase class and pulling an associative array while looping through the results. Now let’s alter our HTML form from above to be used for updating and not inserting.

[code]
<?php

if($_GET['id'])
{
 $results = mysql_query("SELECT
 name,age,gender,phone FROM my_tbl
 WHERE id=".$_GET['id']) or die("MYSQL
 ERROR: ".mysql_error());
 $row=mysql_fetch_assoc($results);
 ?>
 <form action="<?php echo
 $_SERVER['PHP_SELF']; ?>"
 method="post" name="myForm">
  <p>Name: <input name="name"
  type="text" value="<?php echo
  $row["name"]; ?>"></p>
  <p>Age: <input name="age" type="text"
  value="<?php echo $row["age"]; ?>"></p>
  <p>Gender:<br>
  &nbsp;&nbsp;&nbsp;<input name="gender"
  type="radio" value="MALE"<?php echo
  ($row["gender"] == 'MALE') ?
  ' checked' : ''; ?>> Male<br>
  &nbsp;&nbsp;&nbsp;<input type="radio"
  name="gender" value="FEMALE"<?php echo
  ($row["gender"] == 'FEMALE') ?
  ' checked' : ''; ?>> Female</p>
  <p>Phone Number: <input name="phone"
  type="text" value="<?php echo
  $row["phone"]; ?>"></p>
  <p><input name="action" type="submit"
  value="UpdateDB">
  <input name="id" type="hidden"
  value="<?php echo $_GET["id"]; ?>"></p>
</form>
<?php
}

$SQL->Disconnect();

? >
</body>
</html>
[/code]

The only real difference here is the addition of:

<input name=”id” type=”hidden”
value=”<?php echo $_GET["id"]; ?>”>

This is just a hidden form field to pass the id. You will also notice we are echoing out the data in the form fields like so:

value=”<?php echo $row["age"]; ?>”

Alright, now we have got to make this beast actually update the data in the database table.

{mospagebreak title=You Wouldn’t Have to Update It Had You Gotten It Right the First Time}

The blob:

[code]
// Include the file that contains
// the MyDatabase class
require('database.class.php');

 

// If the form was submitted...
if($_POST['id'])
{
 // Instantiate the class
 $SQL = new MyDatabase;
 // Connect to the database
 $SQL->Connect();

 // Update values in my_tbl
 // where id = $_POST['id']
 if(!$SQL->UpdateDB('my_tbl',
 $_POST['id'], 'id'))
  die( $SQL->GetError() );

 // Disconnect from the database
 $SQL->Disconnect();
}
[/code]

Look familiar? It should, unless you accidentally / intentionally skipped a section or two. The only difference now is we are using the UpdateDB method to update our table and now we are passing the id and the name of the id column in the database table.

You Are Much Faster Now, Grasshopper

Now you are able to focus, grasshopper. No more annoying insert and update statements to distract you. This class has become a permanent “must have” in my arsenal of includes. It’s like an audio stream from Digitally Imported Radio (www.di.fm) – I never code without it.

So there you have it; my 2 dollars. It is my hope that you will find this class as helpful as I have. Just don’t use it in a production environment without adding some more validation and better error reporting and so on. Until next time!

Google+ Comments

Google+ Comments