Running a Query on Multiple Databases

by | Jul 7, 2014

Our company uses a CMS that we developed in-house, and there are around 60 sites running the most current version of it. That means about 60 sites with mostly identical database structures.

When I make upgrades or bug fixes to the CMS, I commit them to a core repository which is inherited by all the sites. Very efficient, write once, use over and over and over again. Then if you need custom code for a single site, you can override it locally. So that’s great for the code, but the databases, though all pretty similar, are nonetheless each their own unique entity, running on our MySql server.

When I need to change the databases, I have to change all of them individually. What a chore. Shouldn’t there be a programmatic way to do that? PHP5, Cake framework, MySql, hit me up, let me know what I don’t know. But in the meanwhile, I needed to add a single record to the Permissions table of each and every database.

The query looks like this;

Ok, cool. It checks whether the permission exists in the permissions table, gets the id if it does, and adds a record to grant that permission to the admin (#2) group.

But I got to do this 60 times in a row. No way Jose. I asked my pal Matt to help out, he being more advanced in his career than I am. Matt kindly wrote me a nice syntactically-correct statement using the sp_msforeachdb command – then I pointed out that I’m using MySql and sp_msforeachdb doesn’t exists, nor does it have an equivalent that I know of.

These problems are so basic, I’m almost ashamed to even write about them. Does everyone make such elementary mistakes? I’m positive they do, but I must be the only one who blogs about it. Anyway, Matt’s advice was to burn our 13 years of legacy code and start again on Microsoft Server. Okay pal.

Here’s what I ended up doing:

script.php:


Then I ran the contents of output.txt against my database in PHP MyAdmin, and no I couldn’t use mysql_connect or any of the other excellent PHP MySql functions that exists, because some of the databases were on my list incorrectly, didn’t have the right tables and therefore caused the script to fail gracelessly.

Is there a better way? Will you tell me?