I was in the middle of upgrading the Wasington Parkour forum software to the new phpBB 3.0 when I got distracted by a MySQL limitation I wasn’t previously aware of: it seems you can’t drop more than one table at a time in the MySQL command line interface. This curious limitation begs for a command-line script solution, and indeed I was not surprised to find that there are plenty of them out there. As usual, though, I’m reinventing the wheel for my own entertainment.
For today’s exercise, I experimented with PHP on the command line (PHP CLI). Some of the features I wanted to include weren’t in the scripts I found elsewhere, so I built them in order to make this useful for my current phpBB upgrade and on into the future for various other situations:
- Allow the user to store db login information in the script if he really wants to, but if he’s a security-conscious and relatively sane person, leave it empty in the script and prompt him for the login information when the script is called.
- Allow the user to specify table prefixes so only certain tables are deleted. When I experienced epic failure on my first attempted phpBB 2 -> phpbb3 conversion, I had to go back and remove all the bad tables that had been created. They were prefixed with phpbb3_ while the old tables were prefixed with phpbb_. Obviously I wanted to leave the old ones alone.
- Provide some useful reporting on any errors encountered and results of the batch drop.
So, here’s what I have for you today: a PHP CLI script to batch drop all your unwanted MySQL tables (after the fold).