jump to navigation

How To Generate JSON With PHP from Mysql and Parse It With JQuery November 4, 2011

Posted by Tournas Dimitrios in JQuery.
trackback

JSON (JavaScript Object Notation) is a lightweight data-interchange format , it’s capable of setting up   data constructs for integers , strings , arrays ,  booleans  and obviously objects (collection of key:value pairs , comma-separated and enclosed in curly brackets)  . It is easy for humans to read and write also it is easy for machines to parse and generate .  An example of where this is used is web services responses . In the ‘old’ days, web services used XML as their primary data format for transmitting back data , but since JSON appeared (The JSON format is specified in RFC 4627 by Douglas Crockford), it has been the preferred format because it is much more lightweight .   Not only most programming languages have build-in functionality to generate and parse json data formats but also most frameworks / SDK’s  like Zend-Framework , JQuery , Adobe’s Flex SDK  and more …..  . You can find a lot more info on one of Crockford’s sites here .

 In this article we will generate JSON with PHP on the server (by queying a Mysql database ) and parse it with JQuery on the client (browser) . I’ll skip the basics and go straight to the point where we have an array of all the content from an database query . As of PHP 5.2.0 , the JSON extension is bundled and compiled into PHP by default , which makes life for us extremely easy. This is also a highly optimized implementation of JSON for PHP, so if you have this available to you , then use it . But all is not lost if you don’t have this setup , there are plenty of libraries you can use to take care of this task . For our examples we are using PHP 5.2 to query  a MySQL database as the data source   .


$link = mysql_pconnect("localhost", "root", "") or die("Could not connect");
mysql_select_db("test") or die("Could not select database");

$arr = array();

$rs = mysql_query("SELECT * FROM people");

while($obj = mysql_fetch_object($rs)) {
$arr[] = $obj;
}
echo '{"members":'.json_encode($arr).'}';
/*
//The json object is :
{"members":[{"id":"1","title":"Mr","firstname":"Peter","surname":"Ventouris"},{"id":"2","title":"Mr","firstname":"David","surname":"Dabel"},{"id":"3","title":"Mr","firstname":"John","surname":"Merkel"},{"id":"4","title":"Mr","firstname":"James","surname":"Eltons"}]}
*/

Now let’s create the browser logic :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery PHP Json Response</title>
<style type="text/css">
div
{
text-align:center;
padding:10px;
}

#msg {
width: 500px;
margin: 0px auto;
}
.members {
width: 500px ;
background-color: beige;
}
</style>
</head>
<body>
<div id="msg"> </div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js">
</script>
<script type="text/javascript">

$(document).ready(function(){
var url="http://localhost/pfbc/json.php";
$.getJSON(url,function(json){
// loop through the members here
$.each(json.members,function(i,dat){
$("#msg").append(
'<div class="members">'+
'<h1>'+dat.id+'</h1>'+
'<p>Firstname : <em>'+dat.firstname+'</em>'+
'<p>SurName : <em>'+dat.surname+'</em></p>'+
'<p>Title : <strong>'+dat.title+'</strong></p>'+
'<hr>'+
'</div>'
);
});
});
});

</script>
</body>
</html>

The first function , getJSON , needs a URL (the location of json.php)  , it will also need a callback function whose parameter is the data returned from the url . I’ve named this parameter accordingly but you can change the name if you prefer. Our $.each function will be put inside getJSON’s callback and it will loop through and add tags to the results and append them to the div content . Just like getJSON , $.each has two parameters , first you have to give it the data then the callback function which is used to parse the data . The letter “i” in its callback function keeps count of the results while the post variable represents the current results . Don’t forget to embed the JQuery library .

Let’s improve the appearance of the data , the result will be displayed into an table .

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery PHP Json Response</title>
<style type="text/css">
div
{
text-align:center;
padding:10px;
}

#msg {
width: 500px;
margin: 0px auto;
}
.members {
width: 500px ;
background-color: beige;
}
</style>
</head>
<body>
<div id="msg">
<table id="userdata" border="1">
<thead>
<th>Id</th>
<th>First Name</th>
<th>Surname</th>
<th>Title</th> 
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js">
</script>
<script type="text/javascript">

$(document).ready(function(){
var url="http://localhost/pfbc/json.php";
$("#userdata tbody").html("");
$.getJSON(url,function(data){
$.each(data.members, function(i,user){
var tblRow =
"<tr>"
+"<td>"+user.id+"</td>"
+"<td>"+user.firstname+"</td>"
+"<td>"+user.surname+"</td>"
+"<td>"+user.title+"</td>" 
+"</tr>" ;
$(tblRow).appendTo("#userdata tbody");
});
});
});

