The Wahyudiharto's Blog

Accessing single core database records from several existing online systems is a top application development priority. There are no others choice to retrieve rows on-the-fly accurately without having a pure real live connection. In a corporation which implementing single sign-in on some separated (web based) applications, each login page modules must connected to single dedicated users table in data center in order to verify user’s right. This is a sample about the use of clustering application technique.

Integrating multiple online systems so that it have both data connection; one in local & one in other host can be made with 2 styles; simple & difficult. I said it simple if there’s less or no security policy restriction on both servers. For example, let assume that it’s possible for server B granted request query only from server A. On this condition, it’s easy as provide two common connections in a configuration laid in server A without paying attention on security issues in server B.

<?php
$linkA = mysql_connect("hostA", "mysql_userA", "mysql_passwordA");
mysql_select_db("databaseA", $linkA);
$resultA = mysql_query("SELECT * FROM tableA", $linkA);
$num_rowsA = mysql_num_rows($resultA);

$linkB = mysql_connect("hostB", "mysql_userB", "mysql_passwordB");
mysql_select_db("databaseB", $linkB);
$resultB = mysql_query("SELECT * FROM tableB", $linkB);
$num_rowsB = mysql_num_rows($resultB);

echo "Host A: $num_rowsA Rows\nHost B: $num_rows Rows";
?>

This also can be done if you have designed the network and servers infrastructure by your own self as secure as it should be, by using VPN line or granted all privileges in server B only from server A. But, be sure that you must have root privileges on both servers to configure this out. All of above is a simple one.



But, what if you have no root privileges at all, or there’s a bit complicated situation which server A currently reside on an external web hosting company and server B is behind a firewall on local network (no port allowed except HTTP)? Well … well, this is not a simple way. Although that it’s not a simple, but it has another solutions. Solve the problem - I called it – with bridging method.

Bridging method basically based on SOAP technique. You can clearly read my previous article on this blog about what is SOAP actually including my extra specimen code. This method is simply as using XML to transferring the data. It’s a RMI like on Java (Remote Method Invocation) or known as application server. From picture above, a request query sent to Server B from Server A. Server B recognizing accepted command & continuing to process the query. Then result query will be returned back to Server A. Anyway, it might have a bit degraded performance, but believes me; I don’t want to talk about it right now.

Back to topic, at least, result query retrieval mode on web based application divide into 2 conditions which is single & multiple results. A single result can directly passing to a variable or text box. A more complex is multiple results such as menu/list box. Watch picture below.



Picture above explains a page from server A displaying result query obtained from data bridging from other host (server B). First object (the text box) hold only one row result returned, while combo box can hold many records. Here, I’ll explain what you have done to solve this. To get started, first off all, you need the same PHP SOAP library taken from my previous article. I renamed this library as “bridge.soap.php” & let it stored on both servers. On script in Server A, you must define the initialization:

require_once('bridge.soap.php');
$serverpath ='http://serverB/bridge.php';
$namespace="urn:xmethods-SOAPWebService";

$serverpath is variable hold a target application server on server B. okay, let’s straight more deep, if you need to request a single row result query then this below is the template:

$perintah="SELECT * FROM tableB WHERE fieldB='" . $varB ."'";
$field='fieldB';
$param= array('perintah'=>$perintah,'field'=>$field);
$client=new soapclient($serverpath);
list($field) = $client->call('get_row',$param,$namespace);
unset($client);

$field variable explain what field on database Server B that will be retrieved. And so on, the result query will also be stored on this same variable name (just to make easier). Anyway, it’s quite different if you need to retrieve multiple fields record by bridging & store it to combo box. Check below template:

$perintah="SELECT * FROM tableB order by fieldB1";
$field='fieldB1# fieldB2';
$param= array('perintah'=>$perintah,'field'=>$field);
$client = new soapclient($serverpath);
$record = $client->call('get_loop',$param,$namespace);
$row=explode("#",$record);
for ($x = 0; $x < count($row); $x++) {
$value='';
$col=explode("|",$row[$x]);
for ($y = 0; $y <= count($col); $y++) $value=$value . $col[$y] . '|';
$value=substr($value, 0, -2);
$value_kode=explode("|",$value);
echo("<option value='$value_kode'>$value</option>");
}
unset($client);

Same as first template, $field also hold what field that will be retrieved but it can contains more than single field. For example above, it declared to retrieve fieldB1 and fieldB2 (with # as delimiter). This is similar to query statement:

SELECT fieldB1,fieldB2 FROM tableB order by fieldB1

2nd template actually based on 1st skeleton template. I modified 1st template into 2nd so that it now possible to gain multiple fields. Even both templates look similar, but it’s different; while the 1st calling get_row function, the 2nd calling get_loop function. These both functions are exists on server B. It’s a kind a RPC (Remote Procedure Call) function as a part of application server. For instances, below script are template of SOAP server (I named as bridge.php) only reside on server B (the function definition called from Server A):

<?php
function get_row($perintah,$field)
{
$param = array();
$fieldno=explode("#",$field);

$hasil=mysql_query($perintah) or die($perintah);
while ($row=mysql_fetch_array($hasil))
{
for ($x = 0; $x <= count($fieldno); $x++) array_push($param,$row[$fieldno[$x]]);
}
return $param;
}

function get_loop($perintah,$field)
{
$param = array();
$param_row='';
$fieldno=explode("#",$field);

$hasil=mysql_query($perintah) or die($perintah);
while ($row=mysql_fetch_array($hasil))
{
$param_col='';
for ($x = 0; $x < count($fieldno); $x++) $param_col=$param_col . $row[$fieldno[$x]] . '|';
$param_col=substr($param_col, 0, -1) . '#';
$param_row=$param_row . $param_col;
}
$param_row=substr($param_row, 0, -1);
return $param_row;
}

require_once('bridge.soap.php');
$linkB = mysql_connect("hostB", "mysql_userB", "mysql_passwordB");
mysql_select_db("databaseB", $linkB);

// create the server object
$server = new soap_server;

// register the lookup service
$server->register('get_row');
$server->register('get_loop');

// send the result as a SOAP response over HTTP
$server->service($HTTP_RAW_POST_DATA);
?>

So, now it looks quite clear, huh? The application on server A is connecting to server B through bridge.php. The bridge.php become as database gateway, receive request, querying & send the result back. This is quite stupid, but also more reliable than a technique which I describe earlier (synchronization method) on this blog. Both of techniques were currently used on some of my projects showed on my homepage. You can simulate all appropriate to your existing situation. If you have other ideas or techniques, please let us know. See you on my next experiences…

Labels: MySQL, PHP, Programming, Web Programming

---

PS: If you've benefit from this blog, you can support it by making a small contribution.