Dear All,
I am having 2 different DB on 2 different hosts.
I am running MySQL Server on my local PC where the user is entering data in the tables. I have a website which has the identical DB on the web. I am able to connect to the database by using the codes on the server.
I want to update the server DB with the local system DB by running one update command. I get the error "-SELECT command denied to user 'localusername'@'localhost' for table 'pst_data'"
Given below is the code used for the process :
//connecting the remote system DB
$link = mysql_connect('IPAddress:3306', 'remoteusername', 'Password');
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db('remotedb', $link);
if (!$db_selected) {
die ('Can\'t use Remote System DB: ' . mysql_error());
}
//connecting the local database on the webstite
$weblink = mysql_connect("localhost","localusername","password");
if (!$weblink) {
die('Not connected : ' . mysql_error());
}
$webdb_selected = mysql_select_db('localwebdb', $weblink);
if (!$webdb_selected) {
die ('Can\'t use WebServer Database : ' . mysql_error());
}
//query to fetch the records from remote ystem and insert into local website database
$upd_Query=mysql_query("INSERT INTO localwebdb.`table` SELECT * FROM remotedb.`table` where field=' some condition' ");
---------------------------------------------------
I have tested that I have connected the remote DB by running queries on the webserver.
Could anyone bail me out so that i can copy the DB from remote to local
Any help would be appreciated
Connecting 2 databases on different hosts
Moderator: General Moderators
Re: Connecting 2 databases on different hosts
Queries are executed on the server you that either specify with a dbResource ($link or $weblink) or that last made connection if you didn't specify one. So you are telling $weblink server to access remotedb which, from the error message you gave, looks like that database does exist on the your localhost server, but you do not have permission to access it. the $weblink SQL server has no clue about the connection to $link server
You cannot do this directly the way you are wanting to. What you will need to do is load in the data to PHP with one query, and write back out to the other with another query, something like the following:
The reason I build up the full insert SQL (instead of just doing INSERT INTO `table` VALUES ('val1','val2','val3') is just to future proof the code in case you ever change anything. Also if you need specific field handling you could do something like the following:
To be honest, if this is just a "one time" thing, I wouldn't even write a script, I use Navicat (yes it isn't free, but the use I get out of it daily over the past 5 years, well worth it), and in there, you can just drag/drop tables between databases as well as servers, and it handles it all for you 
-Greg
You cannot do this directly the way you are wanting to. What you will need to do is load in the data to PHP with one query, and write back out to the other with another query, something like the following:
Code: Select all
<?php
//connecting the remote system DB
$link = mysql_connect('IPAddress:3306', 'remoteusername', 'Password')
or die('Not connected : ' . mysql_error());
mysql_select_db('remotedb', $link)
or die('Can\'t use Remote System DB: ' . mysql_error());
//connecting the local database on the webstite
$weblink = mysql_connect("localhost","localusername","password")
or die('Not connected : ' . mysql_error());
mysql_select_db('localwebdb', $weblink)
or die ('Can\'t use WebServer Database : ' . mysql_error());
$fetchSQL = 'SELECT * FROM `table` WHERE `field`="' . mysql_real_escape_string($strCondition) . '"';
$rsRemote = mysql_query($fetchSQL,$link);
$intRowCount = 0;
if ($rsRemote && mysql_num_rows($rsRemote)) {
while ($aryTemp = mysql_fetch_assoc($rsRemote)) {
$SQL1 = 'INSERT INTO `table` (';
$SQL2 = ') VALUES (';
foreach($aryTemp as $key=>$val) {
$SQL1 .= '`'.$key.'`,';
$SQL2 .= '"'.mysql_real_escape_string($val).'",';
}
// $SQL1 = INSERT INTO `table` (`field1`,`field2`,`field3`,
// $SQL2 = ) VALUES ("value1","value2","value3",
$insertSQL = substr($SQL1,0,-1) . substr($SQL2,0,-1) . ')';
mysql_query($insertSQL,$weblink)
or die ('ERROR EXECUTING SQL:' . $insertSQL);
$intRowCount++;
}
}
echo 'FINISHED: Inserted ' . $intRowCount . ' records. ';
?>Code: Select all
foreach($aryTemp as $key=>$val) {
switch($key) {
// These fields can accept NULL values, so let them be NULL if not set to something
case 'Address2':
case 'PhoneExt':
case 'Notes':
if (trim($val) != '') {
$SQL1 .= '`'.$key.'`,';
$SQL2 .= '"'.mysql_real_escape_string($val).'",';
}
break;
// These fields say you want to always have current timestamp
case 'tsImported':
$SQL1 .= '`'.$key.'`,';
$SQL2 .= 'NOW(),';
break;
// These fields are ones you want to skip (ie, fields in local you no longer have in remote)
case 'debugData':
// Do nothing, don't add at all
break;
// Everything else
default:
$SQL1 .= '`'.$key.'`,';
$SQL2 .= '"'.mysql_real_escape_string($val).'",';
}
}
-Greg