469,295 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,295 developers. It's quick & easy.

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

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
0 2145

Post your reply

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

Similar topics

5 posts views Thread by Richard Stanton | last post: by
4 posts views Thread by Geoff | last post: by
2 posts views Thread by =?Utf-8?B?VmFuZXNzYQ==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.