I have multiple databases all with the same table structure. I thought I could easily write a script that will update each database in one shot so I would not have to do it one by one. I also have a table that contains each database name. Here is the script I wrote:
// this include houses the database localhost, username
// password, and databasename that holds all other db names
include "configs.php";
$connection = @mysql_connect("$location", "$databaseuser", "$databasepasswd");
@mysql_select_db("$databasename",$connection);
// read all database names
$sql = "Select db_name from database_table ";
$result = @mysql_query($sql,$connection)or die("$sql.");
// Begin my loop of collecting database names
while ($row = mysql_fetch_array($result)){
$acc_name = $row['db_name'];
// start my other connection to the database I captured above
$connection2 = @mysql_connect("$location", "$databaseuser", "$databasepasswd");
@mysql_select_db("$acc_name",$connection2);
// Now begin my alterations to the tables
$sql2 = "ALTER TABLE my_table MODIFY field1 VARCHAR(100) NOT NULL";
$result2 = @mysql_query("$sql2",$connection2)or die($sql2);
$sql2 = "ALTER TABLE my_table2 MODIFY field7 VARCHAR(100) NOT NULL";
$result2 = @mysql_query("$sql2",$connection2)or die($sql2);
// echo the db_name that was just updated
echo "<br>updating $acc_name";
}
?>
The problem I get is the script only wants to run through one time and then it gives me a mysql_fetch_array result error on line XX which is the line above that is in the while loop the second time it goes through. So the first db it grabs (the first record) gets updated just fine, the second one produces the error.
Anyone know what I am missing or not doing right here.
Thanks for any help.
