How to get data from database in JSON format using Ajax and PHP

  •  
  •  
  •  
  •  
  •  
  •  

A very common use of JSON is to read data from a web server and then display these data into a web page. JSON is text, so when we want to exchange text as data between a browser and web server we can use JSON. In this tutorial, I will teach you how to get data from database in JSON format using Ajax and PHP. PHP has some default functions which can easily handle any JSON string. json_encode() function converts any PHP objects into JSON. Similarly, json_decode() function converts any JSON into PHP array object.

So, for better understand, follow the following example, it will help you to know how to get data from database in JSON format using Ajax and PHP. In this example, we will insert a roll number in the input box, it returns corresponding student details from the MySQL database in JSON format and displays in the web page.

Also, you can read — How to insert JSON data into MySQL database using PHP.

How to get data from database in JSON format using Ajax and PHP

HTML – Display JSON Data (index.php)

<div class="container-fluid">
        <div class="row">
            <div class="col-xs-12 col-md-4"></div>
            <div class="col-xs-12 col-md-4 text-center">
                <div class="form-group">
                    <label>Roll number : </label>
                    <input type="text" id="roll" class="text-center" name="roll" value="" size="3">
                    <button class="btn btn-success" id="btnOk">Get Student Details</button>
                </div>
            </div><!-- .col -->
            <div class="col-xs-12 col-md-4"></div>
        </div><!-- .row -->

        <div class="row">
            <div class="col-xs-12 col-md-4"></div>
            <div class="col-xs-12 col-md-4">
                <table class="table">
                    <tbody>
                        <tr><th>Name</th><td id="name"></td></tr>
                        <tr><th>Class</th><td id="class"></td></tr>
                        <tr><th>Section</th><td id="section"></td></tr>
                        <tr><th>Roll</th><td id="roll_no"></td></tr>
                    </tbody>
                </table>
                <h6 class="msg text-danger text-center"></h6>
            </div><!-- .col -->
            <div class="col-xs-12 col-md-4"></div>
        </div><!-- .row -->
    </div><!-- .container-fluid -->

Very simple bootstrap HTML code. One input box for roll number and a button for start the Ajax call. And a table for display the student details.

Ajax Call – Get & Display JSON Data (index.php)

$(document).ready(function() {
  $('#btnOk').click(function() {
    var roll = $("#roll").val();

    if(roll != '') {
      $.ajax({
        url: 'get-data.php',
        type: 'post',
        dataType: "json",
        data: {roll:roll},
        success: function(data) {
            if(data.error == '') {
               $(".msg").html("");
               $("table").show();
               $("#name").html(data.student.f_name+ " "+data.student.l_name);
               $("#class").html(data.student.class);
               $("#section").html(data.student.section);
               $("#roll_no").html(data.student.roll);
            } else {
               $("table").hide();
               $(".msg").html("No record found!");
            }
        }
      });
    }   
   });
});

The above jQuery and Ajax code send roll number to the get-data.php file and display the returned data in the web page. Within the $.ajax() function json is assigned as dataType, so the returned data from the get-data.php should be in JSON format, otherwise, it will generate an error.

PHP – Generate JSON Data From Database Using PHP (index.php)

<?php include('db.php'); ?>

<?php
  if(isset($_POST['roll']) && $_POST['roll'] != '') {
    $roll = mysqli_real_escape_string($con, $_POST['roll']);
    $qry = "select * from students where id='".$roll."'";
    $res = mysqli_query($con, $qry);
    if(mysqli_num_rows($res) == 1) {
      $row = mysqli_fetch_assoc($res);
      $data['student'] = $row;
      $data['error'] = '';
    } else {
      $data['error'] = 'not_found';
    }
    echo json_encode($data);
  }
  
?>

The above PHP code fetches data from the database table based on the roll number and converts these data into JSON.

Database Configuration (db.php)

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','DATABASE-USERNAME');
define('DB_PASSWORD','DATABASE-PASSWORD');
define('DB_NAME', 'DATABASE-NAME');

$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
// Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

Change the database name, username, and password with your database credentials.

Database Table

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `f_name` varchar(255) NOT NULL,
  `l_name` varchar(255) NOT NULL,
  `class` varchar(10) NOT NULL,
  `section` varchar(3) NOT NULL,
  `roll` varchar(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `students` (`id`, `f_name`, `l_name`, `class`, `section`, `roll`) VALUES
(1, 'Mitrajit', 'Samanta', 'XII', 'A', '1'),
(2, 'Ketaki', 'Das', 'XI', 'B', '3'),
(3, 'Aritra', 'Samanta', 'XII', 'A', '5'),
(4, 'Akash', 'Roy', 'XI', 'C', '3'),
(5, 'Misti', 'Nandi', 'I', 'A', '1');

Create a student table in the database and insert the above sample data into it.

Complete Source Code – How To Get Data From Database In JSON Format Using Ajax And PHP

Download the complete source code from the below Download link. Also, you can try the live demo on how to get data from database in JSON format using Ajax and PHP from the below Demo link. Please like and share this tutorial with others.

About Mitrajit

4 comments on “How to get data from database in JSON format using Ajax and PHP

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove you are a human *