Home PHP MySQL PHP MySQL DELETE, TRUNCATE, DROP Table

PHP MySQL DELETE, TRUNCATE, DROP Table

How To Delete, Truncate, And Drop MySQL Table Using PHP In XAMPP

By Sravan Kumar
Published: Last Updated on 1.3k views

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.

DROPTRUNCATEDELETE
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) CommandTRUNCATE is an Data Definition Language (DDL) CommandDELETE 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.
DROP Vs TRUNCATE Vs DELETE

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.

idnamecount
1Cooking-Gas40
5Shampoo10
2Milk20
3Books14
4Chocos45
6Eggs12
Records From sales table
View Table Records In MySQL Database
View Table Records In MySQL Database

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.

DELETE Rows From A MySQL Table Using PHP
DELETE Rows From A MySQL Table Using PHP

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.

View Records In MySQL Table
View Records In MySQL Table

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.

TRUNCATE A MySQL Table Using PHP
TRUNCATE A MySQL Table Using PHP

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.

DROP A MySQL Table Using PHP
DROP A MySQL Table Using PHP

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.

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More