Occassionaly when I'm working on a site I might append an entirely new set of database tables on an existing website. After a while the database might become so large that it's neccessary to drop older tables.

Tables that share the same prefix can be dropped with the following 2 part method:

In this example, the database table prefix is _elgg

First run the following SQL command:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name';

This will return a bunch of commands to drop each individual table. Running each of these SQL queries will infact solve this issue, but we can go one step further to automate things a little more by running the following query:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name' 
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

The same can also be achieved directly within the command line by running the following:

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`

Sometimes when trying to log into the wordpress back end I get the dreaded whitescreen of death. This is usually caused by a plugin. The solution is usually realtively painless: disable all the plugins. This fixes the admin login whitescreen issue. Plugins can then be enabled individually, checking to see which plugin breaks the login page.

The following changes the relationships between worldpress posts and their category, although it can be modified for other uses.

the example changes posts in category ID #10 and places them in category ID #20

UPDATE wp_term_relationships 
SET term_taxonomy_id = 
   (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id =10)WHERE term_taxonomy_id =
   (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id =20)