MYSQL ( Lesson No. XIII )

MYSQL Delete Query

If you want to delete a record from any MySQL table then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax:


Here is generic SQL syntax of DELETE command to delete data from a MySQL table:

DELETE FROM table_name [WHERE Clause]

• If WHERE clause is not specified then all the records will be deleted from the given MySQL table.
• You can specify any condition using WHERE clause.
• You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data From Command Prompt

This will use SQL DELETE command with WHERE clause to delete selected data into MySQL table tutorials_tbl

Example:

Following example will delete a record into tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3; Query OK, 1 row affected (0.23 sec) mysql> Deleting Data Using PHP Script

You can use SQL DELETE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.

Example:

Try out following example to delete a record into tutorial_tbl whose tutorial_id is 3.

<?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 tutorials_tbl WHERE tutorial_id=3'; mysql_select_db('TUTORIALS'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not delete data: ' . mysql_error()); } echo "Deleted data successfully\n"; mysql_close($conn); ?>

---