Delete several tables with the same prefix

[francoisch]

hello

I need to delete several tables, hosted in the same database, sharing the same prefix.

I found this code into a forum :

$show = "SHOW TABLEs LIKE '".$prefix."%'"; 
$show_res = mysql_query($show,$connexion1) or die(mysql_error()); 
while($row = mysql_fetch_row($show_res)) { 	
$sql = "DROP TABLE '".$row[$num]."'"; 	
mysql_query($sql) or die(mysql_error()); 	
$num++; 	}

I added up a connection to the base, which works well.

As a result, I read only one single line which gives me a single table name.

But, when I run the SHOW TABLE request into PhpMyAdmin, I get a list of tens of tables.

Thank you in advance for your help.

Francois

[eval(BadCode)]

SHOW ___ is an odd kind of query which is based off of information in information_schema (a database of meta information).

You might have better luck using a SELECT query directly into information_schema itself; rather than trying to hack your way to an answer.

SELECT 
  TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='your_database_name' //your database name
  AND TABLE_NAME LIKE 'some_prefix%';

[francoisch]

Thank you for your interesting answer which opens up a new area to me.

I will go and use it.

Thank you again.

Francois