This is a tutorial on how to completely empty a MySQL table using PHP. In certain cases, you may want to delete everything in the table and start afresh.
In this tutorial, I will be using PHP’s PDO object to interface with my MySQL database:
<?php //Instantiate our PDO object and create a new MySQL connection. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', ''); //Our SQL statement. This will empty / truncate the table "videos" $sql = "TRUNCATE TABLE `videos`"; //Prepare the SQL query. $statement = $pdo->prepare($sql); //Execute the statement. $statement->execute();
A step-by-step guide of the code snippet above:
- I connected to MySQL using the PDO object. I also selected a database called “test”.
- I created the SQL statement that I want to run. In this case, I am using the TRUNCATE TABLE statement to empty a table called “videos”.
- I prepared the SQL statement.
- Finally, I executed the statement, thereby emptying the table in question.
Note that there are a few differences between using the TRUNCATE statement and simply deleting all rows with the DELETE statement:
- The TRUNCATE TABLE statement is faster.
- To empty a MySQL table using this method, your MySQL user account will need to have the DROP privilege.
- The TRUNCATE TABLE statement can NOT be rolled back.
- If you truncate a MySQL table, the AUTO_INCREMENT key will be reset. i.e. The next autoindex for your PRIMARY KEY will be set back to 1. This is not the case with DELETE.
- Behind the scenes, this statement is dropping / deleting the table itself before recreating it.
- This statement will not return any meaningful data. i.e. It won’t return the number of rows that have been deleted. It’ll simply tell you that 0 rows have been affected.
Hopefully, this article helped!