Follow The New Tutorial updates on WhatsApp, Telegram or YouTube.

PHP MySQL Update Data

The UPDATE statement is used to update existing records in a table. This statement is typically used in conjugation with the WHERE clause to apply the changes to only those records that matches specific criteria.

UPDATE statement syntax

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

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated.

Example

Let’s look at the “Students” table:

idfirstnamelastnameemailreg_date
1SmithDoesmith@example.com2014-10-22 14:26:15
2NilMoenil@example.com2014-10-23 10:22:30
Students Table

The following examples update the record with id=2 in the ” Students” table:

Example – MySQLi Procedural

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "UPDATE Students SET lastname='Doe' WHERE id=2";

if (mysqli_query($conn, $sql)) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example – MySQLi Object-oriented

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE Students SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

Example – PDO

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "UPDATE Students SET lastname='Doe' WHERE id=2";

  // Prepare statement
  $stmt = $conn->prepare($sql);

  // execute the query
  $stmt->execute();

  // echo a message to say the UPDATE succeeded
  echo $stmt->rowCount() . " records UPDATED successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

After the record is updated, the table will look like this:

idfirstnamelastnameemailreg_date
1 SmithDoejohn@example.com2014-10-22 14:26:15
2NilDoemary@example.com2014-10-23 10:22:30
After record is updated