Building Dynamic Database Inserts
As follow up to yesterday’s post about dynamically assigning variables from database queries, below is some code that takes all incoming values from a PHP form and dynamically maps them to database columns, again, avoiding manually parsing and assigning variables for every query.
The code is simple: form input comes in as HTTP_POST_VARS, which comprise both a key (the variable name) and a value (the information in the form field). We first loop through and break those two elements out of each field and name them $key and $value. Then we create a list of the keys, or field names (which map identically to their counterpart columns in the database), in the format required for the INSERT statement. We do the same for the $values. After looping though and building our lists, we do a little pattern matching and replacement to strip off the trailing commas from each list (preg_replace). From there, we build and execute our elegant little INSERT statement. And like yesterday’s example, this works with any number of fields.
I should note that performance would be better if instead of the preg_replace I used some conditional logic, but I didn’t have time to write that yet.
while(list($key, $value) = each($HTTP_POST_VARS))
{
if($key != “addRequest” && $key != “”)
{
$sqlKeys .= “$key, “;
$sqlValues .= “‘$value’, “;
}
}
$sqlKeys = preg_replace(“!, $!”,”",$sqlKeys);
$sqlValues = preg_replace(“!, $!”,”",$sqlValues);
$sql = “INSERT into vendorApprovals ($sqlKeys) values ($sqlValues)”;
$result = mysql_query($sql);
}
Here also is the code to edit the record we created above:
$sql = “UPDATE vendorApprovals set “;
while(list($key, $value) = each($HTTP_POST_VARS))
{
if($key != “editRequest” && $key != “” && $key != “editID”)
{
$sql .= “$key = ‘$value’, “;
}
}
$sql = preg_replace(“!, $!”,”",$sql);$sql .= ” where id = ‘$editID’;”;
$result = mysql_query($sql);
February 2nd, 2007 at 12:03 pm
I am basking in the glow of the PHP goodness…these two bits were some of the more time consuming things I do on a standard website…a big step to making my life easier.
May 31st, 2007 at 4:11 pm
Chris the two are definitely good especially when not having to match up the fields in the db inserts to the values on the updates but my I add something that might be easier to read and also would work for any table.
function insert($table, $record){
foreach ($record as $key => $val){
//check for first value
if ($count==0) {$fields = “`”.$key.”`”; $values = mySQLSafe($val);}
else {$fields .= “, “.”`”.$key.”`”; $values .= “, “.mySQLSafe($val);}
$count++;
}
$query = “INSERT INTO “.$tablename.” (“.$fields.”) VALUES (“.$values.”)”;
mysql_query($query);
}
also this is a good opportunity to clean all of your in comming values for SQL injection and unescaped ‘ or slashes.
function mySQLSafe($value, $quote=”") {
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
//$value = htmlentities($value);
if(version_compare(phpversion(),”4.3.0″)==”-1″) {
$value = mysql_escape_string($value);
} else {
$value = mysql_real_escape_string($value);
}
$value = $quote . $value . $quote;
return $value;
}
function update($table, $record, $where){
foreach ($record as $key => $val){
//check for first value
if ($count==0) {$fields = “`”.$key.”`=”.mySQLSafe($val);}
else {$fields .= “,`”.$key.”`=”.mySQLSafe($val);}
$count++;
}
$query = “UPDATE “.$tablename.” SET “.$fields.” WHERE “.$where;
mysql_query($query);
}
Hope that can help someone.
Matt
May 31st, 2007 at 4:17 pm
Very nice Matt. Thank you for that. I love the extensibility of making the table names dynamic.
Excellent point about the SQL injection as well. I wrote this code while working on an intranet so was not so concerned with such things. But as you point out, it is good code and hopefully will be useful to others.
Cheers.
Christopher