如果字段为空，如何在更新时忽略密码字段？

Question

我正在编辑管理详细信息并且它正在工作，但我遇到了一个有关密码的问题。我的问题是，如果密码字段为空，则仍然更新现有密码，并且我在数据库中获取空数据。我想要的是，如果密码字段中有数据，则更新它，否则在更新时忽略密码字段。我正在使用下面的代码function updateAdmin($pdo){$a_firstname=sanitize_data($_POST['editfirstname']);$a_lastname=sanitize_data($_POST['editlastname']);$a_email=$_POST['editemail'];$a_accessrole=sanitize_data($_POST['editaccessrole']);$admin_id=sanitize_data($_POST['admin_id']);if(!empty($_POST['editpassword'])){    $a_password=sanitize_data($_POST['editpassword']);    $password=password_hash($a_password, PASSWORD_BCRYPT,['cost' => 12]);}  $data=array(     'a_firstname' => $a_firstname,      'a_lastname' => $a_lastname,       'a_email' => $a_email,        'a_password' => $password,    'a_accessrole' => $a_accessrole      );try{  $sql = "UPDATE tbl_admin SET a_firstname=:a_firstname, a_lastname=:a_lastname, a_email=:a_email, a_password=:a_password, a_accessrole=:a_accessrole WHERE admin_id=:admin_id";$stmt= $pdo->prepare($sql);$stmt->bindParam('admin_id', $admin_id);$stmt->execute($data);    $response['error'] = "true"; } catch(PDOExecption $e) {         $dbh->rollback();         print "Error!: " . $e->getMessage() . "</br>";         $response['error'] = "false";    }     // print_r($response);    echo json_encode($response);}function sanitize_data($data) {  $data = trim($data);  $data = stripslashes($data);  $data = strip_tags($data);  $data = htmlspecialchars($data);  return $data;     }

Answer 1

您可以通过稍微修改 php 代码来实现该目标。因此，不要使用固定表列和 SQL 参数来更新表，而是检查 $_POST['editaccessrole'] 并动态构造参数和 UPDATE SQL。希望下面的代码对您有用。

function updateAdmin($pdo) {

    $a_firstname = sanitize_data($_POST['editfirstname']);

    $a_lastname = sanitize_data($_POST['editlastname']);

    $a_email = $_POST['editemail'];

    $a_accessrole = sanitize_data($_POST['editaccessrole']);

    $admin_id = sanitize_data($_POST['admin_id']);

    $data = array('a_firstname' => $a_firstname, 'a_lastname' => $a_lastname, 'a_email' => $a_email, 'a_accessrole' => $a_accessrole);

    $password_sql = "";

    if (isset($_POST['editpassword']) && !empty($_POST['editpassword'])) {

        //Add the a_password column in UPDATE statement if edit password value is set and non empty

        $a_password = sanitize_data($_POST['editpassword']);

        $password=password_hash($a_password, PASSWORD_BCRYPT,['cost' => 12]);

        

        //So the parm and SQL is dynamic based on $_POST['editpassword']

        $data['a_password'] = $password;

        $password_sql = ", a_password=:a_password";

    }

    try {

        $sql = "UPDATE tbl_admin SET a_firstname=:a_firstname, a_lastname=:a_lastname, a_email=:a_email{$password_sql}, a_accessrole=:a_accessrole WHERE admin_id=:admin_id";

        $stmt = $pdo -> prepare($sql);

        $stmt -> bindParam('admin_id', $admin_id);

        $stmt -> execute($data);

        //This is success but you are sending true as error, any particular reason? I hope you did it intentionally.

        $response['error'] = "true";

    } catch(PDOExecption $e) {

        $dbh -> rollback();

        print "Error!: " . $e -> getMessage() . "</br>";

        //This is failed but you are sending false as error, any particular reason? I hope you did it intentionally.

        $response['error'] = "false";

    }

    // print_r($response);

    echo json_encode($response);

}

Answer 2

您提到了我想要的，如果密码字段中有数据，则更新它，否则在更新时忽略密码字段。。if在这种情况下，您可以在 SQL 查询中使用条件，如下所示：

UPDATE tbl_admin 

SET a_firstname = :a_firstname, 

    a_lastname = :a_lastname, 

    a_email = :a_email, 

    a_password = if(a_password is null or length(a_password) = 0,a_password,:a_password), 

    a_accessrole = :a_accessrole 

WHERE admin_id = :admin_id

更新：

如果您的意思是来自发布请求的空密码字段，您可以首先为查询占位符设置适当的数据，如下所示：

$data = array(

    'a_firstname' => $a_firstname,  

    'a_lastname' => $a_lastname,   

    'a_email' => $a_email,    

    'a_password' => empty(trim($a_password)) ? '' : $password,

    'a_accessrole' => $a_accessrole

);

你的 SQL 查询将如下所示：

UPDATE tbl_admin 

SET a_firstname = :a_firstname, 

    a_lastname = :a_lastname, 

    a_email = :a_email, 

    a_password = if(length(:a_password) = 0,a_password,:a_password), 

    a_accessrole = :a_accessrole 

WHERE admin_id = :admin_id