Creating a Distributed Web App with Javascript, PHP and MySQL

On most web sites it is the server that does all of the work. However, it is possible to spread the load to client computers by combining AJAX and JSON.

One of the most powerful aspects of any web application is the ability to obtain information from a database and pass it to a web browser and then send any updated data back again. Typically a web page may send a request to a server, then that request can then be processed on the server and then the result is returned to the web browser.

So, for example: a Javascript program may send a request for information to a server via Ajax; the request is processed by a PHP script which queries a MySQL database; the PHP script returns an output in HTML; the Javascript program updates the innerHTML of a div block with the HTML code returned by the PHP script. And, in this example, all of the work is done by the server. However, it is possible to spread the load by returning the data in JSON format and enabling the client to do some of the work.

Converting MySQL Data To a JSON Object

The process for converting a set of MySQL records into a JSON object is quit simple: the Javascript program sends an Ajax request to a PHP script; the PHP script sends a query to the MySQL database and turns the result into a JSON format string; the PHP script returns the string to the Javascript program; the Javascript program converts the JSON format string into a JSON object and processes it as required.

The Javascript Request

The Javascript program simply: creates an HTTP request object; sends a request to the server (as form using the post method); waits for a response; creates a JSON object from the text returned by the Ajax request by using the parse function. The code itself is:

var data = {};
if (window.XMLHttpRequest) http=new XMLHttpRequest();
else http=new ActiveXObject("Microsoft.XMLHTTP");
var url = "json_mysql.php"; //Define the php script to be used
var params = "table=content"; //Select the table to be used
http.open("POST", url, true);
http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
http.onreadystatechange = function() {
if(http.readyState == 4 && http.status == 200) {
data = JSON.parse( http.responseText );
document.write("There are " + data.length + " records");
}
}
http.send(params);

It is, of course, important to remember that the request is run asynchronously and so the data will only be available after the request has finished processing. It is also important not to send the full SQL from the web page. Doing that may enable malicious delete or update statements to be sent.

The PHP Script

The purpose of the PHP script is to: make a connection to the database; create a SQL statement from the request sent by the Javascript; send the query to the database; convert the returned result into a JSON statement. Fortunately this can all be done with standard PHP functionality:

$host = "localhost";
$db = "mydb";
$user = "myuid";
$password = "mypassword";
$conn = mysql_connect($host, $user, $password) or die ('Error connecting to mysql');
$db_selected = mysql_select_db($db, $conn);
$sql = "select * from {$_REQUEST['table']}";
$result = mysql_query($sql);
while($row = mysql_fetch_object($result)) $data[] = $row;
echo json_encode($data);

And so, with those few lines of code, the web page developer is able to distribute some of the data processing from the server onto their users' client computers.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---