MySQL DELETE statement

MySQL DELETE statement

You can use SQL's DELETE FROM command to delete records from mySQL data sheets.

You can execute the command in a mysql?command prompt or PHP script.

Grammar

Here is the general syntax for the SQL DELETE statement to remove data from the MySQL data table:

DELETE FROM table_name [WHERE Clause]

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

The WHERE clause is useful when you want to delete the records specified in the data table.

Remove the data from the command line

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

Instance

The following instance removes w3cschool_tbl record w3cschool_id 3 in the table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> DELETE FROM w3cschool_tbl WHERE w3cschool_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Use the PHP script to delete the data

PHP uses mysql_query() function to execute SQL statements, which you can use in SQL DELETE commands or not use where clauses.

This function has the same effect as the mysql?command to execute sql commands.

Instance

The following PHP instances will delete w3cschool_tbl record w3cschool_id 3 in the table:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM w3cschool_tbl
        WHERE w3cschool_id=3';

mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>