Basic PHP Tutorial

Advanced PHP Tutorial

PHP & MySQL

PHP Reference Manual

PHP MySQL UPDATE

In this tutorial, you will learn how to use PHP to update records in a MySQL table.

Update database table data

UPDATEThe statement is used to change or modify existing records in a database table. This statement is usually used in conjunction with the WHERE clause to apply changes only to those records that match specific conditions.

The basic syntax of the UPDATE statement can be given as follows:

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

Let's use an UPDATE statement with a WHERE clause to perform an SQL query, and then execute this query by passing it to the PHP mysqli_query() function to update table records. Consider the following persons table in the demonstration database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email |
+----+------------+-----------+----------------------+
|　　1　| Peter | Parker | [email protected] |
|　　2　| John | Rambo | [email protected] |
|　　3　| Clark | Kent | [email protected] |
|　　4　| John | Carter | [email protected] |
|　　5　| Harry | Potter | [email protected] |
+----+------------+-----------+----------------------+

The following PHP code example will update the persons table where the id equals1email address of the person.

Example: Procedural approach

<?php
/*Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check connection
if($link === false){
　　　　die("Error: Unable to connect. \" . mysqli_connect_error());
}
　
//Attempt to update query execution
$sql = "UPDATE persons SET email='[email protected]' WHERE id=1";
if(mysqli_query($link, $sql)){
　　　　echo "Record has been successfully updated.";
} else {
　　　　echo "Error: Unable to execute $sql. \" . mysqli_error($link);
}
　
//Close the connection
mysqli_close($link);
?>

Example: Object-oriented approach

<?php
/*Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("Error: Unable to connect. \" . $mysqli-connect_error);
}
　
//Attempt to update query execution
$sql = "UPDATE persons SET email='[email protected]' WHERE id=1";
if($mysqli-query($sql) === true)
　　　　echo "Record has been successfully updated.";
} else{}}
　　　　echo "Error: Unable to execute $sql. ". $mysqli->error;
}
　
//Close the connection
$mysqli->close();
?>

Example: PDO method

<?php
/*Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)　*/
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
　　　　die("Error: Unable to connect. ". $e->getMessage());
}
　
//Attempt to update query execution
try{
　　　　$sql = "UPDATE persons SET email='[email protected]' WHERE id=1";　　　　
　　　　$pdo->exec($sql);
　　　　echo "Record has been successfully updated.";
} catch(PDOException $e){
　　　　die("Error: Unable to execute $sql. ". $e->getMessage());
}
　
//Close the connection
unset($pdo);
?>

After the update, the persons table will look like this:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email |
+----+------------+-----------+--------------------------+
|　　1　| Peter | Parker | [email protected] |
|　　2　| John | Rambo | [email protected] |
|　　3　| Clark | Kent | [email protected] |
|　　4　| John | Carter | [email protected] |
|　　5　| Harry | Potter | [email protected] |
+----+------------+-----------+--------------------------+

Warning:The WHERE clause in the UPDATE statement specifies which records should be updated. If the WHERE clause is omitted, all records will be updated.

PHP MySQL DELETE PHP MySQL ORDER BY