PHP-MYSQLI-更新数据库 [英] PHP - MYSQLI - UPDATE DATABASE

问题描述

我希望用户更新他们想要在d数据库-表中的任何字段，但是我不希望UPDATE .. SET删除现有记录，如果提交时未更改所有字段，则为空提交..但已更改只有他们想要的..

I want users to UPDATE any field(s) they want in d database - table but I don't want the UPDATE .. SET to erase existing records with empty submission if they submit without changing all the fields.. but changed only the ones they want to..

$sql = "UPDATE table SET username = '$username', email = '$email',
fname = '$fname', lname = '$lname', address = '$address', city = '$city',
country = '$country', phone = '$phone', aboutme = '$aboutme' WHERE email = '$email'";

如果用户仅更新地址和电话，然后提交其条目..该指令将删除表格中未填写的其他字段....我不希望这种情况发生.请调查一下.谢谢

If the user only updates address and phone then submits his entry.. this instruction erases other fields that is not filled in the form.... I don't want that to happen. Kindly look into this. Thanks

请尝试我的建议，但对我不起作用..我可能做错了吗?-我是PHP新手-以下是我的代码:

Please I have tried your suggestion but its not working for me.. may I am doing something wrong -- I am new to PHP - Here is my code below:

$sql = "UPDATE user_profile SET ";
if ($username!="")
$sql ."username = '$username',"

if ($fname!="")
$sql ."fname = '$fname',"

if ($lname!="")
$sql ."lname = '$lname',"

if ($address!="")
$sql ."address = '$address',"

if ($city!="")
$sql ."city = '$city',"

if($country!="") 
$sql ."country = '$country', "

if($phone!="")
$sql ."phone = '$phone', "

if($aboutme!="")
$sql ."aboutme = '$aboutme' "

$sql ."WHERE email = '$email'";

$query = mysqli_query($database,$sql);
if($query)
{
     $message = "<div class=\"btn btn-lg btn-default\"><i class=\"text-success text-center\">Update Successful!</i></div>";
    //echo "update successful";
}

推荐答案

您应该使用参数，而不是将用户输入直接放入字符串中.但是，这是一种好习惯，可以防止SQL注入和格式不正确的参数.

You should be using parameters rather than placing user input directly into strings. However, that is good practice and protects against SQL injection and poorly formed parameters.

但是，对您的问题没有帮助.您需要查看是否有一个新值，否则，请使用旧值.假设新值不存在，则为NULL，然后使用COALESCE().例如:

Doesn't help your problem, though. You need to see if there is a new value, otherwise, use the old one. Assuming the new value is NULL when not present, then use COALESCE(). For example:

SET username = COALESCE($username, username),
    . . .

注意:没有理由在SET语句中设置email，因为您正在WHERE中使用它.

Note: There is no reason to set email in the SET statement because you are using it in the WHERE.

这篇关于PHP-MYSQLI-更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！