How to Convert Data from MySQL to JSON using PHP

On 1/12/2015

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.

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);
?>

Recommended Read: Use PHP json_decode Function to Insert JSON to MySQL Database

Recommended Read: How to use PHP PREG MATCH function to validate Form Input

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

Last Modified: May-9-2016

97 comments:

  1. Great Post, Actually PHP is a beautiful source for developing a database driven web application, I love this post, thanks for spending your time for discussing about this topic.
    Regards,
    PHP Training in Chennai

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

      Cheers

      Delete
    2. 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/

      Delete
    3. Hi! Fixed the sql commands. Please try again... it works now!!!

      Delete
  2. thanks ... just the exact solution i was looking for :)

    ReplyDelete
  3. 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
  4. 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
  5. $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
  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');$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
  7. 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
  8. Your code is not working in heavy data

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

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

    ReplyDelete
  10. 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
  11. 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
  12. 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
  13. Very nice post! It is exactly what I need.
    Just a question: how can I hide ("hostname","username","password","db_employee")?

    Thanks!

    ReplyDelete
  14. 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
  15. thank you, this is simple and very helpfull

    ReplyDelete
  16. 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
  17. 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
  18. "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
  19. 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
  20. 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
  21. Many thanks. Really helpful having this all in one place (i.e. the connection setup with the query).

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

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

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

    ReplyDelete
  25. 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
  26. 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
  27. 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
  28. Thank you, nice tutorial,its working perfectly

    ReplyDelete
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 6 hours and over 8 different recommendations, this was the only one that actually worked for us on 5.3.28 .

    Thank you!

    ReplyDelete
  35. 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
  36. sorry i deleted some in order to make it postable

    ReplyDelete
  37. 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
  38. 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
  39. 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
  40. 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
  41. Just what i have been looking for. Yet to try it though. Thank you very much for the code :)

    ReplyDelete
  42. Wont work with large 1000+ databases

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

    ReplyDelete
  44. 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
  45. love thxxx sir solved my big problem :D

    ReplyDelete
  46. 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
  47. 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
  48. how to get the data if its clickable?

    ReplyDelete

Contact Form

Name

Email *

Message *