</script>
</body>
</html>
Advertisements

Comments»

1. Pitso Phera - November 25, 2012

Thanks 🙂

2. Keyur Shah - February 9, 2013

Thanks….

3. oscar - March 4, 2013

thank you so much

4. oscar - March 4, 2013

hi, i have a question, i am begginer. do you explain me please, how work the variable (‘i’) in function (i , user). Thankyou.

tournasdimitrios1 - March 5, 2013

@oscar
Hi ,
Welcome and thanks for commenting on this blog .
I never forget that JQuery is just a wrapper around JS . Anonymous callback functions are used extensively in Javascript , for better readability . JQuery’s each method is just a helper method , which then creates a “for-loop” statement behind the scenes .Each “for-loop” returns an Object . In the context of our script , the callback function is “looping” through the values of each Object’s content . The “i” is just a pointer , it marks the position of the current loop . It dictates when the callback functions should stop looping (as it has reached the end of Object’s content ) .
Just for completeness , Closures and anonymous functions are also used in PHP , especially from PHP >= 5.3 . Probably with a slightly different functionality , the basic concepts remain the same though . Soon , I’ll try to write an article related to PHP Closures . So stay tuned if You are interested .

5. oscar - March 5, 2013

+5 thank you so much.

6. Dajz - June 9, 2013

Hi, I tried this example and it worked great. But I also tried to change this to only fetch 1 specific row from the database based on a querystring. So basically I changed the PHP to be able to $_GET a querystring and changed the sql to only fetch that row.

But when I do that. Nothing happens in the jquery-part. If i remove the GET and just write “select * from db where id = 2”; then it works. But not while using GET. Do you have any clue as to why?

Thanks in advance.

tournasdimitrios1 - June 9, 2013

@Dajz
Have you properly encoded the string on “client-side” before the request is made to the server ? (just an example) . It would be nice to see a code-sample (on pastebin or whatever)

7. Shalashaska - August 14, 2013

You’re my personal God. Thanks a lot !!!

8. Phonegap + JQueryMobile. Obtener JSon desde PHP. | Ensayo y Error - February 3, 2014
9. tournasdimitrios1 - May 13, 2014

@Enrique
Any error ?

10. amzad - June 8, 2014

Your json example is very nice.

Thanks,
Amzad

tournasdimitrios1 - June 8, 2014

@amzad
You are welcome 🙂

11. youe - December 1, 2014

Thanks for a really good article. I am quite new to php and javascript and for some reason the code do not work for me. When you see the page there is nothing, only the html. Like the json encoded array is not really passed on to the javascript site.
Is it because I use it on local server like xampp??

Youe

tournasdimitrios1 - December 1, 2014

@youe
Hello there , and welcome to my Blog 🙂
The script is tested on local and remote web-server and should be working fine.
To check your web-server (xampp) response, just visit with your browser “json.php”. You should have a clear json-encoded response. The following screenshot is taken from my Linux Box (CentOs 6.4).

screenshot
Open also your browser’s web-developer toolbar (F12 for Firefox) and go to : network-tab -> response-tab to debug the response .
Let me know 🙂

12. youe - December 2, 2014

Thank you very much for your reply. I have tried to write members on the console in the web-developer, but it says uncaugt reference error members not defined. I have also put the php file and javascript together in one page and not separate page. I believe it would not make any difference. When you write into the browser URL localhost…and the json.php it does shows the array clearly like in your screenshot, but the javascript part seems not to define the array.
(I use google chrome)
In advance thank you

tournasdimitrios1 - December 2, 2014

@youe
It’s not a big deal to debug the code via your browsers console tab. I dit a zip file with all files included, just download from my dropbox account.
1) unzip
2) move folder to your local server’s public directory
3) Open people.php file and edit credentials for database (username, password ….)
4) Open people.html file and edit line 29 to match your local adress .
On my Linux machine its url=”http://local.designpatterns/people.php”; Because I have set up a Virtural host .
Hope that helps 🙂

13. youe - December 3, 2014

When I saved the file in htm it worked fine, just the developers toolbar could still not define the “members” in the console.
But a step in the right direction. Thank you.
Youe

14. srikanth ganta - February 27, 2015

Reblogged this on Srikanth's Blog.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s