Updating the MySQL database with PHP only if the value changed

Question

I have a big MySQL database and I'm extracting the important bits into a smaller MySQL database. The values in the big database are constantly changing. The smaller database should also be changing dynamically.

I am able to populate the smaller database only when it's empty, using this code:

$SQL_INSERT="INSERT LOW_PRIORITY IGNORE INTO oddsnavi_baby.calc (one , two, three)
VALUES ('$one', '$two' , '$three')";

mysql_db_query($database_baby, $SQL_INSERT) or die("Failed Query of " . $SQL_INSERT);

I'd like the values which are changed to be updated. For example, if $three is different than the existing value in 'three' in the small database, then 'three' is the only updated value in the row. How to do that?

EDIT: I'm getting an error with the following code. What's wrong with my syntax?

$SQL_INSERT="INSERT LOW_PRIORITY IGNORE INTO oddsnavi_baby.calc (one , two, three)
VALUES ('$one', '$two' , '$three')
ON DUPLICATE KEY UPDATE oddsnavi_baby.calc SET two = '$two' , three = '$three'
WHERE one = '$one'";

I have no control over the big database, so I can't update the smaller database in the same process. This update I'm talking about is supposed to run as a cron job, so it will be done in the background. I'm just looking for the correct code to update the smaller database instead of only filling it if it's empty. Something like INSERT ON DUPLICATE KEY UPDATE. — Dan Horvat
– Dan Horvat, Commented Apr 30, 2012 at 22:18

BluesRockAddict · Accepted Answer · 2012-04-30 22:15:40Z

1

If both databases are hosted on the same physical hardware, you might want to consider implementing an UPDATE trigger on one of more tables in your "big" database which would update the values in "small" database.

Here is an article with brief overview on using triggers in MySQL: http://www.roseindia.net/mysql/mysql5/triggers.shtml

answered Apr 30, 2012 at 22:15

BluesRockAddict

15.6k3 gold badges40 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

glenstorey · Accepted Answer · 2012-05-01 07:19:13Z

0

I can think of two ways you can do this:

the software that changes the "big" database, alerts the software that manages the small database, and then the managing software changes the appropriate fields.
I think you can create a view between the two databases, never done something like that though, no idea even if its possible, but why not, you can make foreign keys between two (or more) databases.

edited May 1, 2012 at 7:19

glenstorey

5,1525 gold badges42 silver badges71 bronze badges

answered Apr 30, 2012 at 22:15

Nikola

5542 gold badges6 silver badges18 bronze badges

Collectives™ on Stack Overflow

Updating the MySQL database with PHP only if the value changed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related