MySQL to JSON with PHP

September 23, 2009 • Php by Emanuele Feronato • 5 Comments

Ugly title for a post, really… but I did not find any function to convert the result of a MySQL query directly into JSON notation, so I made my own function.

I really needed it today, so I am sharing it with you. It’s not a everyday use function, but I am sure some of you will find it useful, just like it happened with Sending email with multiple attachments with PHP.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

function mysql2json($mysql_result,$name){
     $json="{\n\"$name\": [\n";
     $field_names = array();
     $fields = mysql_num_fields($mysql_result);
     for($x=0;$x<$fields;$x++){
          $field_name = mysql_fetch_field($mysql_result, $x);
          if($field_name){
               $field_names[$x]=$field_name->name;
          }
     }
     $rows = mysql_num_rows($mysql_result);
     for($x=0;$x<$rows;$x++){
          $row = mysql_fetch_array($mysql_result);
          $json.="{\n";
          for($y=0;$y<count($field_names);$y++) {
               $json.="\"$field_names[$y]\" :	\"$row[$y]\"";
               if($y==count($field_names)-1){
                    $json.="\n";
               }
               else{
                    $json.=",\n";
               }
          }
          if($x==$rows-1){
               $json.="\n}\n";
          }
          else{
               $json.="\n},\n";
          }
     }
     $json.="]\n};";
     return($json);
}

The first parameter is the result of the query, without any parsing, just the output of mysql_query function, the second parameter is the name you want to give to your JSON object.

Returns a string with the JSON notation of the result.

Rate this post: 1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 3.43 out of 5)
Loading ... Loading ...
Be my fan on Facebook and follow me on Twitter! Exclusive content for my Facebook fans and Twitter followers

This post has 5 comments

  1. Ransom Weaver

    on September 24, 2009 at 2:38 am

    Don’t forget AMFPHP’s great JSON gateway. If you have an AMFPHP service e.g. “getMyStuff” a call to the gateway will return a JSON version. Example:

    function getMyStuff($usr,$pwd) {
    // get the stuff with php from mysql
    return $mySqlResult;
    }

    example JSON call:

    http://somesite.com/amfphp/myservices/getMyStuff/myusrname/mypwd

    You can also set this up to use POST vars instead of inline GET

  2. Stephen McIntyre

    on September 24, 2009 at 10:00 am

    One thing I’d suggest would be to add the line $count_names = count($field_names); after the line beginning with $rows = mysql_num… and refer all of your references to count($field_names) instead to the $count_names var.

    Otherwise you call the count() function twice for every occurence of $x in each $y, which can add up, especially if you’re querying the number of fields and values in a database.

    Otherwise, keep up the great posts, especially with PHP :)

  3. Caleb Wright

    on September 24, 2009 at 6:37 pm

    PHP 5.2+ has a function called json_encode(). So you can shorten everything to this:

    function mysql2json($mysql_result,$name) {
    $result = array($name => array());
    while ($row = mysql_fetch_assoc($mysql_result))
    $result[$name][] = $row;
    return json_encode($result);
    }

    (I haven’t tested it, but it should work).

  4. jocuri

    on December 13, 2010 at 4:52 pm

    caleb, it tried your code, and is not working

  5. GeoBob

    on June 21, 2011 at 1:40 pm

    I think there is an errant semicolon on line 31 of your code. I don’t think there should be a semicolon inside the quotation marks. Take it out, and you get JSON code that validates.

    Thanks for your post! It’s been extremely helpful.

< >