Database Copy

PHP
1

Hi there,

I am building a website with multiple platforms of software to support the various functions that we need. And each of these platforms are built in PHP & MySQL.

I need to build a PHP Script that will take data from an HTML form and post it to each of the SQL databases that we use so that the same form doesn’t have to be completed for each of them… This will be used to copy user-registration data from 1 platform to another.

Example:

HTML Form will collect Name, Email Address, Desired Password etc…

On submit, the form will action the PHP Script which will post the details entered into 3 databases.

All databases are on the SAME server but have different database names and different tables. For instance…

The script will need to take the data entered in the form’s “Name” field and post it to the databases as follows:

database1 - table = users, field = name
database2 - table = user_data, field = first_name
database3 - table = users, field = username

I hope that makes sense.

Is there a way I can do this - All of the databases are on the same server and have the same username and password.

2

Not sure why they can’t be one table… but in PHP you can start like this:


// Untested of course

define('DBUSER','you');
define('DBPASS','50m3th1Ng-__u__-c@Nt-gu355'); 

function dsn( $dbname ) {
	return 'mysql:dbname=' .$dbname. ';host=localhost';
}

$dbh1 = new PDO(dsn('tbl1'), DBUSER, DBPASS);
$dbh2 = new PDO(dsn('tbl2'), DBUSER, DBPASS);
$dbh3 = new PDO(dsn('tbl3'), DBUSER, DBPASS);

// When the form is submitted
$dbh1->exec('SQL UPDATE');
$dbh2->exec('SQL UPDATE');
$dbh3->exec('SQL UPDATE');
3

Lets hope the credentials are the same for each database… else you can just alter the suggested dsn function to return the correct ones.

You might to have to put some thought into what happens if dbh1 inserts ok, but dbh2 fails - can you roll back on multiple databases?

4

Can you not change the role of two of the servers to slaves to one master? This way you will only need to update one ‘true’ source of data.

Check out Replication over at the MySQL manual. :slight_smile: