By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,393 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,393 IT Pros & Developers. It's quick & easy.

How to store the same data values in two different databases in mysql using php ?

P: 14
sir,

i want to store the same data values in two different databases at that same time in mysql using php programming.

suppose any one databases data will lose then we use the other databases.
suppose there is any query for store the same data values in two different databases at the same time.

for eg:

databases sample1

tables persons schools books


databases sample2

tables persons schools books
Oct 8 '09 #1
Share this Question
Share on Google+
4 Replies


Atli
Expert 5K+
P: 5,058
Hey.

You just need to execute the same query twice, once for each connection.

You would just need to create a function that allows you to do that.
For example: (Note, I left out any sort of error checking for clarity.)
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. class Database
  3. {
  4.     private static $dbLink1;
  5.     private static $dbLink2;
  6.  
  7.     private static function connect()
  8.     {
  9.         self::$dbLink1 = new mysqli("localhost", "user" ,"pw", "database");
  10.         self::$dbLink2 = new mysqli("mysql.example.com", "user", "pw", "database");
  11.     }
  12.  
  13.     public static function query($sql)
  14.     {
  15.         if(is_null(self::$dbLink1) || is_null(self::$dbLink2)) {
  16.             self::connect();
  17.         }
  18.  
  19.         $result1 = self::$dbLink1->query($sql);
  20.         $result2 = self::$dbLink2->query($sql);
  21.  
  22.         if(!$result1) {
  23.             user_error("Failed to execute query on local SQL server.", E_USER_WARNING);
  24.         }
  25.         if(!$result2) {
  26.             user_error("Failed to execute query on remote SQL server.", E_USER_WARNING);
  27.         }
  28.     }
  29. }
  30. ?>
Which would allow you to just do:
Expand|Select|Wrap|Line Numbers
  1. Database::query("INSERT INTO whatever(col) VALUES('val1', 'val2')");
And it would be executed on both your servers.

Keep in mind tho that the queries are not executed at the exact same time, so if you are inserting times or dates, it would be advisable to create them use PHP rather then using the MySQL NOW() function. (Or any other such function.)


Buuuut...
It is worth mentioning that MySQL supports automatic replication.
See the documentation on Replication.

It's a bit more complex, but if you are doing something on a larger scale, it is worth looking into.
Oct 8 '09 #2

P: 14
hello sir,

Your solution ok. But i want to store the data values in two different databases
in mysql at the same time only.

suppose we write the information(data) in bill vouchers, then we use carbon sheet at the same time we got the two copy of the same data records in your note.

Likely i want solution for this problem...
Oct 9 '09 #3

TheServant
Expert 100+
P: 1,168
@sganeshsvk
Did you read Atli's article on replication? Anyway, realistically nothing will ever be *exactly* the same time. The nano-seconds it takes to move to a next line render anything occuring at exactly the same time impossible (even on two different servers the chances are about 0). If you take a timestamp using PHP then make it a variable, you can insert that into the time/date field of your two databases, and that will say the same time.

If you don't use PHP for your timestamp rather than MySQL, you need to either use mysql replication function, or backup your database to another manually.

Personally I would use Atli's code and go with PHP looking after my timestamps, as replication is a bit fiddly.
Oct 9 '09 #4

Atli
Expert 5K+
P: 5,058
@sganeshsvk
So you want the query executed on two separate database on the same MySQL server?

I don't see a way to do that with a single, plain, SQL query, but you could of course do this using a procedure or possibly a trigger.

However, that is basically the same thing being done by my previous PHP example, just coded inside MySQL rather than PHP. The idea is pretty much the same. (PHP is much more flexible, tho)

Or am I not getting what you are talking about?
Oct 9 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.