MySQL DELETE Statement

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

You can execute this command in the mysql> command prompt or PHP script.

Syntax

The following is the general syntax for the SQL DELETE statement to delete data from a MySQL data 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 from a single table in one operation.

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

Delete data from the command line

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

Online example

The following example will delete w3codebox_tbl table w3codebox_id is3 record:

mysql> use w3codebox;
Database changed
mysql> DELETE FROM w3codebox_tbl WHERE w3codebox_id=3;
Query OK,　1　row affected (0.23　sec)

Use PHP script to delete data

PHP uses the mysqli_query() function to execute SQL statements, You can use or not use the WHERE clause in the SQL DELETE command.

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

Online example

The following PHP example will delete w3codebox_tbl table w3codebox_id is 3 record:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8");
　
$sql = 'DELETE FROM w3codebox_tbl
　　　　　　　　WHERE w3codebox_id=3';
　
mysqli_select_db($conn, 'w3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval )
{
　　　　die('Unable to delete data: ' . mysqli_error($conn));
}
echo 'Data deletion successful!';
mysqli_close($conn);
?>