How to Empty All Tables in a MySQL Database with PHP

Home » Information Technology » How to Empty All Tables in a MySQL Database with PHP

This is a simple PHP script to empty all tables in a MySQL database. This script first will list all tables in a database using ‘SHOW TABLES‘ statement then it will empty  each table using  ’TRUNCATE TABLE‘ statement.

$con = mysql_connect('dbhost','dbuser','dbpassword');

if (!$con) die("Can't connect to database!");

mysql_select_db('dbname');

$res = mysql_query('SHOW TABLES', $con);

while ($row = mysql_fetch_array($res, MYSQL_NUM))
{
  $res2 = mysql_query("TRUNCATE TABLE `$row[0]`", $con);
}

About TRUNCATE Statement

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any foreign key constraints that reference the table. If there are no foreign key constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.

Related post:

• How to Set Time Zone in PHP and MySQL (2)
• How to Send Message to Google Cloud Messaging (GCM) Server Using JSON and PHP (5)
• How to Add Wordpress Pages Without Adding Them to Navigation Menu (3)
• How to Crop Image Using JavaScript and PHP (24)
• How to Create Facebook Application with PHP for Beginner (27)