PHP MySQL: Delete Data

 PHP MySQL: Delete Data

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

We will use the tasks the table in the sample database for the demonstration. Before going forward with this tutorial, you should follow the PHP MySQL create table tutorial to create the tasks table and insert sample data for practice.

See the following tasks table.

To delete data in a table, you use the following steps:

• Connect to the MySQL database by creating a new instance of the PDO object.
• Construct a DELETE statement to delete a row, multiple rows, or all rows in a table. If you want to delete all rows in a big table quickly and more efficiently, you use the TRUNCATE TABLE statement.
• Execute the DELETE statement by calling the exec()  method of the PDO object or the execute() method of the PDOStatement object.

PHP MySQL Delete data examples

PHP MySQL: delete a single row example

To delete a single row in a table, you use the DELETE a statement with a WHERE clause that specifies which row to delete.

The following script deletes a row with id 2 in the tasks table.

<?php

/**
 * PHP MySQL Delete Data Demo
 */
class DeleteDataDemo {

    const DB_HOST = 'localhost';
    const DB_NAME = 'classicmodels';
    const DB_USER = 'root';
    const DB_PASSWORD = '';

    /**
     * PDO instance
     * @var PDO 
     */
    private $pdo = null;

    /**
     * Open a database connection to MySQL
     */
    public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME);
        try {
            $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }

    /**
     * Delete a task based on a specified task id
     * @param int $id
     * @return bool true on success or false on failure
     */
    public function delete($id) {

        $sql = 'DELETE FROM tasks
                WHERE task_id = :task_id';

        $q = $this->pdo->prepare($sql);

        return $q->execute([':task_id' => $id]);
    }

    /**
     * close the database connection
     */
    public function __destruct() {
        $this->pdo = null;
    }

}

$obj = new DeleteDataDemo();
// delete id 2
$obj->delete(2);

How it works.

In the __construct()  method of the DeleteDataDemo class, we connect to the MySQL database by initiating an instance of the PDO class, and in the __destruct()  the method, we close the database connection.

The delete()  method accepts the id as the argument. First, we call the prepare()  method of the PDO object to prepare the DELETE statement for execution, and then we pass an array that contains values corresponding to the named placeholders in the DELETE statement to the execute() method of the PDOStatement object.

To delete the task with id 2, we create an instance of the DeleteDataDemo class and call the delete() method.

PHP MySQL: delete all rows in a table examples

There are two ways to delete all rows in a table:

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

The following deleteAll() method deletes all rows in the tasks table using the DELETE statement:

<?php
/**
 * Delete all rows in the tasks table
 */
public function deleteAll(){
	$sql = 'DELETE FROM tasks';
	return $this->pdo->exec($sql);
}

The following truncateTable() the method removes all rows in the tasks table as well as resets its autoincrement column’s value:

<?php
/**
 * Truncate the tasks table
 */
public function truncateTable() {
	$sql = 'TRUNCATE TABLE tasks';
	return $this->pdo->exec($sql);

}

You can download the source code of this tutorial via the following link:

Download PHP MySQL Delete Source Code

In this tutorial, we have shown you how to delete data from a MySQL table using PHP PDO.