Solved

Batch: do query on 200+ DB's at once

Posted on 2008-10-30
5
269 Views
Last Modified: 2013-12-13
I have 200+ mySQL databases, all with the same user privalages.

I want to perform two SQL queries on all of them at once either from a command line or using PHP.

I have a list of all DBs in a long list with one on each line.

    db_sdfdsfd1
    db_jhgdj34
    db_xz45g
    ....


ALTER TABLE `xyz_Pages`

  DROP `showMenu`,

  DROP `showSitemap`,

  DROP `showSEO`;
 

ALTER TABLE `xyz_Pages`  

ADD  `showMenu` TINYINT NOT NULL DEFAULT '1',

ADD `showSitemap` TINYINT NOT NULL DEFAULT '1',

ADD `showSEO` TINYINT NOT NULL DEFAULT 1 ;

Open in new window

0
Comment
Question by:hankknight
5 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 22840358
    Hi!

You can use the  TOAD for MySQL (http://www.toadsoft.com/toadmysql/Overview.htm) for that.
It has the feature to connect to N databases and issue the same SQL on every connection.

Regards,
   Tomas Helgi
0
 
LVL 1

Expert Comment

by:1eEurope
ID: 22840377
just use a readfile in php to go threw your databases in a foreach-loop. in the loop select the database (use myDatabase) and run the statements.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 22840444
Back up all your data bases before you try anything!

Do you have the same user id and password and host for all the data bases?

Thanks, ~Ray
0
 
LVL 6

Accepted Solution

by:
cr4ck3rj4ck earned 500 total points
ID: 22840446
This will have a LONG execution time but essentially this should suit your needs.

Hope this helps.
	$dbConnect = mysql_pconnect($dbHost, $dbUser, $dbPass); 

	$dbList = file('DBList.txt');

	foreach ($dbList as $dbName)

	{

		mysql_select_db(trim($dbName), $dbConnect);

		$querySQL = "ALTER TABLE `xyz_Pages` DROP `showMenu`, DROP `showSitemap`, DROP `showSEO`";

		$executeSQL = mysql_query($querySQL);

		$querySQL = "ALTER TABLE `xyz_Pages` ADD `showMenu` TINYINT NOT NULL DEFAULT '1', ADD `showSitemap` TINYINT NOT NULL DEFAULT '1', ADD `showSEO` TINYINT NOT NULL DEFAULT 1";

		$executeSQL = mysql_query($querySQL);

	}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 22840540
@cr4ck3rj4ck: Just a thought, but when working with beginners, it's a good idea to remind them to test the return values from functions and print out error messages, etc.  Otherwise you get a lot of "it didn't work" postings. ;-)
<?php // multiple db updates
 

// BASIC CONNECTION INFORMATION ASSUMES ALL DB ARE ON SAME SERVER

$db_host	= "localhost";

$db_user	= "your user id";

$db_word	= "your password";
 

// AN ARRAY OF DATA BASE NAMES - YOU MIGHT LOAD THIS BY READING IT FROM A FILE?

$db_names[]	= "db_sdfdsfd1";

$db_names[]	= "db_jhgdj34";

$db_names[]	= "db_xz45g";
 

// CONNECT TO THE DATA BASE SERVER

if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word")) {

	$errmsg	= mysql_errno() . ' ' . mysql_error();

	echo "$errmsg";

	die();

}
 

// ITERATE OVER DATA BASE NAME LIST

foreach ($db_names as $db_name) {
 

// SELECT THE DATA BASE

	if (!$db_sel = mysql_select_db($db_name, $db_connection)) {

	 	$errmsg	= mysql_errno() . ' ' . mysql_error();

	 	echo "$errmsg";

	 	die();

	}

	

// EXECUTE THE QUERIES 

	$sql	= "ALTER TABLE `xyz_Pages` DROP `showMenu`, DROP `showSitemap`, DROP `showSEO` ";

	if (!$s	= mysql_query($sql)) { $error = mysql_error(); die("$error"); } 

	/* MORE QUERIES, AS REQUIRED */

}

?>

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select from sql by results from textbox 8 26
Echo images using file system 2 29
PHP_POST() error message 9 39
echo time from sql to input type="time" 9 17
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now