Empty A MySQL Table With PHP (And PDO).

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:

1 2 3 4 5 6 7 8 9 10 11 12 13

<?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:

1. I connected to MySQL using the PDO object. I also selected a database called “test”.
2. 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”.
3. I prepared the SQL statement.
4. 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:

1. The TRUNCATE TABLE statement is faster.
2. To empty a MySQL table using this method, your MySQL user account will need to have the DROP privilege.
3. The TRUNCATE TABLE statement can NOT be rolled back.
4. 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.
5. Behind the scenes, this statement is dropping / deleting the table itself before recreating it.
6. 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.