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"}
]
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"
}]
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;
}
Comments
jdon_encode is avaliable in php 5.2 and above. for older vesions this is very useful!
json_encode is avaliable in php 5.2 and above. for older vesions this is very useful!
a, strong, em, b, i, code, pre, pandbrallowed. Other tags will be shown as code(< will become <). Urls, Line breaks will be auto-formated.