How to retrieve data from database in JSON format using PHP?

PHP has some default fuctions which can easily handle any JSON string. json_encode() function alters any PHP objects into JSON. Accordingly json_decode() function alters any JSON into PHP array object.

Step 1

Display JSON Data(index.php)

Code:

<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 -->

Step 2

Ajax call (get and display JSON Data(index.php))

Code:

$(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!");
            }
        }
      });
    }   
   });
});

Step 3

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

Code:

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

?>

Read more