Updating Boolean Value in MySQL server

[skania]

I've got a form that lets the user submit, or update a record, matching the update to the previously entered E-Mail. However, there is a "Do Not Contact" option that I need to be able to update as well. Here is how I have it structured:

 elseif (isset($_POST['Update'])){
      $parentOneFirstName = mysqli_real_escape_string($dbcon, $_POST["parentOneFirstName"]);
      $parentOneLastName = mysqli_real_escape_string($dbcon, $_POST["parentOneLastName"]);
      $parentTwoFirstName = mysqli_real_escape_string($dbcon, $_POST["parentTwoFirstName"]);
      $parentTwoLastName = mysqli_real_escape_string($dbcon, $_POST["parentTwoLastName"]);
      $homePhone = mysqli_real_escape_string($dbcon, $_POST["homeNumber"]);
      $cellPhone = mysqli_real_escape_string($dbcon, $_POST["cellNumber"]);
      $city = mysqli_real_escape_string($dbcon, $_POST["city"]);
      $state = mysqli_real_escape_string($dbcon, $_POST["state"]);
      $zipPostal = mysqli_real_escape_string($dbcon, $_POST["zipPostal"]);
      $emailAddress = mysqli_real_escape_string($dbcon, $_POST["emailAddress"]);
      $referringFacility = mysqli_real_escape_string($dbcon, $_POST["referringFacility"]);
      $preferredContact = mysqli_real_escape_string($dbcon, $_POST["preferredContact"]);
     // $doNotContact = mysqli_real_escape_string($dbcon, $_POST["doNotContact"]);
      
      $sql = "UPDATE tbl_parentinformation SET emailAddress = '$emailAddress'";
      if(!empty($parentOneFirstName))
        $sql = $sql . ",parentOneFirstName = '$parentOneFirstName'";
      if(!empty($parentOneLastName))
          $sql = $sql . ",parentOneLastName = '$parentOneLastName'";
      if(!empty($parentTwoFirstName))
          $sql = $sql . ",parentTwoFirstName = '$parentTwoFirstName'";
      if(!empty($parentTwoLastName))
          $sql = $sql . ",parentTwoLastName = '$parentTwoLastName'";
      if(!empty($homeNumber))
          $sql = $sql . ",homePhone = '$homePhone'";
      if(!empty($cellNumber))
          $sql = $sql . ",cellPhone = '$cellPhone'";
      if(!empty($city))
          $sql = $sql . ",city = '$city'";
      if(!empty($state))
          $sql = $sql . ",state = '$state'";
      if(!empty($zipPostal))
          $sql = $sql . ",zipPostal = '$zipPostal'";
      if(!empty($referringFacility))
          $sql = $sql . ",referringFacility = '$referringFacility'";
      if(!empty($preferredContact))
          $sql = $sql . ",preferredContact = 'preferredContact'";
    
        
        $sql = $sql . "WHERE emailAddress = '$emailAddress'";   

This allows the user to update only fields they need to update, and doesn't empty out the data already there. The boolean value is received from a checkbox (check = true, nocheck  = false), but I can't get it to check to see if it needs to update or not. Anyone have this problem in the past? I want them to have the OPTION to update it, but not require them to check each time a record is updated. Thanks in advance to anyone that responds

[AbraCadaver]

Uncomment the // $doNotContact = mysqli_real_escape_string($dbcon, $_POST["doNotContact"]);

 

Then:

if(isset($doNotContact))
          $sql = $sql . ",doNotContact = '$doNotContact'";

However I've got a different way that I do it.  I'll post later.

 

[Psycho]

Don't use

$doNotContact = mysqli_real_escape_string($dbcon, $_POST["doNotContact"]);

The value should be a 1 or 0, so you should force the value to be that

$doNotContact = isset($_POST["doNotContact"]) ? 1 : 0;

On your form are you providing a list of empty fields? I think this process is more difficult than it needs to be. I would simply create a form that is populated with all the current values for the selected record. Then, when the user submits the form update ALL the fields based upon what was submitted. In fact, there is a flaw in that logic. If the user wanted to remove a value it would get overlooked because of the empty() checks. By updating all the fields the suer can edit/remove any current values.

Edited May 21, 2013 by Psycho