Multiple DB connection problems!

[Guest Brivtech]

I'm having some real bother with a program I'm writing that works with 2 separate databases on different servers. The first database search criteria forms a main loop wherein the second database is accessed in a sub-loop. When that completed, the loop returns to fetch the next record from the first database again, but can't connect any longer, so can't go any further.

Here's the code I've programmed, where I've thinned it out to shop the DB connection programming, stripped out the other programming functions as they're irrelevant to this problem:

<?php

/* I've stripped this down to show thow I'm connecting to 2 databases - First databases fetches the main list of records, and goes through them one at a time in a main loop.



Within the loop, a second database on a different server is looked up with queries executed on that (some of the data from the first database is carried over to the second database - This is on purpose as part of a function being programmed).



The problem here lies in that the first database can't be reselected when the main loop moved to the next record, otherwise it brings up a connection error. */



// Database variables for DB1

// Database variables for DB2



// Make the SQL connection

$db1_Link = mysql_connect($DB1_Host,$DB1_User,$DB1_Pass) or die('ERROR 1: ' .	 mysql_error());



// Select that database to start working off it

mysql_select_db($db1_Link) or die('ERROR 2: Could not select database'); // For the record, I tried adding this in a second time where the main loop that follows returns, but it made no difference.



// Faff around with some queries

$stringData = mysql_query("SELECT * FROM `some_table`");



// Quick check to know how many records I'm dealing with...

$num_rows = mysql_num_rows($stringData) or die ('Error 3: '.mysql_error());

echo $num_rows . ' Records to process<br />';



// 2. Start the main loop



$i = $num_rows; // Made this because as the results are processed, I just wanted to keep tabs on the record number within the programming code



while ( $row = mysql_fetch_array($stringData) )

	{



/// ***<1>*** We come back to this point later on when the loop returns.



	$i--;



	// Here, I need another query to fetch some data from a different table as a reference lookup

	$sringData2 = mysql_query("

	SELECT `field_name`

	FROM `".$DB1_Database."`.`table_name`

	WHERE `field` = " .$row['other_field']) or die(mysql_error());

				



	// Now I do some other programming stuff with the data I fetched



	// blah blah blah





################################################################################

####################



// Here's where we need to change databases, because now the second database is on a different mySQL server...



// Open the second database!

	$db2_Link = mysql_connect($DB2_Host,$DB2_User,$DB2_Pass) or die('ERROR 4: ' .	 mysql_error());



// Do another query on the second database to get some more data for referencing...



	$stringData3 = mysql_query("

	SELECT *

	FROM `database2`.`table_on_db_2`

	WHERE `table` = 'some_other_table_to_make_condition'") or die('ERROR 5: '. mysql_error());



// Faff around with the results of that...





// Now here's where I'm having the problem. I've added in 



	mysql_close ($db2_Link); // as we're finished with that database now, and open it up each time we go into the sub-loop.



	}; // and loop back to the main loop, where suddenly, I can't reselect the first database any more. Remember ***<1>*** above???



################################################################################

####################



// Here's where the main loop would have terminated, and the program executed sucessfully.



};



echo "finished whole lot!";



// And close the first database because for now we`re finished with it.

mysql_close ($db1_Link);



?>

Any takers? As I can't come up with a fix or proper reason why I can't go back and forth between databases, I'm thinking of an alternative, loading the results of the first loop (all records, and data needed by the second loop) into an array, so that the second loop can work from the array afterwards, without

having to access the first database any further. If that still doesn't work because the implementation may insist that I have to ultimately go back to the first database, I'll have to dump the array into a file, that the second loop will pick up as a separate process. It's not a pretty solution, and requires manual intervention, but it's all I've come up with so far as an alternativeway of doing it.

Ideally if only I could switch the active database back properly, I wouldn't have to do any major re-programming. Bloody shame, because I almost had it finished having tested the 2 sections separately.

EDIT: I think this is too complicated for most people here. Having looked around on the web for answers, I believe that multiple databases on different servers is something that is at time impossible to resolve, especially if the server is restricted because of security.

Also, having implemented my code into CC, I see that my server doesn't want to break the connection to the CC database, and this is making things nearly impossible giving me an SQL access denied error - It's using data from the second DB to access the first, and that's just in the connect command!

Never mind, I'm setting the second part to run as a batch job, from an array saved into a file.

[Guest]

Why do you need two databases?

We run three stores off one database with a cron job updating the databases every 24 hours to a second server for a daily back up of the website.

Are you using 2 databases for a back up?

[Guest]

Brivtech, this is most certainly possible. You have be mindful of which connect resource you're using on each query. Drop me an IM and I'd be happy to work out the kinks with you.

:)