У меня есть серия баз данных mysql на моем сервере. Имена этих баз данных хранятся в отдельной таблице базы данных (users) – column (dbname). Я хочу пройти через таблицу (пользователей), получить имена db, подключиться к каждому и выполнить операцию в каждой базе данных. Как выполнить такой цикл?
Какой-то грубый код. Не отладка или тестирование …
$masterDBHost = 'localhost'; $masterDBUser = 'username'; $masterDBPass = 'somethingSecret'; $masterDBName = 'theDBname'; $sqlToPerformOnEachDatabases = 'SELECT 1'; // Connect to the Master Database if( !( $master = mysql_connect( $masterDBHost , $masterDBUser , $masterDBPass ) ) ) die( 'MySQL Error - Cannot Connect to Master Server' ); if( !mysql_select_db( $masterDBName , $master ) ) die( 'MySQL Error - Cannot Connect to Master Database' ); // Get the Other Databases to Connect to $databases = mysql_query( 'SELECT * FROM `databaseTable`' , $master ); // Check your Results if( !$databases || mysql_num_rows( $databases )==0 ){ # Nothing to work with echo 'Unable to find Databases to Access'; }else{ # Something to work with while( $d = mysql_fetch_assoc( $databases ) ){ // Connect to the Client Server if( !( $temp = mysql_connect( $d['host'] , $d['user'] , $d['pass'] ) ) ){ # Can't connect to the Server echo 'MySQL Error - Failed to connect to '.$d['host'].' as '.$d['user']; }elseif( !mysql_select_db( $d['base'] , $temp ) ){ # Can't connect to the Database echo 'MySQL Error - Failed to connect to '.$d['base'].' on '.$d['host'].' as '.$d['user']; }elseif( !mysql_query( $sqlToPerformOnEachDatabases , $temp ) ){ # Your Command, well, stuffed up echo 'MySQL Error - Your Command Stuffed Up'; }else{ # Your Command worked OK echo 'All Good!'; } # Close the connection (probably not needed, but nice to do) @mysql_close( $temp ); } }
Позволяет поддерживать соединение, если используется тот же Host / User / Pass
Опять же, не отлаживается или не тестируется.
$masterDBHost = 'localhost'; $masterDBUser = 'username'; $masterDBPass = 'somethingSecret'; $masterDBName = 'theDBname'; $sqlToPerformOnEachDatabases = 'SELECT 1'; // Connect to the Master Database if( !( $master = mysql_connect( $masterDBHost , $masterDBUser , $masterDBPass ) ) ) die( 'MySQL Error - Cannot Connect to Master Server' ); if( !mysql_select_db( $masterDBName , $master ) ) die( 'MySQL Error - Cannot Connect to Master Database' ); // Get the Other Databases to Connect to $databases = mysql_query( 'SELECT * FROM `databaseTable`' , $master ); // Check your Results if( !$databases || mysql_num_rows( $databases )==0 ){ # Nothing to work with echo 'Unable to find Databases to Access'; }else{ # Something to work with // A variable for the MySQL Connection $temp = false; // Declare some short-term memory $last = array(); while( $d = mysql_fetch_assoc( $databases ) ){ // Check Last Loop's details if( $temp && $last && array_diff( $last , $d ) ){ // New Host, User or Pass @mysql_close( $temp ); $last = false; } // Connect to the Client Server if( !$last && !( $temp = mysql_connect( $d['host'] , $d['user'] , $d['pass'] ) ) ){ # Can't connect to the Server echo 'MySQL Error - Failed to connect to '.$d['host'].' as '.$d['user']; }elseif( !mysql_select_db( $d['base'] , $temp ) ){ # Can't connect to the Database echo 'MySQL Error - Failed to connect to '.$d['base'].' on '.$d['host'].' as '.$d['user']; }elseif( !mysql_query( $sqlToPerformOnEachDatabases , $temp ) ){ # Your Command, well, stuffed up echo 'MySQL Error - Your Command Stuffed Up'; }else{ # Your Command worked OK echo 'All Good!'; } # Remember this Loop's details $last = $d; } @mysql_close( $temp ); }
После дней борьбы я смог выйти с этим решением. Я взял много идей из решений, которые были предоставлены этому вопросу на этой платформе. сами решения не работали для меня, но у меня было много идей от них.
<?php //db parameters $dbhost = "myhost"; // this will ususally be 'localhost', but can sometimes differ $dbname = "dbusers"; // the name of the database that you are going to use for this project $dbuser = "root"; // the username that you created, or were given, to access your database $dbpass = "mypassword"; // the password that you created, or were given, to access your database // I first connect to the db with names of the other dbs mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error()); mysql_select_db($dbname) or die("MySQL Error: " . mysql_error()); //select dbnames from the table with db names $query = "SELECT dbname FROM users"; $result = mysql_query($query); //loop through the results(dbname) and connect to each db while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { //put the dbname results into a variable $dbName =$row['dbname']; // connect to each db $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error()); $db = mysql_select_db("mydbprefix_".$dbName) ; //do a query for each db this is because each db has same tables and structure //check to see if db exist if( $db ){ //all the operation goes here in my case I wanted to count number of products for each table called products. $query2 = mysql_query("SELECT * FROM products"); $num_rows = mysql_num_rows($query2); $pro = $num_rows; echo "this user has ".$pro." products"."<br/>"; //another operation goes here for the same db. // then close connection for each db mysql_Close ($conn); } } ?>
Именно так я обошел эту проблему. Это может быть не лучшее решение, но это то, что у меня есть.
я бы сделал что-то подобное, удачи! :
function dbConnect($location, $username, $password, $database) { $conn = mysql_connect($location, $username, $password); if (!$conn) die ("Could not connect MySQL"); mysql_select_db($database, $conn) or die ("Could not open database ".$database); } ####### # Connexion to databases containing users (add your own fields) ####### dbConnect('localhost', 'root', '', 'databases'); $sql = "SELECT * FROM users"; $result = mysql_query($sql); $numrows = mysql_num_rows($result); ####### # Looping to establish connexion to db1 and db2 ####### while($row = mysql_fetch_array($result)) { dbConnect('localhost', $row["user"], $row["pass"], $row["db"]); } ####### # At this point, the connexion is established to LOCAHOST[DATABASES], LOCALHOST[DB1], LOCALHOST[DB2] ####### $sql = "SELECT * FROM databases.users"; $result = mysql_query($sql); $numrows = mysql_num_rows($result); while($row = mysql_fetch_array($result)) { print "<pre>"; print_r($row); print "</pre>"; } /* Array ( [id] => 1 [user] => root [pass] => [db] => db1 ) Array ( [id] => 2 [user] => root [pass] => [db] => db2 ) */ $sql = "SELECT * FROM db1.infos"; $result = mysql_query($sql); $numrows = mysql_num_rows($result); while($row = mysql_fetch_array($result)) { print "<pre>"; print_r($row); print "</pre>"; } /* Array ( [id] => 1 [name] => John [age] => 26 ) Array ( [id] => 2 [name] => Henri [age] => 34 ) */ $sql = "SELECT * FROM db2.infos"; $result = mysql_query($sql); $numrows = mysql_num_rows($result); while($row = mysql_fetch_array($result)) { print "<pre>"; print_r($row); print "</pre>"; } /* Array ( [id] => 1 [name] => Paul [age] => 30 ) Array ( [id] => 2 [name] => Scott [age] => 39 ) */