MySQL DELETE Statement

You can use the SQL DELETE FROM command to delete records from a MySQL database table.

You can execute this command at the mysql> command prompt or within a PHP script.

Syntax

The following is the general syntax for the SQL DELETE statement to delete data from a MySQL database table:

DELETE FROM table_name [WHERE Clause]

• If the WHERE clause is not specified, all records in the MySQL table will be deleted.
• You can specify any condition in the WHERE clause.
• You can delete records in a single table at once.

The WHERE clause is very useful when you want to delete specific records from the table.

Deleting Data from the Command Line

Here, we will use the WHERE clause in the SQL DELETE command to delete selected data from the MySQL table tutorialpro_tbl.

Example

The following example will delete the record from the tutorialpro_tbl table where tutorialpro_id is 3:

DELETE Statement:

mysql> use tutorialpro;
Database changed
mysql> DELETE FROM tutorialpro_tbl WHERE tutorialpro_id=3;
Query OK, 1 row affected (0.23 sec)

Deleting Data Using a PHP Script

PHP uses the mysqli_query() function to execute SQL statements, which can be used with or without the WHERE clause in the SQL DELETE command.

This function has the same effect as executing SQL commands in the mysql> command prompt.

Example

The following PHP example will delete the record from the tutorialpro_tbl table where tutorialpro_id is 3:

MySQL DELETE Clause Test:

<?php
$dbhost = 'localhost';  // mysql server hostname
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

$sql = 'DELETE FROM tutorialpro_tbl
        WHERE tutorialpro_id=3';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not delete data: ' . mysqli_error($conn));
}
echo 'Data deleted successfully!';
mysqli_close($conn);
?>