PHP MySQL Update and Delete Data

Update Data In a MySQL Table Using MySQLi and PDO

To change existing records in a table, use the UPDATE statement.

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

Take note of the WHERE clause in the UPDATE syntax : The WHERE clause defines which record or records should be modified. If you leave out the WHERE clause, all records will be updated!

Let's look at the "Employee" table :

Example (MySQLi Object-oriented)

The affected_rows property is used to return number of rows affected or changed by last executed query (create, insert, update, and delete).

In the "Employee" table, the following examples update the record for city column with the id=3 value :

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Build sql update query 
$sql = "UPDATE Employee SET city='Paris' WHERE id=3";
if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully<br />";
  $count = $conn->affected_rows;
  echo "Total updated records : " . $count;
} else {
  echo "Error: " . $sql . "<br />" . $conn->error;
}
// Close connection
$conn->close();
?>

Output:-

After executing the above program the "Employee" table look like:

Example (MySQLi Procedural)

The mysqli_affected_rows() method is used to get the number of records affected by last executed query.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error);
}
// Build sql update query 
$sql = "UPDATE Employee SET city='Paris' WHERE id=3";
if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully<br />";
  $count = mysqli_affected_rows($conn);
  echo "Total updated records : " . $count;
} else {
  echo "Error: " . $sql . "<br />" . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>

Example (PDO)

The prepare() method is used to build a sql query.

The execute() method is used to execute the prepared sql query.

The rowCount() funtion return the number of affected records in the table by last executed query.

<?php
try {
  $conn = new PDO("mysql:host=localhost;dbname=myDB", "username", "password");
  // Prepare statement
  $sql = "UPDATE Employee SET city='Paris' WHERE id=3";
  $st = $conn->prepare($sql);
  // Execute query
  $st->execute();
  // Display updated message
  echo "Record updated successfully<br />";
  $count = $st->rowCount();
  echo "Total updated records : " . $count;
} catch(PDOException $e) {
  echo $sql . "<br />" . $e->getMessage();
}
$conn = null;
?>

To remove records from a table, use the DELETE statement :

DELETE FROM table_name
WHERE some_column = some_value

Note in the DELETE syntax, the WHERE clause: The WHERE clause sets out the record or records to be removed. If you skip the WHERE clause, All records are erased.

Let's look at the "Employee" table :

Example (MySQLi Object-oriented)

In the "Employee" table, the following examples delete the record(s) for city column with the city='Paris' value :

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Build sql delete query 
$sql = "DELETE FROM Employee WHERE city='Paris'";
if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully<br />";
  $count = $conn->affected_rows;
  echo "Total deleted records : " . $count;
} else {
  echo "Error: " . $sql . "<br />" . $conn->error;
}
// Close connection
$conn->close();
?>

Output:-

After executing the above program the "Employee" table look like:

For Mysqli Procedural and PDO programs, just replace the update query with delete query and please refer the update examples in top of page.