PHP MySQL DELETE, TRUNCATE, DROP Table

In this guide, we will discuss how to perform DELETE, TRUNCATE, And DROP operations on a MySQL database table using PHP in XAMPP in Linux.

Before getting into the topic, let us understand the differences between the Delete, Truncate, and Drop commands.

Difference Between DROP, TRUNCATE and DELETE Commands

The following table shows the what are DELETE, TRUNCATE, DROP Commands and when to use them.

In a nutshell,

• DELETE will delete particular records,
• TRUNCATE will delete all records but will not delete the table,
• and DROP will delete the entire table.

Prerequisites

For demonstration purpose, I have created a table called "sales" in a MySQL database called "my_company" with following records.

Heads Up: In XAMPP, the database and table names are case insensitive. It will consider upper/lower case as lowercase only.

If you haven't created a database and table, refer the following guide.

• Create MySQL Database And Table Using PHP In XAMPP

Steps To Write A PHP Code

1. Specify servername, username, password and database name in your PHP code.

2. Create a connection using mysqli_connect() function.

Code:

$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

3. Check the Connection using the mysqli_connect_error() function specified in an if condition. This function will represent an error, if the connection is failed. This is an optional step.

4. Specify the SQL Query to delete particular row/rows from a table using DELETE/DROP/TRUNCATE commands.

5. Use the mysqli_multi_query() function to check if the values deleted or not.

Syntax:

mysqli_multi_query($connection, $query)

Here,

• $connection specifies the connection details,
• $query is the sql query.

If this is True, then the record is deleted. Otherwise it will return an error. We can check the error by using the mysqli_error($connection) function.

6. Close the connection by using the mysqli_close() function.

Syntax:

mysqli_close($connection);

Let's see discuss the usage of each command with an example.

DELETE Rows From A MySQL Table Using PHP

The DELETE command is used to delete a particular row from the table.

Query Syntax:

DELETE FROM table_name where condition;

Example Code:

In this example, we will delete the row where the id=3.

Create a plain text file named delete.php under /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.

<?php
//specify the server name
$server_name = "localhost";

//specify the username
$user_name = "root";

//specify the password - Here, the password is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//delete record with id-3
$query = "DELETE FROM sales where id=3";

if (mysqli_multi_query($connection, $query)) {
  echo "record deleted Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/delete.php. You will see an output like this in your browser window.

You can verify if the row is really deleted from PhpMyAdmin dashboard.

Open phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL from the browser.

Click on Browse to see the remaining records.

As you can see, the row with id=3 is gone.

TRUNCATE A MySQL Table Using PHP

The TRUNCATE command is used to delete all rows from the MySQL database table.

Query Syntax:

TRUNCATE TABLE table_name;

Example Code:

In this example, we will delete all rows.

Create a text file called truncate.php with following contents.

<?php
//specify the server name
$server_name = "localhost";

//specify the username
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//delete all records
$query = "TRUNCATE table sales";


if (mysqli_multi_query($connection, $query)) {
  echo "Truncated Successfull";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Navigate to http://localhost/truncate.php file from your web browser. You will see "Truncated Successful" message in the browser window.

Go to phpMyAdmin dashboard and click Browse to see if there are any records exists.

As you can see in the above screenshot, all records are deleted.

DROP A MySQL Table Using PHP

The DROP command is used to delete an entire table.

Query Syntax:

DROP TABLE table_name;

Example Code:

In this example, we will delete the entire table along with its structure (rows and columns).

Create a text file called drop.php with following contents:

<?php
//specify the server name
$server_name = "localhost";

//specify the username
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

$query = "DROP table sales";


if (mysqli_multi_query($connection, $query)) {
  echo "Dropped Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/drop.php and you will see "Dropped Successfully" message.

Go to phpMyAdmin and check if the table is exist or not.

Yes, there is no such table called "sales" in my_company database. We just deleted it using DROP command.

Conclusion

In this step guide, we discussed about three MySQL commands namely DELETE, TRUNCATE and DROP. Using these trio commands, we showed you how to delete a single row from a MySQL table, and delete all rows in a table and finally delete the entire table using PHP.