In production environment we test the web applications by inserting test data to the database tables. Once all the testing is completed we need to install our web application on the real server environment. So we have to remove all test data and install a fresh database. Normally you can do this by exporting a database backup with only the structure without including data from tools like phpMyAdmin or Webmin. But if you have database tables which is used for settings, configurations and pre required data you cannot backup with the above option. Only option you have is to backup tables one by one.

Since this is a difficult task i have provided a script that will delete and clean up all the data from database tables apart from the tables you don’t want to clear.

Sql provides a keyword called truncate which will empty the table contents once used on a specific table. Following code contains the syntax to use truncate.

    mysql_query("truncate table_name");

Following steps will guide you to use the php clear db script to clear the unwanted table data values from your database.

 

Step 1 – Provide connection details to your mysql server.

 

Step 2 – Specify the name of the database you want to clear.

 

Step 3 – Define the tables you want to preserve data using the $preserve_tables array.

 

Step 4 – Execute the script and it will clear all the data in the tables apart from data in the tables mentioned in $preserve_tables array.

 

/*
*  Connect to Mysql Database
*/

$con=mysql_connect('localhost','username','password');
mysql_select_db('database_name');

/*
*  Get all the tables in the database
*/ 

$database_tables = mysql_query('show tables');

/*
*  Define the tables to preserve data
*/

$preserve_tables = array('test1');

/*
*  Check the tables to clean up and clear all the data
*/

while($row=mysql_fetch_array($database_tables)){

        $table = trim($row[0]);
        if(!(in_array($table,$preserve_tables))){
                mysql_query("truncate ".$table);
        }

}