Empty database in MySQL and PHP?

Question

Is it possible to empty an entire database by connecting to it with PHP and giving a command?

Answer 1

You can get table names with

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your db'

And make the truncate,

even, you can make

SELECT 'TRUNCATE TABLE ' + table_name + ';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your db'

And then iterate the resultset executing the string query for each record.

Answer 2

Yes. Basically, you need to get a list of all Tables in the Database, then iterate over that list and issue a TRUNCATE TABLE $tablename for each entry.

This looks like a decent enough implementation: Truncate all tables in a MySQL database

Answer 3

The simplest way to do it, if you have the privileges, is:

DROP DATABASE dbName;
CREATE DATABASE dbName;
USE DATABASE dbName;

The alternative is to query the information_schema database for triggers, stored routines (procedures and functions), tables, views and possibly something else, and drop them individually.

Even after this, your database might still not be in the same state as a newly created one, since it might have a custom default character set and collation set. Use ALTER DATABASE to change that.

As features keep getting added (events...) you'll have more and more work this way. So really, the only way to completely empty the database is to drop it and re-create it.