CODEWITHSUNDEEP
phpmysqljson
Tomas Smith
Tomas Smith

Reputation: 427

Fetching mysql query to JSON

Is there any effective way to generate JSON out of MYSQL query like this?

SELECT * FROM player ORDER BY score DESC LIMIT 10

because all I have found is this but for me it seems that there must be some easier way to get around:

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;

Upvotes: 1

Views: 7707

Answers (3)

biziclop
biziclop

Reputation: 14596

Use the json_encode function: http://php.net/manual/en/function.json-encode.php

  • put the whole result into a php array, and json_encode the whole

  • or if your result is huge,

    • first echo '[';
    • then echo each row independently encoded with json_encode (and don't forget to echo ','; when necessary)
    • finally close the json with echo ']';

Upvotes: 2

dldnh
dldnh

Reputation: 8951

I would just use json_encode on the object that comes back from mysql_fetch_assoc

https://www.php.net/manual/en/function.json-encode.php

Upvotes: 3

safarov
safarov

Reputation: 7804

Simply use json_encode and json_decode for array to json string convert.

For your example it will be something like this:

 ...
 $rows = array();
 while($data = mysql_fetch_assoc($data_sql)) {
      $rows[] = $data;
 }
 echo json_encode($rows);
 ...

Upvotes: 3

Related Questions