I have two tables from two different databases and I want a php function to syncronize the data, so that the table number 2 can always verify the content on the table 1 and update it's information. Anyone has one example on how to do that? Thanks in advance.
3 Answers
D.S.'s answer will get the job done.
You could also look into setting up an after insert/update trigger and using dblink. That way, they'll be kept in sync without you needing to worry about it in PHP.
As a side note, be very wary of what might happen on DB errors in either case. You can end up losing sync with either solution when DB errors occur, because the transactions will be autonomous.
answered May 31, 2011 at 14:50
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges
Comments
this example will connect to both the databases, and for each of the first db's authors will update the destination db's author with the same id. Of course you have to set up any necessary check, search and other details before perform and update (or an insert or replace if you prefer), but it fully depends on what you're going to do :)
<?php
if (false !== ($con1 = pg_connect("your source connection string"))) {
if (false !== ($con2 = pg_connect("your dest connection string"))) {
if (false !== ($result = pg_query($con1, "SELECT id, author, email FROM authors"))) {
while (false !== ($row = pg_fetch_assoc($result))) {
pg_query($con2, "UPDATE authors SET email=".pg_escape_string($con2, $row['email']).
'WHERE id='.pg_escape_string($con2, $row['id']));
}
pg_free_result($result);
}
pg_close($con2);
}
pg_close($con1);
}
?>
I hope it was useful. Please feel free to ask any question about it. Enjoy! :)
answered May 31, 2011 at 14:40
Dmitri Sologoubenko
2,9191 gold badge24 silver badges28 bronze badges
2 Comments
aocferreira
DS first of all thanks for your reply:) Im new to PHP so I don't fully understand your code.. tell me something: in that case, the information is updated! but if the destination db is empty and I want the script to update it like source database?
Dmitri Sologoubenko
If you want this, you have to use an INSERT query instead of the UPDATE query, but you should check - before - if the matching row already exists in the destination database, in such a case use UPDATE as in example. You can also use SQL REPLACE command, it depends on what your primary key look like.
Create trigger on Insert, Update, Delete. When trigger procedure called store all the changes done in operation(Insert, update or delete) into database table(let's call this sync_table). Run some script which will copy data from sync_table to another database table. sync_table will store what data modified, inserted and deleted.
CREATE TABLE?, you need to implement your own logic to synchronize data between two different database