sql2json() - Converts SQL Results to JSON String

sql2json() converts the result of the given SQL query to its equivalent JSON string.

See Demo

This function will take a SQL query as it argument, execute it, fetch its result and convert it to a JSON string and then return the JSON string. This is very useful if you are creating Ajax applications and decide to use JSON over XML. If you have decided to use XML, take a look at my XML Parser for JavaScript instead.

Example

Query : SELECT user_id,user_name FROM dummy_data_users WHERE user_status='1'
Code : sql2json("SELECT user_id,user_name FROM dummy_data_users WHERE user_status='1'");
JSON : 

[
	{"user_id":"1","user_name":"Binny"},
	{"user_id":"2","user_name":"Bill Gates"},
	{"user_id":"3","user_name":"George Bush"},
	{"user_id":"4","user_name":"Secret Agent"}
]

See

Query : SELECT * FROM dummy_data_users WHERE user_id='1'
Code : sql2json("SELECT * FROM dummy_data_users WHERE user_id='1'");
JSON : 

[{
	"user_id":"1",
	"user_name":"Binny",
	"user_email":"myfakeemail@nowhere.com",
	"user_site":"http://www.bin-co.com/",
	"user_info":"Hi!",
	"user_status":"1"
}]

See

Code

<?php
//Function will take an SQL query as an argument and format the resulting data as a 
//    json(JavaScript Object Notation) string and return it.
function sql2json($query) {
    $data_sql = mysql_query($query) or die("'';//" . mysql_error());// If an error has occurred, 
            //    make the error a js comment so that a javascript error will NOT be invoked
    $json_str = ""; //Init the JSON string.

    if($total = mysql_num_rows($data_sql)) { //See if there is anything in the query
        $json_str .= "[\n";

        $row_count = 0;    
        while($data = mysql_fetch_assoc($data_sql)) {
            if(count($data) > 1) $json_str .= "{\n";

            $count = 0;
            foreach($data as $key => $value) {
                //If it is an associative array we want it in the format of "key":"value"
                if(count($data) > 1) $json_str .= "\"$key\":\"$value\"";
                else $json_str .= "\"$value\"";

                //Make sure that the last item don't have a ',' (comma)
                $count++;
                if($count < count($data)) $json_str .= ",\n";
            }
            $row_count++;
            if(count($data) > 1) $json_str .= "}\n";

            //Make sure that the last item don't have a ',' (comma)
            if($row_count < $total) $json_str .= ",\n";
        }

        $json_str .= "]\n";
    }

    //Replace the '\n's - make it faster - but at the price of bad redability.
    $json_str = str_replace("\n","",$json_str); //Comment this out when you are debugging the script

    //Finally, output the data
    return $json_str;
}
Subscribe to Feed