Simultaneously empty mysql tables

Have you ever been in a situation where you have lots of mysql tables in your database that you want to empty. Probably because you want to test your application with a clean database.
In this tutorial, I’m going to show you how to make an application that allows you to empty multiple mysql tables in one go.

 

Requirements

  • Wampserver
  • Jquery core
  • Text-editor

 

Procedure

As usual, prepare all the requirements. And place them all in one folder. I’m using a framework used for connecting and manipulating the database. It’s called EzSql. You can either follow along using your own method of connecting to mysql. Or you can go ahead and have a little crash course on EzSql, and come back here once you think you can already follow along.

EzSql Documentation

After having all the requirements prepared. Create a new php file, and name it info_schema.php or whichever name you like to name it. What this file does is just to connect to the built-in mysql database called information_schema. When I say built-in, it means that it is already there after you installed mysql. The main-purpose of this database is to store the information about the other database that you create, and that includes information on the tables and its fields. For more information about information_schema, visit this link.

<?php
include('ez_sql/shared/ez_sql_core.php');
include('ez_sql/mysql/ez_sql_mysql.php');

$db = new ezSQL_mysql('root', '', 'information_schema', 'localhost');
?>

Next, create another php file and name it empty_tables.php. Include the info_schema.php that we created earlier and also the jquery core file:

<?php
require_once('info_schema.php');
?>

<script src="jq.js"></script>

Also create a function that would check all the checkboxes. This allows you to check multiple checkboxes at once by just checking the main checkbox with the id of mc. The code is pretty much self-explanatory. We just select all of the checkbox by using the selector input[type=checkbox]. Then we just change the attribute checked to true or false, if the main checkbox is checked all of the input elements with the type of checkbox will also get checked. If the main checkbox is unchecked, all the other checkboxes will also get unchecked.

<script>
$(function(){

$('#mc').click(function(){
	var stat = $(this).attr('checked');
	
	if(stat == true){
		$('input[type=checkbox]').attr('checked', true);
	}else if(stat ==false){
		$('input[type=checkbox]').attr('checked', false);
	}
	});
});
</script>

Next, we check if the the variable database has been set in the url. If it does, we use mysql_real_escape_string() function to strip the string with all the characters which can be harmful to the database. This can help us avoid mysql injection.

<?php if(!empty($_GET['database'])){ $database = mysql_real_escape_string($_GET['database']);

?>

 

<?php } ?>

Inside the empty check is the form that will be used to to select all the tables that needs to be truncated/emptied. Inside the form is a table, which has 2 columns, the table name, and then the checkbox that will be used to truncate the table. Then we performed an sql query to select the following fields: table_schema, table_name. If you carefully examine the information_schema database, you will notice that the table_name field stores all of the names of the tables that you have in each of the database that you have created. And the table_schema field contains the name of the database where that specific table is located. Yup, my explanation is really confusing so just go ahead and explore what’s in the information_schema database.

We then loop through all of those by selecting only the tables in the database that we have specified in the url. The string that is stored by the $database variable.
And then setting the name of the checkbox to be the actual name of the table.

<form name="x" action="empty_tables.act.php" method="post">

<input type="hidden" name="db"  value="<?php echo $database; ?>"> <table border="1"> <tr> <th>Table</th> <th>Truncate<input type="checkbox" id="mc"/></th> </tr> <?php $tables = $db->get_results("SELECT TABLE_SCHEMA, TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '$database'"); ?> <?php foreach($tables as $t){ ?> <tr> <td><?php echo $t->TABLE_NAME; ?></td> <td><input name="<?php echo $t->TABLE_NAME; ?>" type="checkbox"/></td> </tr> <?php } ?> </table> <input type="submit" value="truncate selected tables"/> </form>

Finally, create a file called empty_tables.act.php. This is where all the data from the empty_tables.php will be submitted and process.

<?php
require_once('db_config.php');
$database = $_POST['db'];
$db = new ezSQL_mysql("root", "", "$database", "localhost");
$tables = array();

foreach($_POST as $k=>$p){
	
	$tables[$k] = $k; 	
	$db->query("TRUNCATE TABLE $k");

}

echo 'successfully emptied the following tables:<br/>';
foreach($tables as $t){

	echo '<ul><li>'. $t . '</li></ul>';
}
?>

What this does is just to loop through all the post variable. This includes the name of the database that we are using, that’s why mysql will return a warning every time you execute this script. But it does its job well, since the tables that you checked still gets truncated.

 

Conclusion

I just showed you how to create a simple php application that can be used to simultaneously empty mysql tables.  The script that I have written is not in any way perfect. You might want to improve it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: