How to Convert Data from MySQL to JSON using PHP

In PHP, Converting Data from MySQL to JSON Format is one of the prominent tasks in Web Development. JSON has gained popularity over the years and is preferred over xml as data exchange format between web applications.

Using json format has its own advantages like being light weight, ability to store complex data structures in plain text and very human readable. Earlier we have discussed about converting json to mysql data here. Now let us see how to convert mysql result set to json in php.

Create MySQL Database

Here is the MySQL Database I'm going to use as an example. Run these sql commands to create the Database.

Read Also:
CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `employee_id` int(4) NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(60) NOT NULL,
  `designation` varchar(30) NOT NULL,
  `hired_date` date NOT NULL,
  `salary` int(10) NOT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_employee` (`employee_id`, `employee_name`, `designation`, `hired_date`, `salary`) VALUES
(1, 'Steve', 'VP', '2013-08-01', 60000),
(2, 'Robert', 'Executive', '2014-10-09', 20000),
(3, 'Luci', 'Manager', '2013-08-20', 40000),
(4, 'Joe', 'Executive', '2013-06-01', 25000),
(5, 'Julia', 'Trainee', '2014-10-01', 10000);

Convert MySQL to JSON String in PHP

Here are the steps in converting mysql to json string with php.

Step 1: Open MySQL Database Connection in PHP

First establish connection to mysql database using mysqli_connect() function.

<?php
    //open connection to mysql db
    $connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));
?>

Step 2: Fetch Data from MySQL Database

After opening the connection, fetch the required table data from mysql db. Using the php function mysqli_query(), I'm going to fetch all the rows from the table 'tbl_employee'.

<?php
    //fetch table rows from mysql db
    $sql = "select * from tbl_employee";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
?>

Step 3: Convert MySQL Result Set to PHP Array

Next loop through the mysql result set we got from step-2 and convert it to php array.

<?php
    //create an array
    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $emparray[] = $row;
    }
?>

Step 4: Convert PHP Array to JSON String

Next use the PHP function json_encode() to convert the php array to json string. Learn about using php json_decode() function here.

<?php 
    echo json_encode($emparray);
?>

That's it! We have successfully converted mysql to json using php. Here is the complete PHP code for it.

<?php
    //open connection to mysql db
    $connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));

    //fetch table rows from mysql db
    $sql = "select * from tbl_employee";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

    //create an array
    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $emparray[] = $row;
    }
    echo json_encode($emparray);

    //close the db connection
    mysqli_close($connection);
?>

Run the code and you get an output something like this.

php-mysql-to-json-response

Convert MySQL to JSON File in PHP

If you want to write the data from mysql to json file, use this piece of code at the end instead of 'echo' statement.

<?php
    //write to json file
    $fp = fopen('empdata.json', 'w');
    fwrite($fp, json_encode($emparray));
    fclose($fp);
?>
Read Also:

Find this PHP MySQL to JSON conversion tutorial useful? Like us on Facebook and never miss any of our tutorials.

121 comments:

  1. Glad you liked it... Roshini!!!

    Cheers

    ReplyDelete
  2. Your tutorial is very fine

    ReplyDelete
  3. thanks ... just the exact solution i was looking for :)

    ReplyDelete
  4. When I view the php file in browser,some html tags also appear along with json. How to get the output exactly like yours i.e. only json strings.Please help me !!!

    ReplyDelete
    Replies
    1. Hi Akhilis,

      Can you provide the o/p you get? The above code works fine! Couldn't find anything wrong.

      Delete
    2. Create the json file ^^ In there you will get the code that works. When the php file loads the json content it converts the html tags to html code. The json file doesn't :)

      Delete
  5. Hi Valli,

    Thanks for your post. I have a question!
    If you update your MySQL data through queries later, how you can rewrite your JSON file? I want to modify my data on MySQL and then I want to update the JSON file which already exist? Do I have delete the old one or can I update the only data which has been modified on the database? could you please provide me the solutions!

    Many Thanks

    Thanks

    ReplyDelete
    Replies
    1. Hi, updating an existing json file would be cumbersome and the best you can do is to decode, append new data to it, encode and write into a new file. My take is to recreate the file as it is more simple and effective.

      Cheers.

      Delete
  6. $qry1 = "SELECT * from vtiger_picklist_dependency where tabid=? and targetfield=? and sourcevalue=?";
    $res1 = $adb->pquery($qry1,array('21','cf_847','Fixed Gas - Sensors'));
    $dataSet = $adb->query_result($res1, 0, 'targetvalues');

    in target values data are in json format and i want to convert it into sequence form in php i used json_decode but not working plz suggest me some solution ?

    ReplyDelete
  7. $qry1 = "SELECT * from vtiger_picklist_dependency where tabid=? and targetfield=? and sourcevalue=?";
    $res1 = $adb->pquery($qry1,array('21','cf_847','Fixed Gas - Sensors'));
    $dataSet = $adb->query_result($res1, 0, 'targetvalues');$qry1 = "SELECT * from vtiger_picklist_dependency where tabid=? and targetfield=? and sourcevalue=?";
    $res1 = $adb->pquery($qry1,array('21','cf_847','Fixed Gas - Sensors'));
    $dataSet = $adb->query_result($res1, 0, 'targetvalues');

    i want to convert my json database values in proper format with numbering i used json_decode but not working in targetvalues there is data in json format so plz help me ?

    ReplyDelete
    Replies
    1. Can you provide the data you get from DB? You can also use json_last_error() function to check if there is any error while decoding the json.

      Cheers.

      Delete
  8. This works really well, but I am getting some extra characters at the very beginning of the JSON return. The very beginning of the return looks like this: [[],{"serial":

    "Serial" is the first column name in my database, but the [], that comes before it is a mystery to me.

    ReplyDelete
    Replies
    1. Hi, thanks for pointing out... there is a minor mistake in the code and I have updated it now. You have to edit the line $emparray[] = array(); to $emparray = array(); and it will remove the empty braces in the json o/p.

      Cheers!

      Delete
  9. Your code is not working in heavy data

    ReplyDelete
    Replies
    1. Can you please tell me what's the error you got?

      Delete
  10. EXACTLY what i was looking for, copy/past start : Success !! Thanks

    ReplyDelete
  11. May I know how can I get above results into textboxes in a php page ?

    ReplyDelete
    Replies
    1. which data you mean? one from mysql or the final json string? Either way you can simply echo the variable in the value of the textbox.

      Say you have a value stored in a variable $name then do it like this,
      <input id="txt" type="text" value="<?php echo $name; ?>" />

      Delete
  12. Your code works great, thank you!

    I do need to add a label to the final array so the end result looks like this as right now the first curly bracket and "data": elements aren't appearing. Thanks again!

    {
    "data": [
    {
    "name": "Tiger Nixon",
    "position": "System Architect",
    "salary": "$320,800",
    "start_date": "2011/04/25",
    "office": "Edinburgh",
    "extn": "5421"
    },
    {
    "name": "Garrett Winters",
    "position": "Accountant",
    "salary": "$170,750",
    "start_date": "2011/07/25",
    "office": "Tokyo",
    "extn": "8422"
    }]
    }

    ReplyDelete
    Replies
    1. Can you provide the code you have tried?

      Delete
    2. This is exactly my problem too! I need the json to be in this format where the array has a name, how would we do that?

      Delete
    3. Try this, echo json_encode(array('data' => $emparray));

      Cheers.

      Delete
    4. I am not getting by using this, I am getting like
      {
      "data":
      {
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
      },



      }

      I wanted like

      {
      "data": {
      {
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
      }
      }
      }

      Could you please tell me Valli Pandy

      Delete
  13. Replies
    1. thank you, you're a life save, this is simple and very helpful

      Delete
    2. thank you, this is simple and very helpfull

      Delete
  14. Very nice post! It is exactly what I need.
    Just a question: how can I hide ("hostname","username","password","db_employee")?

    Thanks!

    ReplyDelete
  15. Very nice post!
    It is exactly what I need.

    Just one question: how can I hide ("hostname","username","password","db_employee")?

    Thanks!

    ReplyDelete
    Replies
    1. Hi! This is not an insecure way. Unless you echo the credentials/ your web server stops parsing php no one will know the password. But you can organize it in some better way like with a config file. That's what MVCs do.

      Check this thread to know more in detail

      http://stackoverflow.com/questions/5962450/hide-database-login-information-in-php-code

      Cheers.

      Delete
  16. thank you, this is simple and very helpfull

    ReplyDelete
  17. Thanks a lot for providing this, it has helped me a lot. But there is one thing... How would I get this to return a json response in the form:

    { "Employees" : [{ "Employee ID": "1", "Employee Name": "John Smith" } ]}

    instead of:

    [{ "Employee ID": "1", "Employee Name": "John Smith" }]

    Where 'Employees' is an object containing an array of elements, so that the array has a name?
    I'm pretty new to PHP and I need the json response to be in this form so it can work with my android java code, which I'm also new to haha.. So any help would be great, thanks again

    ReplyDelete
    Replies
    1. Hi! Encode json like this, echo json_encode(array('Employees' => $emparray));

      It will create json in the format you prefer.
      Cheers.

      Delete
  18. how can i send this created json file to mobile application there is a socket opened with static ip and port can u help me please! and thank you nice tutorial

    ReplyDelete
  19. "movies": [
    {
    "id": "343",
    "title": "Jack Pierce, the Maker of Monsters",
    "year": 2015,
    "mpaa_rating": "Unrated",
    "runtime": 82,
    "release_dates": {
    "theater": "2015-10-02"
    },
    "ratings": {
    "critics_score": -1,
    "audience_score": 0
    },
    "synopsis": "A documentary drawn from recorded interviews, historical footage, and hundreds of photos including Jack Pierce's personal scrap book.Jack Pierce, the legendary Universal makeup man, was the creator of Dracula, Frankenstein's Monster, The Mummy, The Wolfman, Bride of Frankenstein, and countless other monsters that have stood the test of time.",
    "posters": {
    "thumbnail": "http://d3biamo577v4eu.cloudfront.net/static/images/redesign/poster_default_thumb.gif",
    "profile": "http://d3biamo577v4eu.cloudfront.net/static/images/redesign/poster_default_thumb.gif",
    "detailed": "http://d3biamo577v4eu.cloudfront.net/static/images/redesign/poster_default_thumb.gif",
    "original": "http://d3biamo577v4eu.cloudfront.net/static/images/redesign/poster_default_thumb.gif"
    },
    "abridged_cast": [
    {
    "name": "Nikki Blakk",
    "id": "771752701"
    },
    {
    "name": "Ernie Fosselius",
    "id": "364601012"
    }
    ],
    "links": {
    "self": "//api.rottentomatoes.com/api/public/v1.0/movies/771431180.json",
    "alternate": "//www.rottentomatoes.com/m/jack_pierce_the_maker_of_monsters/",
    "cast": "//api.rottentomatoes.com/api/public/v1.0/movies/771431180/cast.json",
    "reviews": "//api.rottentomatoes.com/api/public/v1.0/movies/771431180/reviews.json",
    "similar": "//api.rottentomatoes.com/api/public/v1.0/movies/771431180/similar.json"
    }
    },

    ReplyDelete
  20. Dear Sir,

    My Chart output should be http://onnetsourcing.com/vivera/chart.html . Here I have manually added the data in the code. Actually this process should be automatic. I have taken data from mysql table but I could not enter the data in the matrix format. Please help me to get the data for this chart in matrix format or any other technique. The chart should be as in the given URL. Please help me on this.

    In A block if 1 floor in completed which should go one up. Please help me to get this done.

    In the chart script I have manually added below data
    ['Date','1st Floor', '2nd Floor', '3rd Floor', '4th Floor', '5th Floor', '6th Floor', '7th Floor', 'Tarrace Floor'],
    ['A Block', 1, 1, 1, 0, 0, 0, 0, 0],
    ['B Block', 1, 1, 1, 1, 1, 1, 1, 1],
    ['C Block', 1, 1, 1, 1, 1, 1, 1, 1],
    ['D Block', 1, 1, 1, 1, 1, 1, 1, 1],
    ['E Block', 1, 1, 0, 0, 0, 0, 0, 0],
    ['F Block', 1, 1, 0, 0, 0, 0, 0, 0],
    ];

    The thing is this data has to be get automatically from my mysql table. Please help me to get this.

    Regards,
    Krishnaprasad PB

    ReplyDelete
    Replies
    1. Hi! From what I understood, you don't have any mysql database setup. So first create a mysql table with the same no. of columns you have in the matrix. Feed the floor details like '1' (for completed) and '0' (not yet) and pull the data from mysql with some server script like php and pass to the chart app.

      I hope this makes sense. Let me know if you need any further help.

      Cheers.

      Delete
  21. I need to export huge amounts of JSON at a time. Do you know of a library that can do this?
    While your script is a great "learning" example, in the real world, it will surely choke on huge data-sets. To export these 5 employees as json is a trivial task, but this script will run out of memory when you try to export a real employee dataset which can have tens of thousands of rows, and 20+ TEXT and VARCHAR columns, joined in from other tables.

    ReplyDelete
  22. Many thanks. Really helpful having this all in one place (i.e. the connection setup with the query).

    ReplyDelete
  23. Thanks u very much that very useful for me

    ReplyDelete
  24. how to get Particular data from JSON to Update and delete using PHP &mysql

    ReplyDelete
  25. How to get Particular data from JSON for Update & Delete operations in PHP

    ReplyDelete
  26. Hi Valli
    Your tutorial is realy helpful but i want to know that how can use these codes using creating a function.i try but after creating function i did not get any json response only a blank page

    ReplyDelete
    Replies
    1. Hi! Sry for the late reply... You could try something like this,

      function mysqlToJson($result)
      {
          ...

          return json_encode($emparray);
      }

      echo mysqlToJson($resultset);

      Delete
  27. Hey... Really good..!!! Just what I needed.

    If you can help I have another requirement. One of my db tables contains the filepath of an image held on the filesystem. If I use this pathname to access the image somewhere between Step 3 and Step 4 is there any way I can encode it such that I can include it in the JSON download..??

    ReplyDelete
  28. Great Post...!! One of my database tables holds a filepath to an image held in the file system. Can I use PHP to access this image and include it in the JSON download..??

    ReplyDelete
    Replies
    1. Not the image itself but you can store the image file path in the json just like any other fields.

      Delete
  29. Thank you, nice tutorial,its working perfectly

    ReplyDelete
  30. Hey I can't get the mysql commands to work, there is a syntax error in the last line, but I don't know which one it is. I'm using terminal. Does anyone have a fix for that?

    ReplyDelete
    Replies
    1. Hey! Thanks for letting me know. I have fixed the sql command. Please try now... it should work:)

      Delete
  31. Hi Valli, I keep getting an error message with the sql commands you've provide. The error is the last line, do you have a fix for that? Thanks this is an awesome post/

    ReplyDelete
  32. Hi! Fixed the sql commands. Please try again... it works now!!!

    ReplyDelete
  33. I am pulling data from a database and parsing the data into a JavaScript file. How would can I modify the current format below.

    Current:

    [
    {
    "year": "2016-03-18 00:00:00",
    "make": "Ford",
    "model": "Escort"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "GMC",
    "model": "Yukon"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Toyota",
    "model": "Rav 4"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Honda",
    "model": "Civic"
    }
    ]

    To look like the format below.

    Modified
    [
    {
    "year": "2016-03-18 00:00:00",
    "make": [
    {
    "Ford": [
    {
    "model": Escort
    }
    ]
    },
    {
    "GMC": [
    {
    "model": Yukon
    }
    ]
    },
    {
    "Toyota": [
    {
    "Model": Rav 4
    }
    ]
    },
    {
    "Honda [
    {
    "Model": Civic
    }
    ]
    }
    ]

    ReplyDelete
  34. I need assistance changing a JSON format the current format looks like the following.
    Current:

    [
    {
    "year": "2016-03-18 00:00:00",
    "make": "Ford",
    "model": "Escort"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "GMC",
    "model": "Yukon"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Toyota",
    "model": "Rav 4"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Honda",
    "model": "Civic"
    }
    ]

    I want to modify the JSON to look like the format below.
    Modified
    [
    {
    "year": "2016-03-18 00:00:00",
    "make": [
    {
    "Ford": [
    {
    "model": Escort
    }
    ]
    },
    {
    "GMC": [
    {
    "model": Yukon
    }
    ]
    },
    {
    "Toyota": [
    {
    "Model": Rav 4
    }
    ]
    },
    {
    "Honda [
    {
    "Model": Civic
    }
    ]
    }
    ]

    Any help would be greatly appreciated.

    ReplyDelete
  35. How do I format my JSON output from as follows?

    [
    {
    "year": "2016-03-18 00:00:00",
    "make": "Ford",
    "model": "Escort"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "GMC",
    "model": "Yukon"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Toyota",
    "model": "Rav 4"
    },
    {
    "year": "2016-03-18 00:00:00",
    "make": "Honda",
    "model": "Civic"
    }
    ]

    To this format.

    [
    {
    "year": "2016-03-18 00:00:00",
    "make": [
    {
    "Ford": [
    {
    "model": Escort
    }
    ]
    },
    {
    "GMC": [
    {
    "model": Yukon
    }
    ]
    },
    {
    "Toyota": [
    {
    "Model": Rav 4
    }
    ]
    },
    {
    "Honda [
    {
    "Model": Civic
    }
    ]
    }
    ]

    ReplyDelete
    Replies
    1. are you missing a close sqare brackets in there? can you present what you want better? currently it is hard to read

      Delete
    2. @Valli Pandy thank you very much for this post. I have got this working. But my problem is I have a rather large DB. 300,000+ and counting rows. So when i try and write this to file it does not work. any ideas? I can get it to wok for 39000 rows, but no greater.

      Delete
    3. Hi! I don't see the need for the format you have specified. Use [] brackets only when it is of an array (ie. have multiple items) but yours contain diff. & single items for 'Make' & 'Model'. Only year can be taken as the top most entity as it is repetitive.

      Cheers.

      Delete
  36. I also get something like that but when try to phrase with jquery it i m not able to can you help please.

    [{"magazineId":"1","name":"Wine","link":"link1","categoryName":"alcohol","subCategoryName":"wine"},{"magazineId":"2","name":"beer","link":"link2","categoryName":"Alchol","subCategoryName":"beer"}]


    var obj = jQuery.parseJSON( '{"magazineId":"1","name":"Wine","link":"link1","categoryName":"alcohol","subCategoryName":"wine"}' );
    alert( obj.name === "Wine" );

    ReplyDelete
  37. 6 hours and over 8 different recommendations, this was the only one that actually worked for us on 5.3.28 .

    Thank you!

    ReplyDelete
  38. hi thanks love your tutorial really helped me until I saw an android app with this URL https://www.reddit.com/.json?& I got confuse this is the out put really need your help

    {"kind": "Listing", "data": {"modhash": "", "children": [{"kind": "t3", "data": {"domain": "bbc.com", "banned_by": null, "media_embed": {}, "subreddit": "worldnews", "selftext_html": null, "selftext": "", "likes": null, "suggested_sort": null, "user_reports": [], "secure_media": null, "link_flair_text": "Possible nuke test", "id": "51uqma", "gilded": 0, "archived": false, "clicked": false, "report_reasons": null, "author": "MAVERICK910", "media": null, "name": "t3_51uqma", "score": 6111, "approved_by": null, "over_18": false, "hidden": false, "preview": {"images": [{"source": {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?s=2a870b97e45633ded1bf3986a3d5d383", "width": 1024, "height": 576}, "resolutions": [{"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=108&s=b078d7b8bb082b3bd59fa7960ef028c2", "width": 108, "height": 60}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=216&s=6abaaa4389a2e021d4558c516e7eb1fc", "width": 216, "height": 121}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=320&s=c0fb905b191b85fa5c7f673d79ea0f6e", "width": 320, "height": 180}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=640&s=0b3ca28121df90a50ee746e1df011f89", "width": 640, "height": 360}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=960&s=739496920de0b0066b7657bc4927946e", "width": 960, "height": 540}], "variants": {}, "id": "bKGRkfe76SRJo_3UQqDNrETdfz2IR6Jp6DgaFK8tG5I"}]}, "thumbnail": "default", "subreddit_id": "t5_2qh13", "edited": false, "link_flair_css_class": "normal", "author_flair_css_class": null, "downs": 0, "mod_reports": [], "secure_media_embed": {}, "saved": false, "removal_reason": null, "post_hint": "link", "is_self": false, "hide_score": false, "permalink": "/r/worldnews/comments/51uqma/large_quake_detected_in_north_korea_bbc_news/", "locked": false, "stickied": false, "created": 1473411649.0, "url": "http://www.bbc.com/news/world-asia-37314927", "author_flair_text": null, "quarantine": false, "title": "Large quake detected in North Korea - BBC News", "created_utc": 1473382849.0, "ups": 6111, "num_comments": 1850, "visited": false, "num_reports": null, "distinguished": null}}

    ReplyDelete
  39. sorry i deleted some in order to make it postable

    ReplyDelete
  40. how can i create one like this

    {"kind": "Listing", "data": {"modhash": "", "children": [


    {"kind": "t3", "data": {"id": "51xo9m", "gilded": 0, "archived": false, "clicked": false, "report_reasons": null, "author": "The_Fassbender", "media": null, "name": "t3_51xo9m", "score": 5862, "approved_by": null, "over_18": false, "hidden": false, "thumbnail": "http://b.thumbs.redditmedia.com/vibty-efWlK4ARYtyYzERpppUg0mWa0JThxa9NFwt5Q.jpg", "subreddit_id": "t5_2qqjc", "edited": false, "link_flair_css_class": null, "author_flair_css_class": null, "downs": 0, "mod_reports": [], "secure_media_embed": {}, "saved": false, "removal_reason": null, "post_hint": "link", "is_self": false, "hide_score": false,

    "permalink": "/r/todayilearned/comments/51xo9m/til_when_nicolas_cage_was_sleeping_at_his_home_he/", "locked": false, "stickied": false, "created": 1473460125.0, "url": "http://newsfeed.time.com/2011/09/16/so-this-apparently-happened-nicolas-cage-awoken-by-naked-fudgesicle-eating-intruder/", "author_flair_text": null, "quarantine": false, "title": "TIL when Nicolas Cage was sleeping at his home, he was awoken by a presence. Cage awoke to find a naked man before his eyes, eating a fudgesicle and wearing his jacket. \"It sounds funny\" Cage said, \"but it was horrifying\". Cage used \"Verbal Judo\" to get the man to leave. Cage now lives on an island.", "created_utc": 1473431325.0, "ups": 5862, "num_comments": 1741, "visited": false, "num_reports": null, "distinguished": null}}] }}

    ReplyDelete
    Replies
    1. Create from where? mysql or something else? And the json you have posted is of multi-level. Creating complex json is really easy but I want you to be more precise with your question. I'm really sorry I can't help with such little info. you provide:)

      Delete
  41. Hi. I get blank space when i try to test it, unfortunately. It's very interesting, because it works before i insert the data into the table. I mean i've got 2 [] brackets on the localhost page. But after i insert it, white space everywhere. Any idea what could be the problem? thx

    ReplyDelete
  42. Hi. I get blank space when i try to test it, unfortunately. It's very interesting, because it works before i insert the data into the table. I mean i've got 2 [] brackets on the localhost page. But after i insert it, white space everywhere. Any idea what could be the problem? thx

    ReplyDelete
    Replies
    1. It must be the data you inserted into db. Can you share me those details?

      Delete
    2. Am having that same problem. What type of data can affect the script?

      Delete
    3. Am getting the same problem. What type of data entered into the dp affects the script?

      Delete
  43. I am getting blank page
    no table data
    Username for mamp will be localhost?

    ReplyDelete
    Replies
    1. Hey! hostname should be of server's. If you try in your system then it should be 'localhost'. username & password are MySQL's.

      For example the connection string for mysql default setting would be,

      mysqli_connect("localhost", "root", "", "db_employee")

      (unless you have changed username & pwd).

      Delete
  44. Just what i have been looking for. Yet to try it though. Thank you very much for the code :)

    ReplyDelete
  45. Wont work with large 1000+ databases

    ReplyDelete
  46. Does not work for actual real size databases 1000+ rows

    ReplyDelete
  47. Is there anything a way where I can do pagination of all these records?

    ReplyDelete
    Replies
    1. Please check these links,
      http://www.kodingmadesimple.com/2015/04/php-codeigniter-pagination-twitter-bootstrap-styles.html

      http://www.kodingmadesimple.com/2015/10/codeigniter-pagination-with-search-query-example-bootstrap.html

      Delete
  48. love thxxx sir solved my big problem :D

    ReplyDelete
  49. Dear Mr. Pandey, thanks a lot, this script is great! I was wondering, this seems to return a list of rows, so is it possible to make it return both columns and rows and encode them into a json file? Your help would be really appreciated!

    I'm trying to make it output to something like this format that google charts (https://developers.google.com/chart/interactive/docs/php_example) asks for:
    {
    "cols": [
    {"id":"","label":"Topping","pattern":"","type":"string"},
    {"id":"","label":"Slices","pattern":"","type":"number"}
    ],
    "rows": [
    {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
    {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
    ]
    }

    ReplyDelete
  50. Dear Mr. Pandey, Thank you for this post! This works exactly as expected. I was wondering though, is it possible to add two separate sections to the json file, specifying columns and rows separately, as google does in this example... https://developers.google.com/chart/interactive/docs/php_example - (- )I am trying to populate a google chart with server side data as specified by them). As a newb, your help would be greatly appreciated!

    { "cols": [
    {"id":"","label":"Topping","pattern":"","type":"string"},
    {"id":"","label":"Slices","pattern":"","type":"number"}
    ],
    "rows": [
    {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
    {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
    {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
    ]
    }

    ReplyDelete
  51. how to get the data if its clickable?

    ReplyDelete
  52. Just a Silly question (I am new to php so pls help)
    Do I need to create a json file named "empdata.json"?
    I am just 13 year old so pls don't think I am mad who even does not know this much

    Thanks in Advance

    ReplyDelete
    Replies
    1. Hi! You don't have to create file. It will be done programmatically. The first script will convert mysql to json and display it in browser window. If you need json to be stored in a file then use the last part of the script i have provided.

      Hope that clears your doubt! Cheers!

      Delete
  53. tq code is working perfectly and my output is this
    [{"id":"1","title":"freshers day photos","body":"www.techtotos.com","date":"25-12-16"},{"id":"2","title":"colllege day photos","body":"www.techtotos.com","date":"22-12-16"},{"id":"3","title":"anual day pics","body":"www.couponadda.in","date":"21-12-16"},{"id":"4","title":"colllege day photos","body":"www.techtotos.com","date":"24-12-16"},{"id":"5","title":"anual day pics","body":"www.couponadda.in","date":"23-12-16"}]

    but i need to name my array as shown in this link http://api.androidhive.info/contacts/ how can i do it here they named there array as contacts
    help me in doing this

    ReplyDelete
  54. i got it just replace line
    echo json_encode($emparray);
    with
    echo json_encode(array('details' => $emparray));
    successfully done

    ReplyDelete
  55. How do you display the JSON string in the browser window?

    ReplyDelete
  56. Valli Pandy

    Please, do you reply to the comments? I have a question please.

    ReplyDelete
  57. Hello there
    I have a problem

    I have the following code:

    $sql = "select * from tbl_employee";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

    //create an array
    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
    $emparray[] = $row;
    }
    echo json_encode($emparray);

    The result is the following:
    [{"0":"12498","ID":"12498","1":"1","author":"1","2":"2017-01-19 20:07:17","date":"2017-01-19 20:07:17", ...

    All fields come twice
    {"0":"12498","ID":"12498",
    "1":"1","author":"1" ....

    Do you have any idea why?

    ReplyDelete
  58. Hello sir. This code is so helpful, thank you so much. But i'm wondering if do you have any idea on how can to convert this into laravel? Thank you for your help.

    ReplyDelete
  59. I need like this type format , please how will i do it/

    [{
    "title": "Dawn of the Planet of the Apes",
    "image": "http://api.androidhive.info/json/movies/1.jpg",
    "rating": 8.3,
    "releaseYear": 2014,
    "genre": ["Action", "Drama", "Sci-Fi"]
    },
    {
    "title": "District 9",
    "image": "http://api.androidhive.info/json/movies/2.jpg",
    "rating": 8,
    "releaseYear": 2009,
    "genre": ["Action", "Sci-Fi", "Thriller"]
    },

    {
    "title": "The Amazing Spider-Man 2",
    "image": "http://api.androidhive.info/json/movies/7.jpg",
    "rating": 7.3,
    "releaseYear": 2014,
    "genre": ["Action", "Adventure", "Fantasy"]
    }, {
    "title": "How to Train Your Dragon",
    "image": "http://api.androidhive.info/json/movies/15.jpg",
    "rating": 8.2,
    "releaseYear": 2010,
    "genre": ["Animation", "Adventure", "Family"]
    }]

    ReplyDelete
  60. Great Article. What if we need to extract only one attribute and its value? Advice soon.

    ReplyDelete
  61. Perfect tutorial. Thankyou very much :)

    ReplyDelete
  62. Thank you, nice tutorial,its working perfectly.

    I am basically Java developer and in one of my project I need to create php web services. by following your post I made it within no time.

    ReplyDelete
  63. Hi Mr. Pandy I've tried your code but it does't display anything on my side when i tried to run the code it shows no display on my browser, i am pulling multiple data/rows from my table what do you think is suppose to be the issue?

    ReplyDelete
  64. Hi Mr. Pandy, Ive tried your code and apply it on the script that i am doing but upon running the code it doesnt display anything on my browser. What do you think is suppose to be the problem? The table i am selecting has multiple data /rows.

    ReplyDelete
  65. Do you have this exact same EXAMPLE in java?

    ReplyDelete
  66. Your example is for one database table.it's nice but when i want to multiple table data in one page then what is the processing ?

    ReplyDelete
    Replies
    1. Then use sql joins to retrieve data from multiple tables. Only the query differs. Looks like this,

      $sql = "select table_a.*, table_b.* from table_a inner join table_b on table_a.common_field = table_b.common_field";

      Rest others are same.
      Cheers.

      Delete
  67. thank you fro sharing it worked fine with me
    kind regards :)

    ReplyDelete
  68. Hi, does the data type of the value from mysql when converting into Json retain its data type?.

    this is the Array of the fetch data.

    Array("number_double" => 123.123)

    and this is it's Json encode

    { "number_double" : "123.123" }

    now what i want is the value of number_double is double and not as string like this.

    { "number_double" : 123.123 }

    how can i achieved this?..

    sorry for the noob question.

    ReplyDelete
  69. Hi, does the data type of the value from mysql when converting into Json retain its data type?.

    this is the Array of the fetch data.

    Array("number_double" => 123.123)

    and this is it's Json encode

    { "number_double" : "123.123" }

    now what i want is the value of number_double is double and not as string like this.

    { "number_double" : 123.123 }

    how can i achieved this?..

    sorry for the noob question.

    ReplyDelete
    Replies
    1. Sry for delayed response... JSON is just string, but while processing the data you can use doubleval() function to convert string to double.

      Delete