php - PDO MYSQL 使用 PHP 准备的更新语句未执行

Question

我正在尝试通过 PHP 在 MySQL 数据库上使用 PDO 执行准备好的语句。

我已经尝试了两个版本的代码都没有工作。函数 update 将执行，但数据库中不会更新任何内容。我使用 fetch() 和 fetchAll() 的 View customerData 函数都可以像我的 deleteData 函数一样工作。

我当前的数据库结构是:

customerID(int11)
firstName(varchar(50)
lastName(varchar(50)
address(varchar(50)
city(varchar(50)
state(varchar(50)
postalCode(varchar(20)
countryCode(char(2)
phone(varchar(20)
email(varchar(50)
password(varchar(20)

我正在使用的当前代码版本:

function update_customer($customerID, $firstName, $lastName, $address, $city, $state, $postalCode, $countryCode, $phone, $email, $password)
{   
  global $db;
    $query = "UPDATE customers
                  SET
                      firstName = :first,
                      lastName = :last,
                      address = :add,
                      city = :c,
                      state = :s,
                      postalCode = :postal,
                      countryCode = :country,
                      phone = :p,
                      email = :e,
                      password = :password
                  WHERE customerID = :ID";
    $statement = $db->prepare($query);
    $statement->bindValue(':first',$firstName);
    $statement->bindValue(':last', $lastName);
    $statement->bindValue(':add', $address);
    $statement->bindValue(':c' ,$city);
    $statement->bindValue(':s',$state);
    $statement->bindValue(':postal', $postalCode);
    $statement->bindValue(':country',$countryCode);
    $statement->bindValue(':p', $phone);
    $statement->bindValue(':e', $email);
    $statement->bindValue(':pass', $password);
    $statement->bindValue(':ID', $customerID);
    $statement->execute();
    $statement->closeCursor();
}

我使用过的其他版本的代码

function update_customer($customerID, $firstName, $lastName, $address, $city, $state, $postalCode, $countryCode, $phone, $email, $password)
{
    global $db;
    $query = "UPDATE customers
                  SET
                      firstName = ?,
                      lastName = ?
                      address = ?,
                      city = ?,
                      state = ?,
                      postalCode = ?,
                      countryCode = ?,
                      phone = ?,
                      email = ?,
                      password = ?
                  WHERE customerID = ?";
    $statement = $db->prepare($query);
    $statement->bindParam('ssssssssssi', $firstName, $lastName, $address, $city, $state, $postalCode, $countryCode, $phone, $email, $password, $customerID);
    $statement->execute();
    $statement->closeCursor();
}

我的其他 3 个准备好的语句工作得很好，例如，这里是填充更新客户表单的准备好的语句。

function view_customerData ($customerID) {
    global $db;
    $query = "SELECT * FROM customers
                  WHERE customerID = $customerID";
    try {
        $statement = $db->prepare($query);
        $statement->execute();
        $customerData = $statement->fetch();
        return $customerData;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        echo "<p>Database error: $error_message </p>";
        exit();
    }
}

Answer 1

尝试将整个更新客户代码放在 try block 中，如果发生任何错误，则将其放在 catch block 中。但首先要修复这一行

$statement->bindValue(':pass', $password); 

到

 $statement->bindValue(':password', $password); 
                             ^^^^

try {
 //.....put your update customer code here ...
} catch (PDOException $e) {
     $error_message = $e->getMessage();
    echo "<p>Database error: $error_message </p>";
    exit();
}