I recently developed a compact function to efficiently allow users to change info in a form (like account info, contact info, etc.) and then the function automatically identifies what's changed and scans the database to find out where to change it. Basic concept is form is populated from an object from mysql tables, user changes fields (usually few - not for creating new accounts), $_POST is compared against an array/object with current field values, the associative comparison pulls out only changed values as an array where mysql field is index and value is value. Next is where the function really does the hard work, it pulls all of field names from tables you designate that object comes from. if the field name matches an index in the array, it goes ahead and updates that table. Downside is that it does it one field at a time, but since this is a form update, not a new submission, there shouldnt be too many changes, maybe one or two per table which yo uwould have to write individual mysql_query("UPDATE SET")s for anyway.
-
$account = new Account($accountID);
-
$res2=$account->get_properties(); //standard object foreach function that creates array $account->properties[] with fieldnames as index
-
$diff_result=array_diff_assoc($_POST, $res2); //$_POST comes from form submitted. NOTE: must unset($_POST['xxxx']); any fields that are not part of the object - like 'action', 'posted', etc.
-
if(!$account->update_account($diff_result)) {error message}
-
else {proceed with your script}
-
-
public function update_account($diff_result)
-
{
-
global $link;
-
$account_tables = array('users','accounts','account_info','demographics','account_status'); //list of table names where object fields came from
-
for ($x=0;$x<count($account_tables);$x++)
-
{
-
$fields = mysql_query("SELECT * FROM ".$account_tables[$x]." WHERE accountID=".$this->properties['accountID'], $link);
-
if (!$fields) return false;
-
for ($i=0;$i<mysql_num_fields($fields);$i++)
-
{
-
$name = mysql_field_name($fields, $i);
-
if (array_key_exists($name, $diff_result) && $name!='accountID')
{
-
$update = mysql_query("UPDATE ".$account_tables[$x]." SET $name='".$diff_result[$name]."' WHERE accountID=".$this->properties['accountID'],$link);
-
if (!$update) return false;
-
}}}return true;
-
}
[Please use CODE tags when posting source code. Thanks! --pbmods]
Now I am sure there is a way to have all fields in same table that got changed to get UPDATE/SET at same time, but so few fields are changed at any one sitting that I didn't think it necessary.
Anyway, just wanted to offer helpful script/function by a newbie for other newbies....
oh4real
