php - sql - 更新而不覆盖现有信息

Question

我有以下条件代码，如果具有适当用户名的记录不存在，它将插入一条新记录，如果存在，则更新该记录。这工作得很好。

但是，目前，如果我插入一条新记录，并且仅插入名字和姓氏，也许还有地址详细信息，例如电话信息留空，这样就可以了。如果我随后希望仅使用电话记录更新记录，则姓名和地址信息将被替换为任何内容。

我想知道的是，是否有可能有一种简单的方法来填充我的 php html 表单以更新信息，以及我将要更新的字段的内容？我正在使用

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

获取用户输入，然后将其传递给 JavaScript 函数，然后将其传递回下面的 PHP 代码。如果这是不可能的，是否有一种简单的方法可以使用一些 sql/php 魔法，仅更新与不为空的用户输入相对应的字段？

$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

您只需构建更新语句并动态绑定(bind)参数即可。例如

$userQuery = "UPDATE ";

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

...    

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

因此您的查询不会更新不应更改的列。