UPDATE remote Database from Local Database access error

[Failing_Solutions]

I'm trying to update our remote database from our local database here is what I have...

 

My Connections set in php file

<?php
///THIS IS THE LOCAL DATABASE
define ("DB_HOST", "xxxxx"); // set database host
define ("DB_USER", "xxxx"); // set database user
define ("DB_PASS","xxxxx"); // set database password
define ("DB_NAME","inventory"); // set database name
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection." . mysql_error());
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database" . mysql_error());

//THIS IS THE REMOTE DATABASE
define('DB_SERVER', 'xxxxx');
define('DB_SERVER_USERNAME', 'xxxxxx');
define('DB_SERVER_PASSWORD', 'xxxxxx');
define('DB_DATABASE', 'columb22_231test');
$link2 = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die("Couldn't make connection." . mysql_error());
$db2= mysql_select_db(DB_DATABASE, $link2) or die("Couldn't select database" . mysql_error());
?>

 

My UPDATE QUERY update Remote Database from Local Database

<?php
$sql="UPDATE columb22_231test.products AS p 
INNER JOIN inventory.rebelrstore_inventory AS rs
ON p.products_id=rs.products_id
SET p.products_quantity = rs.products_quantity";
mysql_query($sql) or die(mysql_error());
?>

 

The error

SELECT command denied to user 'remote_xxxx'@'our.network' for table 'rebelstore_inventory'

So I'm getting a denial error to access the local database with the remote user name

 

But I am saying in my query inventory.rebelstore_inventory which should imply the local user name to kick in right??

Anybody know what I'm missing here?

 

Help very much appreciated.

 

[PFMaBiSmAd]

Because you are not specifying the link identifier in the mysql_query() statement, it is using the last connection that was created (remote.) You would need to use the following to use the local database server to run that query -

 

mysql_query($sql,$link) or die(mysql_error());

[Failing_Solutions]

Because you are not specifying the link identifier in the mysql_query() statement, it is using the last connection that was created (remote.) You would need to use the following to use the local database server to run that query -

 

mysql_query($sql,$link) or die(mysql_error());

 

Thanks PFMasBiSmAd

 

I had tried that, sorry I didn't post it originally, doing so does not throw an error, but does not update it either.

[Pikachu2000]

I'm by no means an expert with MySQL, but I don't think you can run that query across two physically separate MySQL servers.

[PFMaBiSmAd]

If you actually are trying to execute a query that references two different database servers, it won't work. The query runs on the database server who's connection you are using and it can only reference databases and tables that server can read.

 

If you are not getting an error, it is likely that you have a database and tables with those names on the server where you are executing that query, but no matching data.

[Failing_Solutions]

If you actually are trying to execute a query that references two different database servers, it won't work. The query runs on the database server who's connection you are using and it can only reference databases and tables that server can read.

 

If you are not getting an error, it is likely that you have a database and tables with those names on the server where you are executing that query, but no matching data.

 

This is exactly what I was trying to do, I guess I can try and put these values into a php array then throw them at the remote database.

 

Thanks for the help