PHP MySQL Update and Delete Data

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 :

ID Name City
1 Suresh Paris
2 Babu London
3 Kumar New York

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:-

Record updated successfully
Total updated records : 1

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

ID Name City
1 Suresh Paris
2 Babu London
3 Kumar Paris

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;
?>


You can also search for these topics, php mysql update data, php mysql update data example, php mysql update data syntax, php update data form mysql with conformation, php mysql update all data, php mysql update data mysqli object-oriented example, php mysql update data mysqli procedural example, php mysql update data mysqli pdo, php mysql modify data.

Delete Data From a MySQL Table Using MySQLi and PDO

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 :

ID Name City
1 Suresh Paris
2 Babu London
3 Kumar Paris

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:-

Record deleted successfully
Total deleted records : 2

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

ID Name City
1 Suresh Paris

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



You can also search for these topics, php mysql delete data, php mysql delete data example, php mysql delete data syntax, php delete data form mysql with conformation, php mysql delete all data, php mysql delete data mysqli object-oriented example, php mysql delete data mysqli procedural example, php mysql delete data mysqli pdo, php mysql destroy data.