Print sql table with JSON

Question

I want to print all sql table and show them in html table. I am new in php, JSON and AJAX. I send username successfully and get result in php. I think there is a problem in JSON part or AJAX. Can anyone help me?

index.php

<div class="col-lg-6">
  <p id="usr" style="color:gray; font-size: 48px;"></p>
  <script type="text/javascript">

      var usr = document.getElementById("dom-target");
      var username = usr.textContent;
      username = username.trim().replace(/ /g, '%20');
      document.getElementById("usr").innerHTML = username;
      var sendtophp = "username="+username;

      $.ajax({
        type: "POST",
        url: "getcoursetable.php",
        data: sendtophp,
        dataType:"json",
        success: function(response) {
          console.log(response);
          var trhtml ='';
          document.getElementById("demo").innerHTML = response;
          $.each(response, function (i, item) {
              trHTML += '<tr><td>' + item.cname + '</td><td>' + item.subject + '</td><td>' + item.course + '</td><td>'+ item.grade + '</td></tr>';
          });
          $('#results').append(trHTML);
        }
      });

  </script>
  <table id="results"></table>
</div>

getcoursetable.php

<?php
  include_once "connection.php";

  if(isset($_POST["username"])){
    $nick = $_POST["username"];

    $prep = "SELECT * FROM `enrolledtable` WHERE nickname='$nick'";

    $results = mysqli_query($con, $prep);

    $jsonData = array();
    while ($row = $results->fetch_row()) {
        $jsonData[] = $row;
    }
    echo json_encode($jsonData);
      }
 ?>

Now, I can print data but not like a table, like that

<p id="demo">denemee,CS,300,B,denemee,CS,301,B ,denemee,CS,305,B ,denemee,CS,307,B,denemee,CS,408,A-,denemee,IE,208,B ,denemee,MATH,306,B</p>

**1)** do not use mysql_* functions, which are unsecure proven for years [See reference](https://stackoverflow.com/questions/14151458/difference-between-mysql-mysqli). Use mysqli_* or PDO. **2)** Then, to show the results, look for [Datatables](https://datatables.net/) -- You will save a lot time by learning to do it right from the start. — Louys Patrice Bessette, Nov 25 '17 at 23:06
In this project security is not important but thanks. Where is wrong in datatables? — ozan, Nov 25 '17 at 23:08
Your PHP resulting array (still can be seen in the edit history) is perfectly formed to be used with Datatables. That will save you A LOT time... Look for it. There is absolutely no gain in re-inventing the wheel. — Louys Patrice Bessette, Nov 25 '17 at 23:15
read my edit so i can delete the answer when you resolve..bye — , Nov 25 '17 at 23:56

score 1 · Answer 1 · answered Nov 25 '17 at 23:25

1

your ajax function is looking for data of type json so you need to declare this at the top of getcoursetable.php

header('Content-Type: application/json');

answered Nov 25 '17 at 23:25

Chris

987
2
12
25

I added and I am searching what is the benefit of this. – ozan Nov 25 '17 at 23:42
This is absolutely not necessary...LOL content type json with json encode in ajax call it's absurd – Nov 26 '17 at 00:10

score 0 · Answer 2 · answered Nov 25 '17 at 23:21

0

The problem might sit around here :

      console.log(response);
      var trhtml ='';
      document.getElementById("demo").innerHTML = response;
      $.each(response, function (i, item) {
          trHTML += '<tr><td>' + item.cname + '</td><td>' + item.subject + '</td><td>' + item.course + '</td><td>'+ item.grade + '</td></tr>';
      });
      $('#results').append(trHTML);

First, JavaScript is a Case Sensitive language : trhtml and trHTML are not the same variables.

Second, if your sentence "output of php" means you reported the output of console.log(), then response look like a string to me, you must make it parsed as Json.

Moreover, I don't know what the beginning of the string denemee is but it breaks the Json notation.

answered Nov 25 '17 at 23:21

Julien P.

11
3

Copy-paste causes case sensitive error, sorry for this stupid error. _denemee_ is nickname. The _item.cname_ is not parse you said? I should parse as JSON. – ozan Nov 25 '17 at 23:32
Don't know about item.cname, I was taking about the response string printed by `console.log()`. Glad you solved your problem. It could have been useful to others to detail your error and process ;) Bye – Julien P. Nov 27 '17 at 08:49
Because cname is a column name in sql nobody knows it, I do not mention that. Thanks for your effort. Bye – ozan Nov 27 '17 at 13:32

score 0 · Accepted Answer · answered Nov 26 '17 at 00:12

I solve my problem. This can take table in php and create html table.

index.php

<div class="col-lg-6">
  <p id="usr" style="color:gray; font-size: 48px;"></p>
  <script type="text/javascript">

      var usr = document.getElementById("dom-target");
      var username = usr.textContent;
      username = username.trim().replace(/ /g, '%20');
      document.getElementById("usr").innerHTML = username;
      var sendtophp = "username="+username;

      $.ajax({
        type: "POST",
        url: "getcoursetable.php",
        data: sendtophp,
        dataType:"json",
        success: function(response) {
          var trhtml ='';
          $.each(response, function (i, item) {
              trhtml += '<tr><td>' + item[0] + '</td><td>' + item[1] + '</td><td>'+ item[2] + '</td></tr>';
          });
          $('.append').append(trhtml);
        }
      });

  </script>
  <table id="results">

    <tr>
    <th>Name</th>
    <th>Subject</th>
    <th>Course</th>
    <th>Grade</th>
    </tr>
    <tbody class="append">

    </tbody>
    </table>
</div>

getcoursetable.php

<?php
  header('Content-Type: application/json');
      include_once "connection.php";

      if(isset($_POST["username"])){
        $nick = $_POST["username"];

        $prep = "SELECT subject,course,grade FROM `enrolledtable` WHERE nickname='$nick'";

        $results = mysqli_query($con, $prep);

        $jsonData = array();
        while ($row = $results->fetch_row()) {
            $jsonData[] = $row;
        }
        echo json_encode($jsonData);
      }
 ?>

Print sql table with JSON

3 Answers3