Home arrow MySQL arrow Page 5 - Dynamically Insert and Update Values In a MySQL Database Using OOP

Updateagenessly - MySQL

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.

TABLE OF CONTENTS:
  1. Dynamically Insert and Update Values In a MySQL Database Using OOP
  2. Essential Connection
  3. Adam Up
  4. If You POST It, It Will Go
  5. Updateagenessly
  6. UpdateDB in Action
  7. You Wouldn't Have to Update It Had You Gotten It Right the First Time
By: Sam 'SammyK' Powers
Rating: starstarstarstarstar / 96
February 04, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Sam 'SammyK' Powers
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: