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.
Table of Contents
Difference Between DROP, TRUNCATE and DELETE Commands
The following table shows the what are DELETE, TRUNCATE, DROP Commands and when to use them.
DROP | TRUNCATE | DELETE |
DROP will delete entire table (both rows and structure-columns). | TRUNCATE deletes all the rows in the table and structure(columns) will remain the same. | DELETE is used to delete particular rows based on the condition specified in WHERE clause |
Syntax:DROP TABLE table_name | Syntax:TRUNCATE TABLE table_name | Syntax:DELETE FROM table_name WHERE condition |
DROP is an Data Definition Language (DDL) Command | TRUNCATE is an Data Definition Language (DDL) Command | DELETE is an Data Manipulation Language (DML) Command |
DROP & TRUNCATE are equal in terms of computing. | TRUNCATE is faster than DELETE. | DELETE is slower than TRUNCATE. |
WHERE Clause is not used. | WHERE Clause is not used. | WHERE Clause is used. |
Use this when you don't need the table anymore. | Use this when you want to empty a table. | Use this when you want to delete a certain records from a table. |
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.
id | name | count |
1 | Cooking-Gas | 40 |
5 | Shampoo | 10 |
2 | Milk | 20 |
3 | Books | 14 |
4 | Chocos | 45 |
6 | Eggs | 12 |
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.
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.