{"id":96,"date":"2011-03-03T13:10:22","date_gmt":"2011-03-03T11:10:22","guid":{"rendered":"http:\/\/www.zulfikar.co.uk\/blog\/?p=95"},"modified":"2011-03-03T13:10:22","modified_gmt":"2011-03-03T11:10:22","slug":"mysql-dropping-multiple-tables-joomla","status":"publish","type":"post","link":"http:\/\/zulfikar.co.uk\/blog\/mysql-dropping-multiple-tables-joomla\/","title":{"rendered":"MySQL – dropping multiple tables – Joomla"},"content":{"rendered":"

Ever tried to DROP multiple tables from a MySQL database which all start with a specific prefix? For example, a default Joomla install adds in a number of tables all beginning with a prefix JOS_<\/strong>, also whenever you configure MySQL to create your own site it is best practice to create ideally a new\u00a03 letter prefix for your site database\u00a0e.g. ABC_<\/strong><\/p>\n

Even using the GUI phpMyAdmin is a pain as you’d have to point and click each table and select the drop icon, fancy doing that 50 times??? You would have thought you could use a wildcard prefix and the sql DROP command e.g. DROP table WHERE table LIKE ‘JOS_%’<\/p>\n

But NO, that doesn’t work. Instead it is a 2 step process, FIRST use a CONCAT command to generate a new DROP command. Within an sql interface execute the following command (feel free to copy it!)<\/p>\n

SELECT CONCAT( ‘DROP TABLE ‘, GROUP_CONCAT(table_name) , ‘;’ ) AS statement FROM information_schema.tables WHERE table_name like ‘JOS_%’;<\/strong><\/p>\n

Oh yeah you MUST replace JOS_ in the command above with your prefix or suffix even (just move that % around!). Rememeber the above command doesn’t actually drop or delete anything,\u00a0it just gives you a text output as thus…<\/p>\n

DROP TABLE jos_banner,jos_bannerclient,jos_bannerfinish,jos_categories,jos_components, etc. etc.<\/strong><\/p>\n

Which you can now use to delete those pesky tables, copy the above output and simply execute it within an sql interface again. It’s this 2nd bit that actively deletes the tables!!!<\/p>\n

BANG – job done! Any probs drop me a line.<\/p>\n","protected":false},"excerpt":{"rendered":"

Ever tried to DROP multiple tables from a MySQL database which all start with a…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"footnotes":"","_jetpack_memberships_contains_paid_content":false,"jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[6],"tags":[16],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p502c8-1y","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/posts\/96"}],"collection":[{"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":0,"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"wp:attachment":[{"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/zulfikar.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}