[RESOLVED] Updating data from two MYSQL databases on different servers

[Operandi]

Hi

We have two MySQL database servers: server1 (MySQL 5.x) and server2 (MySQL 4.1.22)

I want to issue a select query against server1.links table, process the data and then update the server2.links table.

I can successfully connect using a PHP script from server2 to server1 to issue the SELECT statement, however I cannot then insert that data into the server2.links table as there are permission problems.

I have tried creating a temporary table on server2 but I hit permission problems too.

Any help very gratefully received.

This is the code, so far:

//connect to the local database
if ( ! $local_connection = mysql_connect( $server2_host_name, $server2_database_username, $server2_database_password ) )
	{
	die ( "Cannot connect to the local database" );
	}

mysql_select_db( $server2_database_name, $local_connection );

//connect to the remote database (server1)
if ( ! $local_connection = mysql_connect( $server1_host_name, $server1_database_username, $server1_database_password ) )
	{
	die ( "Cannot connect to the remote database" );
	}

mysql_select_db( $server1_database_name, $local_connection );


//Issue the SELECT query against the remote links table

$query = "SELECT * FROM server1.links";
$rs	= mysql_query( $query ); 
if  ( ! $rs )
	{
	echo mysql_error ();
	die;
	}
// OK so far, our remote query is successful

if ( mysql_num_rows ( $rs ) > 0 )
	{
	while ( $row = mysql_fetch_assoc ( $rs ) )
		{
		//update local links table with remote links data
		$query = "UPDATE links SET 
				field1='$row[field1]',
				field2='$row[field2]'
			WHERE supplier_id = '$row[supplier_id'";
		$mysql_query ( $query );
		//  **** This query fails with permission problems
		}
	}

[rulian]

If you are running into permissions problem, first make sure you are connecting to the servers with an account that has those permissions.
If you can post the actual error its reading it would be helpful

[Operandi]

I've actually resolved this myself.

I hadn't added the resource identifier to the mysql_query commands so MYSQL was assuming the wrong connection, and therefore ran into permission problems

It should be $mysql_query ( $query, $local_connection );