This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

how to update one or more fields ignoring the empty fields into mysql database

By: a guest on Mar 30th, 2012  |  syntax: None  |  size: 4.43 KB  |  views: 14  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. $cst = $_POST['custname'];
  2. $a = $_POST['tel'];
  3. $b = $_POST['fax'];
  4. $c = $_POST['email'];
  5. $sql = mysql_query("UPDATE contacts SET TEL = '$a', FAX = '$b', EMAIL = '$c'
  6.                     WHERE Cust_Name = '$cst' ");
  7.        
  8. $upd = mysql_query("UPDATE custcomm_T SET
  9. Telephone = ".(is_null($a)?'Telephone':"'$a'").",
  10. Fax = ".(is_null($b)?'Fax':"'$b'").",
  11. Mobile = ".(is_null($c)?'Mobile':"'$c'").",
  12. EMail = ".(is_null($d)?'EMail':"'$d'").",
  13. trlicense = ".(is_null($e)?'trlicense':"'$e'").",
  14. trlicexp = ".(is_null($f)?'trlicexp':"'$f'")."
  15. WHERE Cust_Name_VC = '$g' ") or die(mysql_error());
  16.        
  17. $semaphore = false;
  18. $query = "UPDATE contacts SET ";
  19. $fields = array('tel','fax','email');
  20. foreach ($fields as $field) {
  21.    if (isset($_POST[$field]) and !empty($_POST[$field]) {
  22.      $var = mysql_real_escape_string($_POST[$field]);
  23.      $query .= uppercase($field) . " = '$var'";
  24.      $semaphore = true;
  25.    }
  26. }
  27.  
  28. if ($semaphore) {
  29.    $query .= " WHERE Cust_Name = '$cst'";
  30.    mysql_query($query);
  31. }
  32.        
  33. $query = 'update contacts set ';
  34. if ($_POST['tel'] != '') $query .= 'TEL="'.$_POST['tel'].'", ';
  35. if ($_POST['fax'] != '') $query .= 'FAX="'.$_POST['fax'].'", ';
  36. if ($_POST['email'] != '') $query .= 'EMAIL="'.$_POST['email'].'", ';
  37. $query .= "Cust_Name = '$cst' where Cust_Name = '$cst'";
  38.        
  39. foreach ($_POST as $var=>$value) {
  40.             if(empty($value)) continue; //skip blank fields (may be problematic if you're trying to update a field to be empty)
  41.             $sets[]="$var= '$value";
  42.  
  43.         }
  44.         $set=implode(', ',$sets);
  45.         $q_save="UPDATE mytable SET $set WHERE blah=$foo";
  46.        
  47. "UPDATE `custcomm_T`
  48. SET `Telephone` = IF(TRIM('" . mysql_real_escape_string($a) . "') != '', '" . mysql_real_escape_string($a) . "', `Telephone`),
  49. SET `Fax` = IF(TRIM('" . mysql_real_escape_string($b) . "') != '', '" . mysql_real_escape_string($b) . "', `Fax`),
  50. SET `Mobile` = IF(TRIM('" . mysql_real_escape_string($c) . "') != '', '" . mysql_real_escape_string($c) . "', `Mobile`),
  51. SET `EMail` = IF(TRIM('" . mysql_real_escape_string($d) . "') != '', '" . mysql_real_escape_string($d) . "', `EMail`),
  52. SET `trlicense` = IF(TRIM('" . mysql_real_escape_string($e) . "') != '', '" . mysql_real_escape_string($e) . "', `trilicense`),
  53. SET `trlicexp` = IF(TRIM('" . mysql_real_escape_string($f) . "') != '', '" . mysql_real_escape_string($f) . "', `trlicexp`)
  54. WHERE Cust_Name_VC = '" . mysql_real_escape_string($g) . '";
  55.        
  56. # arrays of input => db field names. If both are the same, no index is required.
  57. $optional = array('tel' => 'telephone', 'fax', 'email');
  58. $required = array('custname' => 'cust_name');
  59.  
  60. # $input is used rather than $_POST directly, so the code can easily be adapted to
  61. # work with any array.
  62.  
  63. $input =& $_POST;
  64.  
  65. /* Basic validation: check that required fields are non-empty. More than is
  66.  necessary for the example problem, but this will work more generally for an
  67.  arbitrary number of required fields. In production code, validation should be
  68.  handled by a separate method/class/module.
  69.  */
  70. foreach ($required as $key => $field) {
  71.     # allows for input name to be different from column name, or not
  72.     if (is_int($key)) {
  73.         $key = $field;
  74.     }
  75.     if (empty($input[$key])) {
  76.         # error: input field is required
  77.         $errors[$key] = "empty";
  78.     }
  79. }
  80. if ($errors) {
  81.     # present errors to user.
  82.     ...
  83. } else {
  84.     # Build the statement and argument array.
  85.     $toSet = array();
  86.     $args = array();
  87.     foreach ($optional as $key => $field) {
  88.         # allows for input name to be different from column name, or not
  89.         if (is_int($key)) {
  90.             $key = $field;
  91.         }
  92.         if (! empty($input[$key])) {
  93.             $toSet[] = "$key = ?";
  94.             $args[] = $input[$key];
  95.         }
  96.     }
  97.     if ($toSet) {
  98.         $updateContactsStmt = "UPDATE contacts SET " . join(', ', $toSet) . " WHERE cust_name = ?";
  99.         $args[] = $input['custname'];
  100.         try {
  101.             $updateContacts = $db->prepare($updateContactsStmt);
  102.             if (! $updateContacts->execute($args)) {
  103.                 # update failed
  104.                 ...
  105.             }
  106.         } catch (PDOException $exc) {
  107.             # DB error. Don't reveal exact error message to non-admins.
  108.             ...
  109.         }
  110.     } else {
  111.         # error: no fields to update. Inform user.
  112.         ...
  113.     }
  114. }
  115.        
  116. mysql_query("
  117.     UPDATE contacts
  118.     SET
  119.         TEL = ".(is_null($a)?'TEL':"'$a'").",
  120.         FAX = ".(is_null($b)?'FAX':"'$b'").",
  121.         EMAIL = ".(is_null($c)?'EMAIL':"'$c'")."
  122.     WHERE Cust_Name = '$cst'
  123. ");
clone this paste RAW Paste Data