How to empty all tables in mysql database

Many a times, during web application development, particularly while testing, you require to empty all the tables in the database. MySql provides you with the handy 'truncate' function, that empties the table and resets the auto-increment value (if set). Or you can use phpMyAdmin - select table and click on 'empty'. If you have multiple tables, select each table and click on empty in phpMyAdmin or have a script for looping it in php to run the truncate query. It gets confusing, if few of the tables need not be reset all the time. Its furthermore cumbersome, if you have few tables where you have to insert some initial records. All along, I have used phpMyAdmin's feature. How many ever the number of tables, I have patiently and carefully, done select-and-empty. Then gone about manually inserting records.

During one of my php / MySql projects at Signature Solutions (a website design and development company in Bangalore, India), I was tackling more than 40 tables in a database. Few of the tables had data that did not need be emptied every time (particularly, ip2nation table had more than 29,000 records). Searching on the net only suggested - either I drop the entire database and recreate or select each of them individually, and empty using phpMyAdmin. Every time I needed to empty the entire database, I had to be very very careful. I finally, managed to put together a script that would do the job as well. So here it is:-

The approach:

1. List all the tables in mysql database and store in an array
2. Create an array with table names that need not be deleted
3. Run the difference of arrays in a loop to truncate each
4. Write custom insert initial values

The php code:

<?php

// CONNECT TO THE DATABASE
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "mydatabase";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql: ' . mysql_error());
mysql_select_db($dbname);

// CREATE A NEW ARRAY TO STORE THE ALL TABLE NAMES
$all_tables = array();

// USE MYSQL'S SHOW TABLES TO GET ALL THE TABLE NAMES
$sql = mysql_query("SHOW TABLES") or die(mysql_error());

while($row = mysql_fetch_array($sql))
{
$all_tables[] = $row[0];
}

// CREATE A NEW ARRAY THAT CONTAINS NAMES OF TABLES THAT NEED NOT BE EMPTIED
$not_to_empty = array('admin', 'countrylist', 'currency', 'templates', 'ip2nation', 'ip2nationcountries', 'settings');

// FIND THE DIFFERENCE IN ARRAYS
$truncate_tables = array_diff($all_tables, $not_to_empty);
sort($truncate_tables);

// RUN A LOOP TO TRUNCATE THE TABLES
for($i=0; $i<count($truncate_tables); $i++)
{
$truncate = mysql_query("TRUNCATE TABLE $truncate_tables[$i]") or die(mysql_error());
if($truncate === true)
{
echo $truncate_tables[$i]." <span style=\"color:#060; font-weight:bold;\">truncated</span><br>";
}
else
{
echo $truncate_tables[$i]." <span style=\"color:#f00;\">could not be truncated</span><br>";
} // END OF IF
} // END OF FOR

// INSERT INITIAL VALUES
$insert_sql = mysql_query("INSERT INTO users (id, first_name, last_name, email, password, status) VALUES ('10000000', 'Signature', 'Solutions', 'sridhar@signature.co.in', '$rand_password', 'active')") or die(mysql_error());

echo ($insert_sql === true) ? "<br><br><strong>DONE.</strong>" : "<br><br>AN ERROR HAS OCCURED. PLEASE RESTORE THE DATABASE FROM THE BACKUP AND TRY AGAIN.";

?>

To dress it up nicely, add some JavaScript before running the php script, that confirms from the administrator if they want to empty of the database:

JavaScript Confirm code:

<script language="javascript" type="text/javascript">

function confirmreset()
{
alert("This option will reset all the data in the database. Before your proceed with the reset, it is strongly recommended that you take a backup of your database. To proceed with the reset, click on the OK button. Click on Cancel button to exit without reset.");

if(confirm("Do you really want to reset the database?"))
{
return true;
document.loginform.submit;
}
else
{
return false;
}
} // END OF FUNCTION

</script>

Sometimes, you are boggled, how simple things can be and how complex we make it.