sql - updating without overriding existing information

Question

I have the following conditional code, which will insert a new record if a record with the appropriate username does not exist, and update the record if it does exist. This works fine.

However, at the moment, if I insert a new record, and only insert the firstname and lastname, and maybe address details with say phone information being left blank this is fine. If I then wish to update the record with just the phone record, the name and address information gets replaced with nothing.

What I would like to know, is if it is possibly to have an easy way to populate my php html form for updating the information, with the content of the field I will be updating? I am using

 <input type="text" name="uniquename" />

To obtain user input, which is then passed to a javascript function which is then passed back to the below php code. If this is not possible, is there an easy way to work some sql/php magic, to only update a field corresponding the userinput which is not empty?

$usernameQuery = "select username from USERS where username = '" . $con->escape_string($username) . "'"; 

$xblah = $con->query($usernameQuery);
  while ($row = mysqli_fetch_assoc($xblah))
  {
    $checkUsername = $row['username'];
   }

if ($checkUsername == null) {

$userQuery = "INSERT INTO USERS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
if ($userInfo = $con->prepare($userQuery)) {
    $userInfo->bind_param("ssssssssssssssssssss", $username, $firstname, $lastname, $flaggedauctions, $lastauction, $street1, $city1, $postcode1, $street2, $city2, $postcode2, $phone, $mobilephone, $fax, $email, $website, $bank, $banknumber, $accountnumber, $comments);
    $userInfo->execute();
    $userInfo->close();
    echo "true";
} else {
echo "false";
}
print_r($con->error);
}

else if ($checkUsername == $username) {

$userQuery = "UPDATE USERS SET firstname = ?, lastname = ?, flaggedauctions = ?, lastauction = ?, street1 = ?, city1 = ?, postcode1 = ?, street2 = ?, city2 = ?, postcode2 = ?, phone = ?, mobilephone = ?, fax = ?, email = ?, website = ?, bank = ?, banknumber = ?, accoutnumber = ? WHERE username = ?";
if ($userInfo = $con->prepare($userQuery)) {
    $userInfo->bind_param("sssssssssssssssssss", $firstname, $lastname, $flaggedauctions, $lastauction, $street1, $city1, $postcode1, $street2, $city2, $postcode2, $phone, $mobilephone, $fax, $email, $website, $bank, $banknumber, $accountnumber, $username);
    $userInfo->execute();
    $userInfo->close();
    echo "true";
} else {
echo "false";
}
print_r($con->error);
}

Answer 1

You just have to build your update statement and bind parameter dynamically. e.g.

$userQuery = "UPDATE ";

if (!empty($firstname)) {
  $userQuery += "firstname = ?,";
}

...    

if (!empty($firstname)) {
  $bindArgs[] = $firstname;
}
...

so your query won't update columns that are not supposed to change.

Answer 2

More generally, note that:

• It's wise to specify your column names in INSERT ... VALUES because otherwise if someone adds a column you'll be annoyed and if someone reorders the columns you'll be confused.

• There's a race condition between your select and your update ... another process could insert/delete a row while you're still thinking, causing your insert/update to fail Consider either:

  • INSERT ... ON DUPLICATE KEY UPDATE ...
  • REPLACE INTO ...