循环浏览mysql数据库

| 我的服务器上有一系列mysql数据库。这些数据库的名称存储在单独的数据库表（用户）-column（dbname）中。我想遍历表（用户），获取dbname，连接到每个数据库，并对每个数据库执行操作。如何执行这样的循环？     
2020-01-07 4 条评论
分享

没有找到相关结果

    已邀请:

    荒劫娇噬

    一些粗糙的代码。未经调试或测试... 
    $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 );
  }
}
    版本2 如果使用相同的主机/用户/密码，则允许保持连接 同样，未经调试或测试。 
    $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 );
}
        
    2020-01-07 0 0
    分享

    凸晴

    经过几天的奋斗，我终于提出了这种解决方案。我从该平台上针对该问题提供的解决方案中汲取了很多想法。解决方案本身对我不起作用，但是我从中得到了很多想法。 
    <?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);
    }
     } 
    ?>
    这就是我解决这个问题的方法。这可能不是最佳解决方案，但这就是我所拥有的     
    2020-01-07 0 0
    分享

    告耸

    我会做这样的事，祝你好运！ ： 
    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
)
*/
        
    2020-01-07 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.034576892852783