Using jQuery AJAX and PHP how to fetch data from a MySQL database table as JSON format. The PHP script retrieves the MySQL tables records and send to client side jQuery Script as JSON string format. This tutorial also explains how to retrieve data from MySQL database in JSON format using PHP, AJAX & jQuery and how to convert MySQL records or rows in JSON format using PHP and jQuery.
In the first program– I have shown how to send JSON encoded associative array from PHP as JSON string format to client side jQuery / JavaScript code in client browser using AJAX. In second program I have sent serialized form data to server side PHP by ajax and then retrieved MySQL table records as array and send to client side jQuery / JavaScript code as JSON string to show in client screen using AJAX. In third program – I have retrieved multiple records from MySQL table by PHP and sent the whole table structure to client side JavaScript / jQuery code using AJAX as 2 dimensional array.
You can use the JSON data format to send data to the client (browser), not only simple data , you can retrieve a record / row or lots of records from database and you can send the whole table structure to the client browser from the server by AJAX technology in which silently data transfer occurs from Server side code (server.php) in the following example) to client JavaScript code. Keep in mind server side (PHP) code can access database and can perform any kind of complex computation and send back to the client.
You can read the article or view the YouTube video below-
I am not using the core JavaScript (JS) codes here, rather using the jQuery instead because this JS library is much easier to update and handle HTML DOM (Document Object Model & hierarchy of HTML5 elements) and behind the scenes jQuery uses the core JavaScript. You may require some jQuery knowledge to understand AJAX calls used here but they are pretty simple.
This may seem complex to you, but if you want to retrieve data from the database via a PHP script (or simply get data from the server, PHP here) and want to show the database information in your browser without refreshing the page, then you should require JSON data and AJAX with PHP you need to show data from database. Not only data from a database, actually any data coming from server script can be shown by AJAX without refreshing the page.
A) PHP – jQuery – Ajax example to transfer JSON data
Project A : If you click on button of the client browser, data comes from server.php sent as a JSON string to the client (user browser). The JSON string converted in client window as JavaScript object and shown in the user window.
The client side code shown in the following index.php file-
index.php (Client , calls by AJAX to PHP server side script “server.php”)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Title of the document</title>
<script type="text/javascript" src="jquery.js"></script>
</head>
<body>
<p>The Ajax Response will be shown here of the document......</p>
<button class="btn">Click Me</button>
<div class="response"></div>
</body>
<script type="text/javascript">
$(document).ready(function(){
msg = $("div.response");
$("button").on("click", function() {
$.ajax({
url : 'server.php',
type: "GET",
dataType: 'json',
success:function(response) {
var op = "";
for(var prop in response){
op += " ( " + prop + " = " + response[prop] + " ) \r\n" ;
}
msg.html(op);
}, // success
error:function(xhr,textStatus,errorThrown){
var str = "ERROR : SERVER error<br>" + xhr +
"<br>" + textStatus + "<BR>" + errorThrown;
msg.html(str);
} // error callback function block
}); // ajax call ends
});// $("button").on("click") ends
}); //$(document).ready ends
</script>
</html>
The following server PHP script shown below. It generates an associative array ( $arr ) and returns it to the client (browser). The browser’s JS engine has its own JSON parser to parse JSON. JSON format is nothing but a string formatted well for JSON parser. The json_encode () funtion converts the array to a JSON string.
server.php
<?php
$arr = array('index1' => 1,
'index2' => 2,
'index3' => 3,
'index4' => 4.5,
'index5' => 'five');
echo json_encode($arr);
?>
How to run the above program ? Assume all in test folder in your localhost installation.. go to the following URL of your browser:
http://localhost/test/index.php
You will see a button. Just click the button. Then the following text will be shown
The Ajax Response will be shown here of the document...... ( index1 = 1 ) ( index2 = 2 ) ( index3 = 3 ) ( index4 = 4.5 ) ( index5 = five )
So how all these happens: Explanation of the above codes
In client / browser (in index.php) you will see a button and some text. When you click the button the following text to be shown :
index1 = 1, index2 = 2 , index3 = 3, …
inside <div class=”response”></div>
So how JSON data interchange takes place between “server side PHP code” & “client side JavaScript jQuery code”, explained by example
The CLIENT jQuery code calls the server.php file :
- the client JavaScript(JS) code (here jQuery ) inside index.php page will call the server script PHP script ( server.php ) via AJAX call when the user clicks the button.
- The button’s click event fires the AJAX call to server. Here the client does not send any data to server.php, rather get data from server.php through AJAX call via GET method.
How the PHP server send JSON response to JS/ jQuery script by AJAX
- On button click the JavaScript(JS) / JQuery calls the server side script (server.php), via a GET request is made to run server.php shown above, then the server script generates a JSON string by the json_encode() method from the associative array ( $arr ) inside server.php script, which converts array $arr to a string in JSON format and the string is printed by echo statement (in server.php). When any string is printed via echo statement, that string is picked up by AJAX and sent to the client browser’s JS (jQuery) code, here in the index.php file, the jQuery code shows the JSON response in the screen as response from server (server.php).
- The client JavaScript (JS) / jQuery codes ( in index.php ) when receives the the JSON string ( sent by server.php ), it parses the JSON string into a JavaScript object “response” (you can change it to any other name) in the client browser by the JavaScript / JSON parser in browser. So the JSON string sent by PHP server side script (server.php), received by client browser’s JavaScript or jQuery code (in index.php) and converted into a JavaScript object as name / value pairs in the client browser.
- Each of the response object properties are parsed via the following lines in index.php –
for(var prop in response){
op += " ( " + prop + " = " + response[prop] + " ) \r\n" ;
}
The response is the JS object having properties “index1” , “index2”,…, “index5”
So response[“index1”] = 1, response[“index2”] = 2,
The above code is a part of the jQuery code of index.php and response is the generated JavaScript object from JSON string has property values (prop in the above code) like index1, index2, index3 …. If you know about JavaScript objects you should know this. This is the basic JavaScript object concept and by for in loop and the properties of response object shown in the client browser screen.
B) HTML from submission triggers jQuery AJAX call to server side PHP code to fetch MySQL database record
About the project B: In the following program I shall fetch a record from a database table via a server side PHP script which retrieves a record of a particular “username”. The username should be taken from the client browser via a HTML form. The user should type the username and click the submit button and database resord associated with that username will be retrieved by PHP and sent to client side JS code, which will show the user data as a good looking HTML table. So this is the whole thing I am going to do.
In this problem an AJAX call is made to a PHP script by the client javascript / jQuery code when user submit a form.
Believe it or not , you can retrieve lots of records of employees/ students / products or anything from the database where each record represents a particular employee / person or/ product and you can send the whole data from the server side PHP script to the client via a JSON string. The JSON data is converted to a JSON object in client browser.
The database table columns in bold and data in normal font-
username faculty role bikram Arts user Admin All admin
The following code is a part of client side HTML :
The HTML form shown below - <form> Type User Name : <input type="text" id="username" name="username" value=""> <button type="submit" class="btn">fetch data</button> </form>
The server side PHP Script server2.php shown below-
In the following PHP script, username is obtained from the user form ( typed by user ) by the POST HTTP method. The database is connected via MySQLi function and the record of the username (entered in the form) is retrieved from the database.
PHP code : server2.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "personnel";
// Create connection
$conn = new mysqli($servername, $username, $password,
$dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$user = $_POST["username"];
$databaseTableName = "users";
$sql = "SELECT username, faculty, role FROM
$databaseTableName WHERE username = '$user' ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while( $row = $result->fetch_assoc()) {
break;
// A single record picked up from database so quit loop
}
}
$conn->close();
echo json_encode($row);
?>
Record(s) from database is retrieved by a while loop in the above PHP – MySQL script which refers to a tuple or record in a database table. In this program the loop runs only one (the while loop) because we have only one record for the particular username, so inside while loop I have written a break statement. Because after retrieving the particular record of the “username” the loop quits.
And then the username, faculty, role of the particular username is kept in an associative array called $row, and I have JSON encoded the associative array $row and printed by the echo statement which is retrieved by AJAX (JavaScript code in client browser window) as the server response and shown in the client browser window as a good looking table format.
Here the database fields acts as the keys of the associative array (in PHP script server2.php) retrieved by
$row = $result->fetch_assoc()
and also these keys are the object properties in the client JavaScript code. In the PHP code the associative array keys are username, role & faculty.
The fields used in the SQL query variable ( $sql ) of PHP script in server2.php, so they are also the keys of JavaScript Object generated in the client, because the associative array is returned from PHP code to JavaScript code via Ajax call.
The JavaScript / jQuery code resides in the following file:
client2.php
code here
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Title of the document</title>
<script type="text/javascript" src="jquery.js"></script>
<style type="text/css">
table, tr, td {
border: 1px solid black;
padding: 10px
}
</style>
</head>
<body>
<p>Type the User name here to fetch user name, role, faculty information retrieved from database by Ajax......</p>
<div class="">
<form>
Type User Name : <input type="text" id="username" name="username" value=""> <button type="submit" class="btn"> fetch data from server </button>
</form>
</div>
<div class="response"></div>
<script type="text/javascript">
$(document).ready(function(){
msg = $("div.response");
form = $("form");
form.submit( function(event) {
event.preventDefault();
$.ajax({
url : 'server2.php',
type: "POST",
dataType: 'json',
data: form.serialize(),
success:function(response)
{
var op = "<table><tr><td width='200px'>User Name</td>" +
"<td width='200px'>Faculty</td>" +
"<td width='200px'>Role</td></tr><tr>";
for(var prop in response){
op += "<td>" + response[prop] + "</td>" ;
}
op += "</tr></table>";
msg.html(op);
}, // success
error:function(xhr,textStatus,errorThrown){
var str = "ERROR : SERVER error<br>" + xhr +
"<br>" + textStatus + "<BR>" + errorThrown;
msg.html(str);
} // error callback function block
}); // ajax call ends
}); // form.submit() ends
});// $(document).ready() ends
</script>
</body>
</html>
But the previous program has a drawback.
If you enter any arbitrary username then the program crashes because in the program there is no check for invalid username. Only if the user name is valid then it retrieves the data from the database and send the whole information of an user to the client.
This is done by this line :
if ( $result->num_rows > 0)
But if the username entered does not exist in database table, what happens then?
Answer : Then the above program will crash and will show arbitrary data [some object object error] because the JSON string is not properly formatted.
So in the following program I am checking whether the data retrieval is success / or a failure.
C) Server side script send “2 dimensional associative array” as JSON string response to client jQuery-JavaScript by AJAX
This program is almost same as the previous program in the user point of view, but in coding there is a huge change. Error checking has been imposed if this example code is compared with previous example (B). Here also the user has to type the username in the form. And form when submitted will trigger the server side PHP script to fetch data from the database if found, if not found an error will be shown. All will be done by AJAX methodology without page refresh.
Error Checking imposed here : If no records are found in MySQL table error will be shown using a response object in JavaScript / jQuery code. Now the server side PHP code given below , its little changed from the code mentioned above program (B).
Here a new 2-Dimensional array introduced as $output in the server side PHP script as shown below.
server3.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "personnel";
$output = array( 'success' => false, 'error' => null,
'resp' => array() );
// Create connection
$conn = new mysqli($servername, $username,
$password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$user = $_POST["username"];
$databaseTableName = "personnelusers";
$sql = "SELECT username, faculty, role
FROM $databaseTableName WHERE username = '$user' ";
$result = $conn->query($sql);
if ( $result->num_rows > 0)
{
$output["success"] = true;
// output data of each row
while( $row = $result->fetch_assoc()) {
$output["resp"] = $row;
break;
}
}
else {
$output["error"] = "No rows selected";
}
$conn->close();
echo json_encode($output);
?>
A two dimensional array has been introduced called $output , it is basically a 2D associative array and the first key “success” indicates first array element $output[“success”] which has default value false.
Second associative array key is “error”, so that second array element $output[“error”] shall hold any error string, by default it is null, if any error occurs that will be assigned here.
The main output of this server side PHP script server3.php , is the data retrieved from the database ( with reference to the submitted username in the HTML form) will be assigned to the array element $output[“resp”] of 2D array $output , which will be assigned to the the response JS object when $output will be sent as JSON encoded string, to the client side script (JS or jQuery).
This $output[“resp”] is a single dimensional associative array when the database retrieval is a success w.r.t the username.
So if number of rows retrieved greater than 0, then the $output[“success”] element have true value and the $output[“resp”] have the $row from database table.
$output[“success”] = true;
$output[“resp”] = $row;
If no records found in database table, $output[“success”] = false, and the other associative array element $output[“error”] = “no row selected” .
At the end of script the 2D array $output after being JSON encoded sent to the client Browser via echo statement and AJAX.
client3.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Title of the document</title>
<script type="text/javascript" src="jquery.js"></script>
<style type="text/css">
table, tr, td {
border: 1px solid black;
padding: 10px
}
</style>
</head>
<body>
<p>Type the User name here to fetch user name, role, faculty information retrieved from database by Ajax......</p>
<div class="">
<form>
Type User Name : <input type="text" id="username" name="username" value="">
<button type="submit" class="btn"> fetch data from server </button>
</form>
</div>
<div class="response"></div>
<script type="text/javascript">
$(document).ready(function(){
msg = $("div.response");
form = $("form");
form.submit(function(event) {
event.preventDefault();
$.ajax({
url : 'server2.php',
type: "POST",
dataType: 'json',
data: form.serialize(),
success:function(response)
{
if(response.success == true )
{
var op = "<table><tr> " +
"<td width='200px'>User Name</td>" +
"<td width='200px'>Faculty</td>" +
"<td width='200px'>Role</td></tr><tr>";
for(var prop in response.resp){
op += "<td>" + response.resp[prop] +
"</td>" ;
}
op += "</tr></table>";
msg.html(op);
}
if(response.success == false )
{
msg.html(response.error);
}
}, // success
error:function(xhr,textStatus,errorThrown){
var str = "ERROR : SERVER error<br>" +
xhr + "<br>" +
textStatus + "<BR>" + errorThrown;
msg.html(str);
} // error callback function block
}); // ajax call ends
});// form.submit() ends
});// $(document).ready(function() ends
</script>
</body>
</html>
In the client side code response is the parsed (from JSON string) to JS object having property success , error, resp. These properties can be accessed via response.success, response.error, response.resp .
response.resp holds also some properties as database field names username, faculty, role ( They can be accessed as response.resp.username , response.resp.faculty, response.resp.role in JavaScript code ).
Now if you enter an arbitrary username and click button, then if response.success == false and error string will be shown. The error string response.error will not be null then.