CODEWITHSUNDEEP
phpmysqlcodeigniter
Qadir Hussain
Qadir Hussain

Reputation: 8856

How to synchronize two tables of different MySQL databases on the same machine?

I have 2 different databases, let say db_one and db_two. Each one has a table. db1_emp_company and db2_emp_info respectively.

Structure is given below:

db_one           |    db_two
__________________________________
db1_emp_company  |    db2_emp_info
__________________________________
phone_no         |    contact_no
emp_email        |    email_add
home_address     |    address

These two databases are hosted by two different php websites on the same server.

I want to synchronize these both tables of different databases. For example if a user update the phone_no of db1_emp_company (in db_one) it should update the contact_no of the db2_emp_info (in db_two). And if a user update the emp_email of db1_emp_company (in db_one)it should update the email_add of db2_emp_info (in db_two). and so on.

Is it possible?

Upvotes: 2

Views: 6570

Answers (3)

Nuri Akman
Nuri Akman

Reputation: 820

You can use mysqldiff for this issue. Check this page: http://www.mysqldiff.org/index.php

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

For this purpose you can use transactions.

$mysql_host                 = DB_HOST;
$mysql_username             = DB_USER;
$mysql_password             = DB_PASSWORD;
$mysql_database1            = DATABASE1;
$mysql_database2            = DATABASE2;

$mysqli = new mysqli($mysql_host, $mysql_username, $mysql_password) or die( mysql_error());

/*  Begin Transaction   */
$mysqli->autocommit(FALSE); 


/*  Insert data from db1 to db2 */
$query  =  "    INSERT INTO $mysql_database1.table1";

$a  =   $mysqli->query($query); 

$query  =  "    INSERT INTO $mysql_database1.table2
            SELECT 
                *
            FROM $mysql_database2.table2
            WHERE NOT EXISTS(SELECT * from $mysql_database1.table2)     
";
$d  =   $mysqli->query($query); 


if ($a and $b) 
{
    $mysqli->commit();      
    echo "Data synched successfully.";
} else {        

    $mysqli->rollback();        
    echo "Data failed to synch.";
}

If one query fails it will not allow the other to run and will roll back. This is only example code. You can use update instead of insert.

Upvotes: 5

Rahul Tapali
Rahul Tapali

Reputation: 10137

You can do like this:

On updating db_one table in site1:

 update `db_one`.`db1_emp_company` set phone_no = '1234567890' where conditions_here;
 update `db_two`.`db2_emp_info` set contact_no = '1234567890' where conditions_here;

Same thing in db_two:

 update `db_two`.`db2_emp_info` set contact_no = '1234567890' where conditions_here; #Its a same thing as above
 update `db_one`.`db1_emp_company` set phone_no = '1234567890' where conditions_here;

If you post your code which is used for updating things. It will be helpful to give exact answer.

Upvotes: 0

Related Questions