Create JSON from MySQL To use with AJAX

Now a days most websites use the power of AJAX to make their website more lively and productive. To transfer large amount of raw data, that JavaScript can process, the best way is to transfer it as a JSON file. It is quite easy for a PHP script to generate a JSON file from raw data. This data can be received using a suitable AJAX call. As JavaScript can easy parse JSON object, the rest of the processing with the received data can be done using JavaScript.


The main advantage of using this technique is that, the processing done on the server is minimal. It just fetches raw data and returns to the calling page. Then the JavaScript processes the code on the client browser. It can make the page load faster, and moreover as a developer, the projects implementing this technique are easy to develop, test and deploy.

Here a PHP function 'jsonFromQuery' is defined which accepts the MySQL query result as the parameter and returns the JSON encoded string. The PHP function along with its usage is given below.

<?php
function jsonFromQuery($result) {
    if(mysql_num_rows($result)>0) {
        while($r = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $json[] = $r;
        }
    } else {
        $json = "Table is empty";
    }
    return json_encode($json);
}
?>

You might have to replace the hostname, username, password, database name, query string etc to suit your needs.

<?php
$conn = mysql_connect("hostname","username","password");
mysql_select_db("databasename",$conn);
$query = "SELECT * FROM tablename";
$res = mysql_query($query);
$json = jsonFromQuery($res);
header("Content-type: application/json");
print $json;
?>

The logic behind the function is quite simple. It fetches the MySQL result as an associative array. This array is stored one by one in another array called json. This array can be json encoded using the function json_encode to get the required result.