PHP MySQL Delete

In this article, you will learn how to use Php Mysql Delete to delete records from a MySQL database using PHP MySQLi and PDO extensions. We will start by discussing the basics of deleting data in MySQL, and then move on to explain how to use MySQLi and PDO for deletion.

Additionally, we will cover topics such as binding parameters and handling errors, ensuring you have a comprehensive understanding of the process.

Delete operation in MySQL is used to remove one or more rows from a table. In PHP, you can use the MySQLi or PDO extensions to perform the delete operation on a MySQL database.

Let’s see how to do it using both extensions.

Table Of Content-

PHP MySQL Delete:
- PHP MySQL Delete Table Data with MySQLi and PDO.:

PHP MySQL Delete Table Data with MySQLi and PDO.

You can delete records from a table using the DELETE statement in the following manner:

DELETE FROM table_name
WHERE some_column = some_value

There is a WHERE clause that specifies what record or records should be deleted.

All records will be deleted if you do not include the WHERE clause in your command!

In the “Users” table, we can see the following data:

id	firstname	lastname	email	age	gender	reg_date
1	Matt	LeBlance	[email protected]	55	Male	2023-03-08 20:09:15
2	Matthew	Perry	[email protected]	53	Male	2023-03-08 20:09:15
3	Jennifer	Anniston	[email protected]	54	Female	2023-03-08 20:09:15

Here are some examples of how to delete a record with id=2 in the “Users” table using the following code:

Example (MySQLi Object-oriented):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // query to delete a record $sql = "DELETE FROM Users WHERE id=2"; if ($conn->query($sql) === TRUE) { echo "Data deleted successfully"; } else { echo "Error deleting data: " . $conn->error; } $conn->close(); ?>

As an example, the following is a MySQLi procedural way of displaying the same information as shown above:

Example (MySQLi Procedural):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Checking connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } // query to delete a record $sql = "DELETE FROM Users WHERE id=2"; if (mysqli_query($conn, $sql)) { echo "Data deleted successfully"; } else { echo "Error deleting data: " . mysqli_error($conn); } mysqli_close($conn); ?>

Below is an example using MySQL PDO(PHP Data Objects) method:

Example (PDO):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Specify the exception error mode in the PDO $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // query to delete a record $sql = "DELETE FROM Users WHERE id=2"; // Due to the fact that no results are returned, use exec() instead $conn->exec($sql); echo "Data deleted successfully"; } catch (PDOException $e) { echo $sql . " " . $e->getMessage(); } $conn = null; ?>

Example Explanation

In this PHP example, we define the servername, username, password, and database name that we will use to establish a connection to the MySQL database.

We then use a try-and-catch block to handle any exceptions that may occur during the code’s execution.
Within the try block, we establish a PDO connection with the MySQL database and set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION, indicating that exceptions will be thrown for errors.
We define the SQL statement to delete a record from the “Users” table where the “id” is 2, and store it in the $sql variable.
Since no results are returned by the deletion query, we use the exec() method of the PDO object to execute the SQL statement.
If the execution of the SQL statement is successful, we echo out a message “Data deleted successfully” to the HTML page.
If an exception is caught, we display an error message that includes the SQL statement and the error message generated by the exception.
Finally, we close the connection to the database by setting the $conn variable to null.

The table will look like this after the record has been deleted:

id	firstname	lastname	email	age	gender	reg_date
1	Matt	LeBlance	[email protected]	55	Male	2023-03-08 20:09:15
3	Jennifer	Anniston	[email protected]	54	Female	2023-03-08 20:09:15

We value your feedback.