PHP & MySQL - Update Data

The MySQL UPDATE statement is used to modify the existing records in a table. The MySQL WHERE clause can be used with the UPDATE statement to update the selected rows, otherwise all the rows will be assigned the updated value.

The syntax for using UPDATE statement in MySQL is given below:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE conditions;

To specify condition in a query, MySQL comparison or logical operators like <, >, =, LIKE, IN, NOT, NULL etc. are used.

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

Update Data of a MySQL Table - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The example below demonstrates how to update the City and Salary data of an employee whose EmpID is 5 (uses object-oriented style).

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//query for updating records
$sql = "UPDATE Employee
SET City = 'Mumbai', Salary = 2900
WHERE EmpID = 5";

//executing the query
if (!$mysqli->query($sql)) {
  echo "Error updating records: ". $mysqli->error;
} else { 
  echo "Records updated successfully.";
}

//closing the connection
$mysqli->close();
?>

The output of the above code will be similar to:

Records updated successfully.

Update Data of a MySQL Table - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//query for updating records
$sql = "UPDATE Employee
SET City = 'Mumbai', Salary = 2900
WHERE EmpID = 5";

//executing the query
if (!mysqli_query($mysqli, $sql)) {
  echo "Error updating records: ". mysqli_error($mysqli);
} else { 
  echo "Records updated successfully.";
}

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be similar to:

Records updated successfully.

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.