Create JSON From MySQL Using PHP and Parse it with jQuery

JSON(Java Script object notion), I think every web dev already know about JSON and the advantages of using it. Some common questions that every new developer asks are: why to use JSON? Where to use JSON?  How to use JSON in web applications? I came across a very good article on JSON vs XML. After reading this article you will have some strong points in your bucket to answer the question “Why to use JSON”. I am preparing this tutorial for beginners in JSON.  In this tutorial I will be demonstrating how you can create JSON string from MySQL using PHP and then parse it with jQuery to display it in client side (Browser).

So for this tutorial we have a table named ‘users’ schema of class table is described in picture given below.

users table infotuts

   LIVE DEMO   DOWNLOAD

Now all we have to do is fetch rows from this table with PHP and convert it in JSON string. It’s really easy to do because PHP empowers us with its inbuilt function ‘Json_encode’. ‘json_encode’ returns the JSON representation of a value.

Here is our code for getjson.php:

</p>
<?php
$mysql_db_hostname = "localhost";
$mysql_db_user = "root";
$mysql_db_password = "12345";
$mysql_db_database = "school";

&nbsp;

$con = @mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password,
 $mysql_db_database);

if (!$con) {
 trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
}
$var = array();
 $sql = "SELECT * FROM users";
$result = mysqli_query($con, $sql);

while($obj = mysqli_fetch_object($result)) {
$var[] = $obj;
}
echo '{"users":'.json_encode($var).'}';
?>
<p style="text-align: justify;">

Now when you have JSON data, you can parse it with jQuery and display it in browser.  The good thing about this is you have all the control over data so you can display it the way you want. We will display all data in a table.

Code for ShowJson.html:

</p>
<table class="mGrid" id="jsondata">
<thead>
<th>Id</th>
<th>Name</th>
<th>Age</th>
<th>Gender</th>
<th>Location</th>
</thead>
<tbody></tbody>
</table>
</div>

<script type="text/javascript">

$(document).ready(function(){
var url="getjson.php";
$("#jsondata tbody").html("");
$.getJSON(url,function(data){
$.each(data.users, function(i,user){
var newRow =
"<tr>"
+"<td>"+user.id+"</td>"
+"<td>"+user.name+"</td>"
+"<td>"+user.age+"</td>"
+"<td>"+user.gender+"</td>"
+"<td>"+user.location+"</td>"
+"</tr>" ;
$(newRow).appendTo("#jsondata tbody");
});
});
});

</script>
<p style="text-align: justify;">

I hope this simple tutorial will help you understand how to get started with JSON. I will post some more article and practical uses of JSON in web services in my next posts. Share it with your friends and followers.

11 thoughts on “Create JSON From MySQL Using PHP and Parse it with jQuery”

  1. Hey

    Good article. I have read many of your articles and have learned a lot from you. I actually use JSON strings with AJAX calls to a PHP script when doing page updates.
    However.. in this case and I realizr this is just a demo but why would you convert the recordset to JSON and have the client browser render the table and data when you are already using php and could have the table and data rendered on the server. Wouldn’t that be faster?

    Thanks and take care
    Wayne

    1. I would think the best reason to have the browser render the data is so that you don’t have to reload the page, which comes in handy if you have other javascript running on the same page. I can’t image it would that there would be any noticeably difference in time between the two methods.

  2. Hi Wayne,
    Its good to know that InfoTuts is helping you :)Your question is really good. Yes it was just a demo to show how to use JSON. Yes if I’d done all the rendering on server end then it would have been faster.
    JSON gives us a cross domain scripting power, its mainly used to get web services response. more clearly response from a different domain.

    1. Sup,
      Rich tut explained simply. It would be good if another TUT/DEMO comes out that is using JSON ,Restfull Web-Service invocation using JSON ,any middleware and MySQL backend. I have been working with Restfull Web-Service consumed by a J2EE Application and backend ORACLE/VERTICA and that is why I want to see a running application using PHP,JSON,Web-Service.

      Cheers!

  3. Sjakelien Vleeschbaardt

    Somehow I have a lot of trouble to make this work.
    I started off running the code, that I copied using the ‘view source’ buttons next to the code samples above, in my local MAMP installation,
    It took me a while to notice the difference between “mysqli” and “mysql”. Once I put the code on a platform that had the mysqli extensions activated, at least the “getjson.php” page worked fine, returning some son-formatted stuff.
    However, the ShowJson.html page keeps on showing a white screen. Is there something extra I should know? As far as I understand, javascript is executed client side, so I wouldn’t expect the need of extra extensions.

    What would be the best approach for debugging here?

    Thanks for your advise..

  4. Correction to my previous post that is being moderated at the moment: I don’t get a white screen when opening ShowJson.html, I only get the Table Headers, but no rows.

  5. Sjakelien Vleeschbaardt

    OK, I solved it, and I guess it is obvious to those who are experienced. But I’m not, well now I am, a bit, so this might help others:
    In the source code of ShowJson.html, you should include a reference to the query library, like so:

    Then, in the getjson.php source, you really want to take out the first and the last line, since it invalidates the json response.

    Anyway, while debugging, I came to understand a lot more than if it would have worked immediately…..

  6. Hi, I’ve tested your solution and it does the job – but if I could ask you how I could get the nordic characters encoded correct as well? The db has latin1_swedish_ci encoding so it might be json then (?) When my table is rendered it shows up with ‘null’ when ever a nordic character is used 🙂

    Thanks

  7. Doesnt seem to work for me, the .php shows data from the page, but the show html doesnt show, tried on different sites still with no data from rows showing up 🙁

Comments are closed.