0

I was wonder if there is any way to delete all the table of my bdd exept the one named "keep" in php. Something like this:

$sql = 'DROP TABLE * EXEPT 'keep' ';

Also is there any command in php to create a new bdd (not a table) or do I have to do it in phpmyadmin ?

Thanks in advance !

Yuniru'
  • 13
  • 5
  • There is no such a command. If you need to exclude tables, get all tables, loop through them and check their name. If their name is not excluded, drop table, otherwise skip. – Charlotte Dunois Apr 18 '16 at 16:06
  • I think you can look into `LOCK TABLE` I've not used it personally so someone else might have a better idea but I believe you can lock a table down to say read only which would prevent you dropping the table while you dropped the rest, and then unlock it afterwards -- preserving the table you wanted to keep. – Mikey Apr 18 '16 at 16:08
  • A) Get list of tables with `SHOW TABLES`. B) Remove from this list the one you don't want. C) Issue `DROP TABLE` statements. Not hard. – tadman Apr 18 '16 at 16:15

1 Answers1

0

You can create a "drop table" sql file by accessing the information_schema.tables table and selecting from it for the appropriate schema.

select concat('drop table ','table_name',';')
  from information_schema.tables
 where table_schema = '[my_schema_name]'
   and table_name != '[the_table_I_don't_want_to_drop]'
INTO OUTFILE '[FILESPEC]';

If you would like to list a group of tables not to drop, simply change the and clause to "and table_name not in ('table1','table2',...'tableN')".

You could do the same thing in PHP. You would simply loop through the results and execute the resultant string.

T Gray
  • 712
  • 4
  • 10