Multiple Database Connection Loop (Hangs on 2nd connection)

[alwoodman]

Im trying to connect to multiple local databases to run updates but on completing the first run ok  (and occasionally the second run) it hangs saying:

 

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/vhosts/mywebsite/httpsdocs/SQLUpdate.php on line 58
Site 1 Updated

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/vhosts/mywebsite/httpsdocs/SQLUpdate.php on line 58
No database selected

 

Im pretty sure its something to do with unsetting variables before the next run??

 

This is how it works at the moment:

 

Primary Query here to get all the database settings, username passwords etc
$query_getSpecCity = "SELECT * FROM database settings table";




       do { 


				// get specific sites database details
				$hostname_get = "localhost";
				$database_get = $row_getSpec['name'];
				$username_get = $row_getSpec['username'];
				$password_get = $row_getSpec['password'];
				$get = mysql_pconnect($hostname_get, $username_get, $password_get) or trigger_error(mysql_error(),E_USER_ERROR);


				// $UpdateSQL = "THIS WILL BE MY QUERY TO ALTER UPDATE ETC";



				// update successful
				echo $row_getSpecCity['City_URL']." Updated<br/>";
				echo $totalRows_getCity;


				unset($hostname_get_city);
				unset($database_get_city);
				unset($username_get_city);
				unset($password_get_city);
				unset($get_city);
				unset($query_getCity);
				unset($row_getCity);
				unset($totalRows_getCity);
				unset($query_getCity);
				}while ($row_get = mysql_fetch_assoc($get));

		}

 

thanks

 

Lee

[trq]

Your code makes little sense. $get holds a connection resource, mysql_fetch_assoc expects a result resource. Two completely different resources.

[alwoodman]

Hi thanks for the reply... ok here's the actual code...i thinned it out a bit previously

 

thanks

 

lee

 

# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_city_system = "localhost";
$database_city_system = "dbname";
$username_city_system = "username";
$password_city_system = "password";
$city_system = mysql_pconnect($hostname_city_system, $username_city_system, $password_city_system) or trigger_error(mysql_error(),E_USER_ERROR); 

// get all the details of the databases that need updating

mysql_select_db($database_city_system, $city_system);
$query_getSpecCity = "SELECT * FROM tbl_city";
$getSpecCity = mysql_query($query_getSpecCity, $city_system) or die(mysql_error());
$row_getSpecCity = mysql_fetch_assoc($getSpecCity);
$totalRows_getSpecCity = mysql_num_rows($getSpecCity);

		if ($_GET['run'] == "go") {


       do { 
         

				// get specific sites database details
				$hostname_get_city = "localhost";
				$database_get_city = $row_getSpecCity['city_db_name'];
				$username_get_city = $row_getSpecCity['city_db_username'];
				$password_get_city = $row_getSpecCity['city_db_password'];
				$get_city = mysql_pconnect($hostname_get_city, $username_get_city, $password_get_city) or trigger_error(mysql_error(),E_USER_ERROR);


				// $UpdateSQL = "ALTER TABLE `tbl_city` ADD `City_Via123AID` VARCHAR( 11 ) NOT NULL AFTER `City_AdsenseID` , ADD `City_Via123PCID` VARCHAR( 11 ) NOT NULL AFTER `City_Via123AID`";


				mysql_select_db($get_city, $database1_get_city);
				$query_getCity = "SELECT * from tbl_hotels";
				$getCity = mysql_query($query_getCity, $get_city) or die(mysql_error());
				$row_getCity = mysql_fetch_assoc($getCity);
				$totalRows_getCity = mysql_num_rows($getCity);

				// update successful
				echo $row_getSpecCity['City_URL']." Updated<br/>";
				echo $totalRows_getCity;


				unset($hostname_get_city);
				unset($database_get_city);
				unset($username_get_city);
				unset($password_get_city);
				unset($get_city);
				unset($query_getCity);
				unset($row_getCity);
				unset($totalRows_getCity);
				unset($query_getCity);
				}while ($row_getSpecCity = mysql_fetch_assoc($getSpecCity));

		}

[trq]

Why on earth are you using two different databases for this? And even if you need to (I seriously doupt you do), move the connection outside you loops.