PHP MySQL: Delete Data

Summary: in this tutorial, you will learn how to delete data from a MySQL database table using PHP PDO.

To delete data from a table, you follow these steps:

• First, connect to the MySQL database.
• Second, prepare a DELETE statement.
• Third, execute the DELETE statement.

Deleting a row from a table

The following script deletes the task with the id 3 from the tasks table:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'delete from tasks where id =:id';
    $stmt = $conn->prepare($sql);
    $stmt->execute([':id' => 3]);
} catch (PDOException $e) {
    die($e);
}Code language: HTML, XML (xml)

Verify the delete

The following statement retrieves all rows from the tasks table:

select * from tasks;Code language: JavaScript (javascript)

Output:

+----+-------------------------+-----------+
| id | title                   | completed |
+----+-------------------------+-----------+
|  1 | Learn PHP MySQL         |         0 |
|  2 | Build a Web Application |         0 |
+----+-------------------------+-----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Deleting all rows from a table

There are two ways you can delete all rows from a table:

• Execute a DELETE statement without a WHERE clause.
• Execute a TRUNCATE TABLE statement.

To execute the DELETE or TRUNCATE TABLE statement, you can call the exec() method of the PDO object.

The following script deletes all rows from the tasks table using the DELETE statement:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'delete from tasks';
    $stmt = $conn->exec($sql);
} catch (PDOException $e) {
    die($e);
}Code language: HTML, XML (xml)

The following script deletes all rows from the tasks table using the TRUNCATE statement:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'trunate table tasks';
    $stmt = $conn->exec($sql);
} catch (PDOException $e) {
    die($e);
}
Code language: PHP (php)

Summary

• Call the exec() method of the PDO object to execute the DELETE statement to delete one or more rows from a table.
• Call the exec() method of the PDO object to execute the TRUNCATE TABLE statement to delete all rows from a table.