PHP MySQL Website to External SQL System Integration

Question

Hello,

I am after opinions from some expert web developers as to the best solution to my problem.

THE SETUP

In my job, I look after an online shop, which is based upon osCommerce, with many additional features and tweaks. This is on Linux shared hosting and uses PHP and MySQL as its main technologies.

We are going through the process of upgrading our stock and order system, which is Microsoft SQL based and runs on a data server on our network (a Dell Server PC).

THE PROBLEM

I would like to achieve better integration between the stock/order system and our website, such as to get up-to-date stock information, check prices, etc. With the eventual aim of getting customer data linked as well.

MY POSSIBLE SOLUTION

I currently have XAMPP running on our SBS server for development, which I use to test new code, before uploading to the live site.

I have written a PHP file on this server, which connects to the SQL server and runs various SQL queries, taking in parameters in the $_GET array, retrieving the result as an associative array, JSON encoding the result and echoing the encoded string.

The website just uses something like this to get and use the result:

$result = file_get_contents('http://SBS-SERVER-IP/getinfo.php?partenquiry=' . $cleanStringPartNumber);

if ($result) $stock_info = (array) json_decode($result);

I thought this would be a decent solution as any logins for the SQL, even the fact that it is SQL is not exposed to the website, so if the website was compromised, it shouldn't compromise our system.

I will be making sure that the login for the SQL server only has access to SELECT data as I don't want to update/insert/delete anything as it may cause problems with the stock/order system.

Also, I was thinking of caching the results somewhere, so our stock/order system doesn't suffer performance issues.

I am sure there are many ways of passing the data between the 2 systems, but I want to be sure I am using a solution that is secure, using the most efficient and industry-standard methods of carrying this out.

I see technologies such as cURL, SOAP, XML, JSON, etc and wonder if any of these are ideal.

What are your thoughts?

Answer 1

I would use https if possible to have a secure communication between both servers. Which technologie you use is up to you and the framework you are willing to learn. JSON is one of the easiest way to send data from a to b.

SOAP practicly is XML but you don't have to bother with the XML itself. Using SOAP you can send and store Objects.

Using php's serialize and unserialize you can transform objects too and send them and afterwards store the content of that object into the database.

It's more about preference and productionspeed.

Learning a new framework takes some time at first but can make you more productive afterwards.