Delete Record From Datatbase Using MYSQL

 The SQL DELETE command is used to delete rows that are no longer required from the database tables. It deletes the whole row from the table. Delete command comes in handy to delete temporary or obsolete data from your database.The DELETE command can delete more than one row from a table in a single query. This proves to be advantages when removing large numbers of rows from a database table.

 Once a row has been deleted, it cannot be recovered. It is therefore strongly recommended to make database backups before deleting any data from the database. This can allow you to restore the database and view the data later on should it be required.

Syntax

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]

• If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
• You can specify any condition using the 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 the Command Prompt

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

Example

The following example will delete a record from the 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 a PHP Script

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

Example

Try the following example to delete a record from the tutorial_tbl whose tutorial_id is 3.

<?php
   $dbhost = 'localhost';
   $dbuser = 'root';
   $dbpass = '';
   $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);
?>