how to delete tables with a prefix

geyids · Jan 17th, 2008, 17:08

How do I delete tables with a certain prefix eg that starts with php_ all at once. Thanks

alexgeek · Jan 17th, 2008, 17:21

Try:

DROP TABLE php_

geyids · Jan 17th, 2008, 17:29

will that delete all the tables including like this php_zebra

alexgeek · Jan 17th, 2008, 17:31

That was wrong actually.
I think this should work but I'm not entirely sure.

Code: Select all

DROP TABLE php_*

geyids · Jan 17th, 2008, 17:32

Code: Select all

mysql> drop table phpbb_*
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '*' at
 line 1

alexgeek · Jan 17th, 2008, 18:18

This php should work, found it on a forum.

PHP: Select all


<?php 
//assuming you're connected and have selected the db already 
$result = mysql_query("SHOW tables"); 
for ($i = 0; $i < mysql_num_rows($result); $i++) 
{ 
 $tablename = mysql_result($result, $i, 0); 
 if (substr($tablename, 0, 3) == "php") 
 { 
 if (mysql_query("drop TABLE ".$tablename)) print "droped ".tablename."!<br />"; 
 } 
} 
?>

geyids · Jan 17th, 2008, 18:49

lemmi try it out

geyids · Jan 17th, 2008, 19:24

nothing happens the page is just blank doesn't echo the result droped so nothing has happend and the tables are still there

alexgeek · Jan 17th, 2008, 19:25

You have put in your connection string yeah?
Try replacing
:

PHP: Select all


$result = mysql_query("SHOW tables");

with:

PHP: Select all


$result = mysql_query("SHOW tables") or die(mysql_error());

geyids · Jan 17th, 2008, 19:50

This is how i connected to my database. Is it right? I still get a blank page

PHP: Select all


$db_host = "localhost";
    $db_user = "my_user";
    $db_password = "my_passwd";
    $db_name = "my_name";
    $connection = @mysql_connect($db_host, $db_user, $db_password) or die("error connecting");
    mysql_select_db($db_name, $connection);

alexgeek · Jan 17th, 2008, 20:01

Did you try the code I posted?

geyids · Jan 17th, 2008, 20:09

yes the whole thing looks like this now

PHP: Select all


<?php 
    $db_host = "localhost";
    $db_user = "my_user";
    $db_password = "my_passwd";
    $db_name = "my_name";
    $connection = @mysql_connect($db_host, $db_user, $db_password) or die("error connecting");
    mysql_select_db($db_name, $connection);
    //assuming you're connected and have selected the db already 
    $result = mysql_query("SHOW tables") or die(mysql_error()); 
    for ($i = 0; $i < mysql_num_rows($result); $i++) 
    { 
     $tablename = mysql_result($result, $i, 0); 
     if (substr($tablename, 0, 3) == "phpbb_") 
     { 
     if (mysql_query("drop TABLE ".$tablename)) print "droped ".tablename."!<br />"; 
     } 
    } 
    ?>

alexgeek · Jan 17th, 2008, 20:57

You're not getting any error?
Remove the @ before the mysql_connect.

geyids · Jan 18th, 2008, 06:09

no error at all. i have removed the @ and still thesame. a blank page

alexgeek · Jan 18th, 2008, 06:17

Try changing:

PHP: Select all


  $connection = @mysql_connect($db_host, $db_user, $db_password) or die("error connecting");
    mysql_select_db($db_name, $connection);

to:

PHP: Select all


mysql_connect($db_host, $db_user, $db_password) or die("error connecting");
    mysql_select_db($db_name);

Otherwise I dont know.