Drop MySQL Tables


It is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.

Syntax

Here is a generic SQL syntax to drop a MySQL table −

DROP TABLE table_name ;

Dropping Tables from the Command Prompt

To drop tables from the command prompt, we need to execute the DROP TABLE SQL command at the mysql> prompt.

Example

The following program is an example which deletes the tutorials_tbl

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

Dropping Tables Using PHP Script

PHP uses mysqli query() or mysql_query() function to drop a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to drop a table.

2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to drop a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head><title>Dropping MySQL Table</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query("Drop Table tutorials_tbl")) {
            printf("Table tutorials_tbl dropped successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not drop table: %s<br />", $mysqli->error);
         }

         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Table tutorials_tbl dropped successfully.
Advertisements