从两个不同数据库中的表更新数据

I've got two different sites. What I'd like to do is to automatically run a script that sends some of the data inserted into the database in site 1 when a user registers and updates a table in the database for site 2 so that an account is automatically created in site 2 using the same details.

我有两个不同的网站。我想做的是当用户注册并更新站点2的数据库中的表时,自动运行一个脚本,该脚本将发送插入到站点1中的数据库中的一些数据,以便在站点2中自动创建一个帐户使用相同的细节。

I'm at the stage of trying to create a query that will update the database. I'm the self-made type so don't know that well what I'm doing. Got this query from somewhere but can't make it work. Can anyone tell what's wrong with it? It's not executing the query.

我正处于尝试创建将更新数据库的查询的阶段。我是自制型,所以不知道我在做什么。从某个地方获得此查询但无法使其工作。任何人都能说出它有什么问题吗?它没有执行查询。

Thanks!

Eugenie

<?php

    $host = "localhost"; // Host name 
    $username = "----"; // Mysql username 
    $password = "----"; // Mysql password 
    $db_name1 = "------"; // Database name 
    $db_name2 = "-----"; // Database name 
    $tbl_name1 = "-----"; // Table name 
    $tbl_name2 = "---"; // Table name 

    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name1")or die("cannot select DB");

    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name2")or die("cannot select DB");
    $query = "USE $db_name2 
              UPDATE $db_name2.dbo.$tbl_name2 
              SET email=d2.email FROM $db_name1.dbo.$tbl_name1 d2 
              WHERE d2.uid = $tbl_name1.uid";
    $result = mysql_query($query) or die ("could't execute query.");
?>

3 个解决方案

#1

<?php

$host = "localhost"; // Host name 
$username = "----"; // Mysql username 
$password = "----"; // Mysql password 
$db_name1 = "------"; // Database name 
$db_name2 = "-----"; // Database name 
$tbl_name1 = "-----"; // Table name 
$tbl_name2 = "---"; // Table name 

$conn = mysql_connect($host, $username, $password);
mysql_select_db($db_name1, $conn) or die("cannot select DB");
mysql_select_db($db_name2, $conn) or die("cannot select DB");;

$query1 = "SELECT * FROM `" . $db_name1.$tb1_name1 . "` ";
$query2 = "SELECT * FROM `" . $db_name2.$tb1_name2 . "` ";

You can fetch data of above query from both database as below

您可以从两个数据库中获取上述查询的数据,如下所示

$result1 = mysql_query($query1);
while($row = mysql_fetch_assoc($result1)) {
    $data1[] = $row;
}

$result2 = mysql_query($query2);
while($row = mysql_fetch_assoc($result2)) {
    $data2[] = $row;
}

print_r($data1);
print_r($data2);
?>

Suggestion: Try shifting to mysqli or PDO since mysql is depreciated now.

建议:尝试转换到mysqli或PDO,因为mysql现在已经被折旧了。

#2

Recall the documentation for mysql_connect:

回想一下mysql_connect的文档:

Returns a MySQL link identifier on success or FALSE on failure.

成功时返回MySQL链接标识符,失败时返回FALSE。

... and the documentation for the second parameter for mysql_query:

...以及mysql_query的第二个参数的文档:

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

MySQL连接。如果未指定链接标识符,则假定mysql_connect()打开的最后一个链接。如果没有找到这样的链接,它将尝试创建一个,就好像没有参数调用mysql_connect()一样。如果未找到或建立连接,则会生成E_WARNING级别错误。

... should solve your problem. Example:

......应该解决你的问题。例:

$link1 = mysql_connect( ... ); // For db 1.
$link2 = mysql_connect( ... ); // For db 2.

$result1 = mysql_query( "some query for db 1", $link1 );
$result2 = mysql_query( "some query for db 2", $link2 );

#3

Well,

first of all, you're not connecting to two different databases, but using two different schemas in the same database. So only a mysql_connect should be used.

首先,您没有连接到两个不同的数据库,而是在同一个数据库中使用两个不同的模式。所以只应该使用mysql_connect。

Also, if you're using full qualified names to access your tables you don't need to call mysql_select_db, nor the 'use db_name' mysql command.

此外,如果您使用完全限定名称来访问表,则不需要调用mysql_select_db,也不需要调用'use db_name'mysql命令。

Your query string is wrong. After USE $db_name2 you should have a semi-colon, and the update sentence is not correct.

您的查询字符串是错误的。在USE $ db_name2之后,你应该有一个分号,并且更新语句不正确。

Code could be somthing like that:

代码可能是这样的:

mysql_connect(...) $query = "update $db2.$table2, $db1.$table1

mysql_connect(...)$ query =“update $ db2。$ table2,$ db1。$ table1