PHP MySQL UPDATE 查詢

在本教程中，你將學習如何使用 PHP 更新 MySQL 表中的記錄。

更新資料庫表資料

UPDATE語句用於更改或修改資料庫表中的現有記錄。此語句通常與 WHERE 子句結合使用，以僅將更改應用於符合特定條件的記錄。

UPDATE 語句的基本語法如下：

UPDATE table_name SET column1=value, column2=value2,... WHERE column_name=some_value 

讓我們使用 UPDATE 語句和 WHERE 子句進行 SQL 查詢，之後我們將執行此查詢，方法是將其傳遞給 PHP mysqli_query() 函式以更新表記錄。考慮 demo 資料庫中的 persons 表 ：

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  1 | Peter      | Parker    | peterparker@mail.com |
|  2 | John       | Rambo     | johnrambo@mail.com   |
|  3 | Clark      | Kent      | clarkkent@mail.com   |
|  4 | John       | Carter    | johncarter@mail.com  |
|  5 | Harry      | Potter    | harrypotter@mail.com |
+----+------------+-----------+----------------------+

以下示例中的 PHP 程式碼將更新 persons 表中 id 等於 1 的人員的電子郵件地址。

程序導向方式

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt update query execution
$sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE id=1";
if(mysqli_query($link, $sql)){
    echo "Records were updated successfully.";
} else {
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);
?>

物件導向方式

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
// Check connection
if($mysqli === false){
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}
 
// Attempt update query execution
$sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE id=1";
if($mysqli->query($sql) === true){
    echo "Records were updated successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
 
// Close connection
$mysqli->close();
?>

PDO 方式

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}
 
// Attempt update query execution
try{
    $sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE id=1";    
    $pdo->exec($sql);
    echo "Records were updated successfully.";
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
 
// Close connection
unset($pdo);
?>

更新後，persons 表將如下所示：

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | Peter      | Parker    | peterparker_new@mail.com |
|  2 | John       | Rambo     | johnrambo@mail.com       |
|  3 | Clark      | Kent      | clarkkent@mail.com       |
|  4 | John       | Carter    | johncarter@mail.com      |
|  5 | Harry      | Potter    | harrypotter@mail.com     |
+----+------------+-----------+--------------------------+

警告： 在 UPDATE 語句中的 WHERE 子句指定一個或多個記錄應更新。如果省略該 WHERE 子句，則將更新所有記錄。