CODEWITHSUNDEEP
phpmysqldatabase
NeonOriginal
NeonOriginal

Reputation: 15

Database to Database communication

I'm in the planning stage of building a website for a company that I work with. This company is an Electronics wholesaler and wants to get a better web presence. Their current POS system uses PervasiveSQL as the database. I will be using MySQL for the website DB and PHP for the language.

I want to integrate this website with this current database however; i want to have the website use it's own database just in case an intrusion happens, everything won't be compromised. When the website takes in an order I want it also to be duplicated into their POS system.

How do I go about accomplishing this? Do I just write a second INSERT statement in my code and insert into both databases? Is there a method I can use that when the database has something inserted into a table it will trigger an event that takes the information inserted and place it into another database? I ask this because when a new product or customer account is added into the original POS system I want that information to be placed in the websites database.

Upvotes: 1

Views: 1334

Answers (2)

crafter
crafter

Reputation: 6296

You don't say how you will code your database connection, but it is quite possible to connect to more than one database at the same time as there are no restrictions.

Let's just assume you are using PDO.

To connect to 2 databases, you could use :

$db1 = new PDO('mysql:host=localhost;dbname=database1;charset=utf8', 
'username1', 'password1');

$db2 = new PDO('mysql:host=localhost;dbname=database2;charset=utf8', 
'username2', 'password2');

This will result in 2 database references $db1 and $db2

To insert into both databases, you then use the references in your query submission

$result1 = $db1->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$result2 = $db2->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");

Upvotes: 0

GrandmasterB
GrandmasterB

Reputation: 3455

From what you describe, what you might do is have the web site access the POS system's database using read-only credentials. Then you wouldnt need to be duplicating data. If there was an exploit of the web site, the attackers would not be able to gain write access to the POS database.

If you need to write to the POS database from the web app, and are concerned about giving it direct access, you can put a web service in between the two (accesible only from your internal network), and call that rather than accessing the POS database directly. Actually, putting a web service in place would be a good way to read from the POS system as well.

Basically, duplicating data and keeping things synchronized can be a big problem thats best to be avoided given the option.

Upvotes: 1

Related Questions