How to sync two database tables with each other in mysql PHP

Question

I have two tables called clients, they are exactly the same but within two different db's. Now the master always needs to update with the secondary one. And all data should always be the same, the script runs once per day. What would be the best to accomplish this.

I had the following solution but I think maybe theres a better way to do this

$sql = "SELECT * FROM client";
$res = mysql_query($conn,$sql); 
while($row = mysql_fetch_object($res)){
    $sql = "SELECT count(*) FROM clients WHERE id={$row->id}";
    $res1 = mysql_query($connSecond,$sql);
    if(mysql_num_rows($res1) > 0){
       //Update second table
    }else{
       //Insert into second table
    }
}

and then I need a solution to delete all old data in second table thats not in master.

Any advise help would be appreaciated

Answer 1

This is by no means an answer to your php code, but you should take a look @ Mysql Triggers, you should be able to create triggers (on updates / inserts / deletes) and have a trigger (like a stored proceedure) update your table.

Going off the description you give, I would create a trigger that would check for changes to the 2ndary table, then write that change to the primary table, and delete that initial entry (if so required) form the 2ndary table.

Triggers are run per conditions that you define.

Hopefully this gives you insight into 'another' way of doing this task.

More references on triggers for mysql:

• http://dev.mysql.com/doc/refman/5.0/en/triggers.html
• http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

Answer 2

You can use mysql INSERT ... SELECT like this (but first truncate the target table):

TRUNCATE TABLE database2.client;
INSERT INTO database2.client SELECT * FROM database1.client;

It will be way faster than doing it by PHP.

And to your notice:

As long as the mysql user has been given the right permissions to all databases and tables where data is pulled from or pushed to, this will work. Though the mysql_select_db function selects one database, the mysql statement may reference another if you use complete reference like databasename.tablename

Answer 3

Not exactly answering your question, but how about just using 1 table, instead of 2? You could use a fedarated table to access the other (if it's on a different mysql instance) or reference the table directly (like shamittomar's suggestion)

Answer 4

If both are on the same MySQL instance, you could easily use a view:

CREATE VIEW database2.client SELECT * FROM database1.client;

And that's it! No synchronizing, no cron jobs, no voodoo :)