Update data from tables in two different databases

Question

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.

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.");
?>

See this question please: http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage — Paolo Stefan, Aug 09 '13 at 07:43

score 2 · Answer 1 · answered Aug 09 '13 at 09:37

<?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.

score 0 · Answer 2 · answered Aug 09 '13 at 07:46

Recall the documentation for mysql_connect:

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

... and the documentation for the second parameter for 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.

... 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 );

Hi, thanks for this but I really am a dummy when it comes to this. I can just about copy and paste code and make some minimal adjustments but if you take me out of there I'm lost. If I could get the code I need so that I can paste and try that'd be much appreciated. Thanks! — Eugenie, Aug 09 '13 at 08:59

score 0 · Answer 3 · answered Aug 09 '13 at 07:52

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.

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.

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

Code could be somthing like that:

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

Hi, thanks for reply. Same comment as above. I'm a complete dummy when it comes to coding. Something that I could copy and paste and try would be just perfect :) — Eugenie, Aug 09 '13 at 09:00

Update data from tables in two different databases

3 Answers3