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

Adam Up - 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

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.



 
 
>>> 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: