By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Here's handsoff function() updating fields in multiple tables from big form.

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. $account = new Account($accountID);
  2. $res2=$account->get_properties(); //standard object foreach function that creates array $account->properties[] with fieldnames as index
  3. $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.
  4. if(!$account->update_account($diff_result)) {error message}
  5. else {proceed with your script}
  6.  
  7. public function update_account($diff_result)
  8. {
  9. global $link;
  10. $account_tables = array('users','accounts','account_info','demographics','account_status'); //list of table names where object fields came from
  11. for ($x=0;$x<count($account_tables);$x++) 
  12.     
    {
  13.     $fields = mysql_query("SELECT * FROM ".$account_tables[$x]." WHERE accountID=".$this->properties['accountID'], $link);
  14.     if (!$fields) return false;
  15.     for ($i=0;$i<mysql_num_fields($fields);$i++)
  16.         
    {
  17.         $name = mysql_field_name($fields, $i);
  18.         if (array_key_exists($name, $diff_result) && $name!='accountID') 
    {
  19. $update = mysql_query("UPDATE ".$account_tables[$x]." SET $name='".$diff_result[$name]."' WHERE accountID=".$this->properties['accountID'],$link);
  20. if (!$update) return false;
  21. }}}return true;    
  22. }
[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
Jun 22 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.