PHP Delete and Update Data in MySQL

PHP Delete and Update Data in MySQL

MySQL Delete Data is used In Deleting data using MySQL the DELETE Statement is used.

Syntax:

DELETE FROM tablename
WHERE specificcolumn = correspondingvalue

MySQL Delete Data

Take Note! Using the DELETE command you must not forget to include the WHERE clause because the Where clause is used to specify record you want to delete.

Without having the WHERE clause, all records in the table will be deleted.

Here the following examples in Deleting record from “tblpeople”.

Example using MySQLi(Object-Oriented):

die("Connection failed: " . $conn->connect_error);
}

// sql to delete record from table
$sql = "DELETE FROM tblpeople WHERE ID=9";

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

$conn->close();
?>

Example using MySQLi(Procedural):

$username = "root";
$password = "";
$database = "mysqltutorial";

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

// sql to Delete record from table
$sql = "DELETE FROM tblpeople WHERE ID=9";

if (mysqli_query($conn, $sql)) {

echo "Record Delete successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example using PDO:

$sql = "DELETE FROM tblpeople WHERE ID=8";

$conn->exec($sql);
echo "Record Delete successfully";
}
catch(PDOException $e)
{

echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

MySQL Update Data

To update the existing data in the MySQL database you need to used the UPDATE statement.

Syntax:

UPDATE tablename
SET column1=val1, column2=val2, column3=val3
WHERE column=value; 

Take Note! 

Using the UPDATE command you must not forget to include the WHERE clause, because the Where clause is used to specify record you want to modify. Without having the WHERE clause, all records in the table will be modified. Example in Updating Data using MySQLi(Object-Oriented), MySQLi(Procedural) and PDO. 

Example using MySQLi(Object-Oriented):

die("Connection failed: " . $conn->connect_error);
}

// sql to UPDATE record from table
$sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;";

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

$conn->close();
?>

Example using MySQLi(Procedural):

$username = "root";
$password = "";
$database = "mysqltutorial";

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

// sql to UPDATE record from table
$sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;";

if (mysqli_query($conn, $sql)) {

echo "Record UPDATED successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example using PDO:

$sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;"; 
$conn->exec($sql); 
echo 
"Record UPDATED successfully";
 } catch(PDOException $e) { 
echo $sql . " " . $e->getMessage();
 } 
$conn = null; 
?>

Summary

In summary, we have discussed here how to update and delete MySQL data using PHP with MySQLi and PDO. After you finish this article, you may go back to the first tutorial and review from the beginning and you be able to realize that you are ready to create whole PHP Projects.

Note: If you have questions or suggestions about MySQL tutorial with PHP, please leave a comment below.