Hi,
I was working on a project, had to delete multiple tables from a database.
I had 2 options , either delete individual tables using phpmyadmin or write a procedure for the same.
Since i am lazy, i did not liked the first option and since i am not a sql admin and didn’t have the time to learn and write a procedure to delete multiple tables , i couldn’t use the second option also.
So i created a php script to do the same, it worked for me. Hope it will work for you people also.
#######################
<html>
<head>
<title>Deleting MySQL Tables</title>
</head>
<body>
<?php
$dbhost = ‘localhost’;
$dbuser = ‘dnusername’;
$dbpass = ‘password’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);
$dbname = ‘databasename’;
/* query all tables and gets the tables with the matching string*/
$sql = “SHOW TABLES FROM $dbname like “somestring”;
if($result = mysql_query($sql)){
/* add table name to array */
while($row = mysql_fetch_row($result)){
$found_tables[]=$row[0];
}
}
else{
die(“Error, could not list tables. MySQL Error: ” . mysql_error());
}
foreach($found_tables as $table_name){
$sql = “DROP TABLE $dbname.$table_name”;
if($result = mysql_query($sql)){
echo “Success – table $table_name deleted.”;
}
else{
echo “Error deleting $table_name. MySQL Error: ” . mysql_error() . “”;
}
}
mysql_close($conn);
?>
</body>
</html>
##############################
If anybody knows SQL command to do the same, do tell me, it will be useful and i don;t have to write such a big php script.
Bye
Devenix's Weblog 