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

(Page 5 of 7 )

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:


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



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


function UpdateDB($tbl, $id, $id_name)



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.


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



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:


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



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.

Next: UpdateDB in Action >>
 

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!