Dynamically Insert and Update Values In a MySQL Database Using OOP - Adam Up

(Page 3 of 7 )

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.


// 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;
  }
 }
}



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


function AddToDB($tbl)



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


$sql_columns = array();
$sql_columns_use = array();
$sql_value_use = array();



$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.


$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"];



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


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



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.


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;



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.


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;
}



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.


$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;
}



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.


function GetError()
{
 return $this->Error;
}
 
function GetLastSQL()
{
 return $this->SQLStatement;
}



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

Next: If You POST It, It Will Go >>
 

More MySQL Articles
More By Sam 'SammyK' Powers

Comments On: Dynamically Insert and Update Values In a MySQL Database Using OOP

>>> Be the FIRST to comment on this article!