How do you delete all tables in a database in MySQL?

Sometimes you just have to start over.  I had a database that had a bunch of tables in it, almost 9000. The problem was that I didn’t know if they were the right tables.  I had a mysqldump files that did have the right tables, but I didn’t want any extras around. So I wanted to start over with the fresh import.  In MySQL you would normally just do a drop database x; create database x; and you are done.  However, I didn’t have a user with the rights to create a database.

With a small database you can do a command line like

SELECT group_concat(table_name order by table_name desc separator ‘ ‘) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘DATABASENAME’ ;

and then type drop table  and paste in the resulting table list.  This does not work with anything more than a dozen or so tables. This question gets to be very tricky when you are dealing with a LARGE database.

I found an article from back in 2006 that had a rather unique idea and I’ve expanded it to be faster and to work better with extremely large database.  You will still have to wait a bit for completion, but when you need to remove a large number of tables  from a database, try this command line:

mysqldump -u[USERNAME] -p[PASSWORD] [DATABASE]–add-drop-table –no-data –skip-lock-tables | grep ^DROP | mysql -A -u[USERNAME] -p[PASSWORD] [DATABASE]

MySQL dump will create a list of drop commands for every table in the database, the grep will isolate those and feed them back into mysql for execution.  It’s a rather slick solution and while you may still have to wait for 5 minutes (or even much more) as all the tables are listed and dropped, it will get the job done.

 

Find what week of the month a date is in PHP

{EAV_BLOG_VER:cf05756ccfd5297d}

The need for this routine comes up every now and then and it confuses people because they thing date(“W”) will do the trick.

Once they realize that’s the week of the year, they start to get all elaborate in finding ways to get which week in the month it is.

Applying a little logic, you can come up with the simple solution of subtracting away all of the weeks before the start of the month an the remainder is your answer

 

Like so:

$weekNum = date(“W”) – date(“W”, strtotime(date(“Y-m-01”, now()))) + 1;

(Drop the + 1 if you want it to be zero based.