how to delete tables with a prefix

geyids · January 17th, 2008, 01:08 PM

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

alexgeek · January 17th, 2008, 01:21 PM

Try:

Code:

DROP TABLE php_

geyids · January 17th, 2008, 01:29 PM

will that delete all the tables including like this php_zebra

alexgeek · January 17th, 2008, 01:31 PM

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

Code:

DROP TABLE php_*

geyids · January 17th, 2008, 01:32 PM

Code:

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 · January 17th, 2008, 02:18 PM

This php should work, found it on a forum.

Code:


			
<?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 · January 17th, 2008, 02:49 PM

lemmi try it out

geyids · January 17th, 2008, 03:24 PM

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

alexgeek · January 17th, 2008, 03:25 PM

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

Code:


			
$result = mysql_query("SHOW tables");

with:

Code:


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

geyids · January 17th, 2008, 03:50 PM

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

Code:


			
$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 · January 17th, 2008, 04:01 PM

Did you try the code I posted?

geyids · January 17th, 2008, 04:09 PM

yes the whole thing looks like this now

Code:


			
<?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 · January 17th, 2008, 04:57 PM

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

geyids · January 18th, 2008, 02:09 AM

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

alexgeek · January 18th, 2008, 02:17 AM

Try changing:

Code:


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

to:

Code:


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

Otherwise I dont know.

geyids · January 19th, 2008, 03:23 PM

still no output. just let it go i will work with phpMyAdmin