I have a function that is used for dynamically building an update query that works by having a manually created form with fields that match the names of the table columns, then building the query based on the field names and values being submitted. However I have come across an instance where certain posted fields must be filtered out. I can do it easily if done manually but I am trying to make it work when the number of columns to remove, which are fed in as an array, are not necessarily known in advance.
For example, if I use this in place of one of the foreach loops below, it works:
$FieldName = array_values($Date1Name);
// Remove $Date1Name submit values
if ($key == $FieldName[1]) continue;
if ($key == $FieldName[2]) continue;
if ($key == $FieldName[3]) continue;
but I am trying to do it dynamically similar to the following. Note that in this case, the first value of the array must not be removed as it is the actual field name but in the second foreach loop, all are to be removed. Any ideas?
function formProcess($ID,$DBName,$TableName,$PassName,$Date1Name,$Date2Name,$RemoveFields,$insertGoTo) {
$Values = array();
// Update record
if (isset($_POST["update"])) :
unset($_POST['update']);
foreach ($_POST as $key=>$value ) :
if (is_array($Date1Name)) :
// ARRAY EXAMPLE
//$Date1Name = array("StartDate","month1","day1","year1");
$Field1Name = array_values($Date1Name);
foreach($Date1Name as $keyrm) :
if ($keyrm !== 0 && $key == $Field1Name) continue;
endforeach;
endif;
if (is_array($RemoveFields)) :
// Remove certain fields from POST using $RemoveFields array values
// ARRAY EXAMPLE
//$RemoveFields = array("ID","EndName");
$FieldRemoved = array_values($RemoveFields);
foreach($RemoveFields as $keyrm) :
if ($keyrm == $FieldRemoved) continue;
endforeach;
endif;
$Values[] = "`$key`=".isNull($value, $DBName);
endforeach;
$sqlUpdate = "UPDATE $TableName SET ".implode(",",$Values)
." WHERE ID='".intval($ID)."'";
DBConnect($sqlUpdate, "Update", $DBName);
if ($insertGoTo) :
Redirect(sprintf('%s', $insertGoTo."?ID=".intval($ID)));
endif;
endif;
}
I have done a similar task in the past, perhaps it will give you some idea(s). This is assuming you are using mysql, if not, the filter may need some degree of manual handling.
1) Create a function that will extract the fields from the table. I have a class that I am feeding in which is labeled as $db. It will run my queries and return associative arrays:
function getFields($table,$db)
{
# Run the "describe table" query
$fields = $db->query("describe {$table}")->getResults();
# Extract just the field names from the rows
foreach($fields as $row) {
$val[] = $row['Field'];
}
# Send back the field names
return $val;
}
Here is what the above function would return on a simple user table:
Array
(
[0] => ID
[1] => username
[2] => password
[3] => first_name
[4] => last_name
[5] => email
[6] => country
[7] => usergroup
[8] => user_status
[9] => timestamp
)
2) Next create the filtering function. I would send back a bind array as well, I am using PDO here as my end database, not sure which library you are using.
function filterArray($array,$filter = array())
{
$bind =
$cols = array();
if(!is_array($filter))
$filter = array();
foreach($array as $key => $value) {
if(!empty($filter) && !in_array($key, $filter))
continue;
$bKey = ":{$key}";
$bind[$bKey] = $value;
$cols[] = $key;
}
return array(
'columns'=>$cols,
'bind' => $bind
);
}
3) Make the update function. I have made it simple, you can make it as complex as you need it.
# The first is the filtered array, the $where is which key to update on,
# the $table is obvious, the $op is the operator which could be substituted for
# LIKE or !=
function getUpdateStatement($array,$where,$table,$op = '=')
{
# Start off the statement
$sql['update'][] = "UPDATE {$table} SET";
# Combine and loop the keys/values and assign the WHERE anchor as well
foreach(array_combine($array['columns'],array_keys($array['bind'])) as $col => $bind) {
if($col == $where)
$sql['where'][] = "WHERE `{$col}` {$op} {$bind}";
else
$sql['cols'][] = "`{$col}` = {$bind}";
}
# Create the statement from the parts
$statement = implode(' ',$sql['update']).' '.implode(", ",$sql['cols']).' '.implode(" AND ",$sql['where']);
# Send back statement
return $statement;
}
To use:
# Get all the columns from the table
$filter = getFields('users',$queryEngine);
# Let's pretend the is a actually your $_POST array...
$POST = array(
'ID' => 123,
'first_name' => 'John',
'last_name' => 'Doe',
'email' => 'test@best.com',
'update' => 'SAVE',
'action' => 'update_my_stuff',
'token' => 'aB23As753hedD6baC213Dsae4'
);
# Create the filtered array
$filtered = filterArray($POST,$filter);
# Create the statement
echo getUpdateStatement($filtered,'ID','user');
This above statement will read:
UPDATE users SET `first_name` = :first_name, `last_name` = :last_name, `email` = :email WHERE `ID` = :ID
Remember the bind values are in the $filtered['bind'] array. Hopefully this is what you are kind of looking for or that it will maybe give you some ideas.
Working now and updated to handle Insert, Update and Delete operations. I decided that I couldn't have separate functions for the update and insert query building due to the need for some special field processing so I moved the functionality back into the main formProcess() function. I did need to filter out certain fields rather than say which fields to keep so came up with a way to do that using a combination of array_diff_key() and array_flip() which seems to do the trick. This provides an array of all the fields to be processed. The main reason for this is to filter out day, month and year selectors, then process them into a single field:
$filteredarray = array_diff_key($_POST, array_flip($RemoveFields));
I am sure that some of the programmers here can really simplify this code but here it is as it now stands with some annotations to say what does what. I hope it helps someone!
function formProcess($ID,$DBName,$TableName,$PassName,$Date1Name,$Date2Name,$RemoveFields,$insertGoTo) {
// Insert record
if (isset($_POST["insert"])) :
unset($_POST["insert"]);
// Remove unneeded fields from $RemoveFields variable
if (!is_array($RemoveFields)) $RemoveFields = array($RemoveFields);
$filteredarray = array_diff_key($_POST, array_flip($RemoveFields));
// Build the INSERT query
foreach($filteredarray as $col => $val) :
// Process Password field
if ($PassName) :
// Encode password field
if ($col == $PassName && $val != "") $val=md5($val);
// If no changes, save original password
if ($col == $PassName && $val == "") continue;
endif;
// Process Date/Time fields using custom dateProcess()
if (is_array($Date1Name)) :
$pieces1 = $Date1Name;
if ($col == $pieces1[0]) $val = dateProcess($Date1Name);
endif;
if (is_array($Date2Name)) :
$pieces2 = $Date2Name;
if ($col == $pieces2[0]) $val = dateProcess($Date2Name);
endif;
$Fields[] = "`$col`";
$Values[] = isNull($val, $DBName);
endforeach;
$sqlInsert = "INSERT INTO $TableName (".implode(",",$Fields).")
VALUES (".implode(",",$Values).")";
// Custom function to process the INSERT query
$InsertID = DBConnect($sqlInsert, "Insert", $DBName);
if ($insertGoTo) :
Redirect(sprintf('%s', $insertGoTo."?ID=".intval($InsertID)));
endif;
// Update record
elseif (isset($_POST["update"])) :
unset($_POST['update']);
// Remove unneeded fields using array $RemoveFields variable
if (!is_array($RemoveFields)) $RemoveFields = array($RemoveFields);
$filteredarray = array_diff_key($_POST, array_flip($RemoveFields));
// Build the UPDATE query
$count = 0;
$fields = '';
$Where = '';
foreach($filteredarray as $col => $val) :
if ($count++ != 0 && $col != "ID") $fields .= ', ';
if ($col == "ID") :
$Where = " WHERE `$col` = " . intval($val);
else :
// Process Password field
if ($PassName) :
// Encode password field
if ($col == $PassName && $val != "") $val=md5($val);
// If no changes, save original password
if ($col == $PassName && $val == "") continue;
endif;
// Process Date/Time fields
if (is_array($Date1Name)) :
$pieces1 = $Date1Name;
// Process first Date/Time field using custom function
if ($col == $pieces1[0]) $val = dateProcess($Date1Name);
endif;
if (is_array($Date2Name)) :
$pieces2 = $Date2Name;
// Process second Date/Time field using custom function
if ($col == $pieces2[0]) $val = dateProcess($Date2Name);
endif;
// Build the UPDATE query
$val = isNull($val, $DBName);
$fields .= "`$col` = $val";
endif;
endforeach;
$sqlUpdate = "UPDATE $TableName SET $fields $Where";
// Custom function to process the INSERT query
DBConnect($sqlUpdate, "Update", $DBName);
if ($insertGoTo) :
Redirect(sprintf('%s', $insertGoTo."?ID=".intval($ID)));
endif;
// Delete record
elseif (isset($_POST["delete"])) :
// Build the DELETE query
$sqlDelete = "DELETE FROM $TableName WHERE ID=".intval($ID);
// Custom function to process the DELETE query
DBConnect($sqlDelete, "Delete", $DBName);
if ($insertGoTo) :
Redirect(sprintf('%s', $insertGoTo));
endif;
elseif (isset($_POST["clear"])) : // NOT CURRENTLY FUNCTIONAL
foreach ($_POST as $key=>$value) :
if ($key == 'clear') continue;
if (trim($key)) :
$key = "";
$value = "";
endif;
if (isset($_POST[$key])) :
unset($key);
endif;
endforeach;
endif;
}