WordPress.org

1. islandcastaway
   Member
   Posted 11 months ago #

   Greetings,

   I am looking for a sql query or php function I could run to delete multiple tables across a WPMU database.

   So the end result would be:

   search WPMU database for tables like wp_1_table to wp_999_table and delete them.

   Cheers

2. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 11 months ago #

   Towards what end?

   I ask because if you just delete tables, you can break things. So what's the end goal?

3. islandcastaway
   Member
   Posted 11 months ago #

   The end goal is to remove old tables created by old inactive plugins.

   I understand about removing tables and breaking things. This is not an issue for me, but thanks for the heads up.

   I can kindof picture the code in my head in old school basic:

   for $x = 1 to 999
   $table_name = 'wp_'.$x.'_table';
   $wpdb->query('DROP TABLE IF_EXISTS '.$table_name);
   next $x

   Don't laugh to hard. I just figured there is a dedicated sql query or easy php function to do this available.

   Cheers

4. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 11 months ago #

   Solid :) Just making sure.

   This is not actually a WordPress thing. I mean, yes, it is in that the plugins should clean up after themselves, but it's not something you want to RUN via WordPress.

   Even PHPMyAdmin doesn't let you drop wildcard tables. If you really wanted to do that, it'd be a mySQL command line thing.

   I'd pop open phpMyAdmin, check the tables I want to drop, and drop 'em all that way. AFTER making a backup of course :)

5. islandcastaway
   Member
   Posted 11 months ago #

   I understand that this is not a WordPress specific thing.

   I just figured someone has done something like this already.

   Droping the tables from searching thru 4000+ tables manually isn't gonna be happening.

   Thanks for your input. But like I said it would be a sql query(command line) or php script.

   Cheers

6. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 11 months ago #

   That's the trouble.

   show tables like ‘wp_%_foobar’; works GREAT.

   DROP TABLE ‘wp_%_foobar’; doesn't work at all.

   I would do the show table, copy the list to a textpad/notepad++ file. Edit it to be 'drop table' and paste that in if I had to.

7. islandcastaway
   Member
   Posted 11 months ago #

   Thanks for the % wildcard thingy. Great idea.

   I melted my brain for a few mins and came up with this for a standalone php file in the root directory:

   <?php
   // Include WordPress
   include_once('wp-config.php');
   include_once('wp-load.php');
   include_once('wp-includes/wp-db.php');
   
   //configs
   $WordPressDataBasePrefix = 'wp_'; //change to daatabase prefix
   $tableNameToDelete = '_table'; //change to table to drop
   $WPMUtotalSites = '999'; //change 999 to number of WPMU sites
   
   //the death loop
   $i = 0;
   for ($n=0; $n<$WPMUtotalSites; $n++) {
   $table_name = $WordPressDataBasePrefix . ++$i . $tableNameToDelete;
   $wpdb->query('DROP TABLE IF_EXISTS '.$table_name);
   }
   ?>

   I'm 99% sure this will work but im havin another beer first.

   Cheers

8. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 11 months ago #

   And a reeeeealy good backup first, I hope!

9. islandcastaway
   Member
   Posted 11 months ago #

   Ok, I finally got around to trying it and I have a syntax error in my sql query statement.

   I changed:
   $wpdb->query('DROP TABLE IF_EXISTS '.$table_name);
   to:
   $wpdb->query("DROP TABLE IF EXISTS $table_name");

   It workx perfect.

   Someday ill get around to making this into a plugin that you can input the tables or something.

   Cheers

Reply

You must log in to post.