How can I extract JSON data from PHP and display it in a datatable?

Question

I want to get reports from my database by using PHP via turning it into JSON format and displaying it with AJAX JQuery. But I can't seem to pass the data correctly. Can somebody please tell me my fault?

Here is my code view:

$(document).ready(function() {

  $("#searchOverall").click(function() {


    var ay = $("#Overall_acadyear").val();
    var year = $("#Overall_year").val();

    if (ay === undefined || ay == '') {

      alert("Select Academic year.");

    } else if (year === undefined || year == '') {

      alert("Select year level.");

    } else {


      $.ajax({

        url: "js/overallreport.php",
        dataType: "json",
        data: "ay=" + ay + "&year=" + year,
        success: function(data) {
                 //left this blank because I am not sure of what I am doing.
                 //I used $.getJSON and $.each
        },

        error: function() {
          alert('Cannot retrieve data from server.');
        }

      }); //ajax




    } //else

  }); //btnOverall



});
//This is the JS FILE

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<div class="form-group col-sm-4">
  <select class="form-control" id="Overall_acadyear">
    <option value='' active>Select AY</option>
    <?php $r->selectAcademicYear(); ?>
  </select>

  <select class="form-control col-sm-4" id="Overall_year" align="right">
    <option value='' active>Select year level</option>
    <?php $r->selectYear(); ?>
  </select>
  <div class="form-group">
    <button type="button" class="control-label btn-success" id="searchOverall" name='createAccount'>Search</button>
    <button type="reset" class="control-label btn-danger" id="reset">Clear Fields</button>
  </div>

  </th>

  </tr>
  <tr>
    <th>Student Number</th>
    <th>Student Name</th>
    <th>Section</th>
    <th>Status</th>
  </tr>
  </thead>
  <tbody id="tableBody">

  </tbody> <!--This is the view file -->

<?php
require($_SERVER['DOCUMENT_ROOT']."/finalsis/include/config.php");
include_once($_SERVER['DOCUMENT_ROOT']."/finalsis/include/class.utility.php");
header("Content-Type: application/json");
$ay = $_GET['ay'];
$year = $_GET['year'];

$ay = $obj->cleanString($ay);
$year = $obj->cleanString($year);


$conn = mysqli_connect(db_server,db_user,db_password,db_database);
$ay = mysqli_escape_string($conn,$ay);
$year = mysqli_escape_string($conn,$year);

$selectSQL = "SELECT studentlevel_student, student_fname, student_mname, student_lname,
section_name, student_status FROM tblstudentlevel inner join
tblstudent on studentlevel_student = student_number inner join
tblyearsection on studentlevel_ys = ys_id WHERE studentlevel_acadyear = '" .$ay."' AND year_name = '" .$year. "'";
$result = mysqli_query($conn,$selectSQL);

$output = '{"student": [';
while($rs = mysqli_fetch_array($result)){
        $name = $obj->getFullName($rs['student_fname'],$rs['student_mname'],$rs['student_lname']);
        $output .= '{"sno":"' .$rs['studentlevel_student']. '", ';
        $output .= '"name":"' .$name. '", ';
        $output .= '"section":"' .$rs['section_name']. '",';
        $output .= '"status":"' .$rs['student_status']. '"},';
    }

$output .= "]}";
mysqli_close($conn);
echo json_encode($output);

 //This is where my data gathering happens. ?>

Don't try to encode the JSON yourself use json_encode instead php.net/manual/en/function.json-encode.php — jcubic
– jcubic, Commented Mar 31, 2016 at 8:14
In your ajax call, the data should be a json format (e.g. data: { ay: ay, year: year }). Or you can append the parameters to the url (e.g. url: "js/overallreport.php?ay=" + ay + "&year=" + year). — owenvoke
– owenvoke, Commented Mar 31, 2016 at 8:16

Abu Musab · Accepted Answer · 2016-03-31 08:18:28Z

1

Problem is in the following line:

echo json_encode($output);

json_encode needs an array. You are passing string.

$jsonData = array();
while($rs = mysqli_fetch_array($result)){
    $name = $obj->getFullName($rs['student_fname'],$rs['student_mname'],$rs['student_lname']);

     $jsonData[] = array(
         'sno' => $rs['studentlevel_student'],
         'name' => $name,
     );
 }

echo json_encode($jsonData);

answered Mar 31, 2016 at 8:18

Abu Musab

966 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zypro Over a year ago

That's the state of the art. See PHP Manual

Collectives™ on Stack Overflow

How can I extract JSON data from PHP and display it in a datatable?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related