How to get JSON data from a PHP file to print in html file

Question

I have a PHP file that outputs JSON from the MySQL database, I wanted to get that JSON output into HTML file and display as a table. It works fine when I use the JSON output as it is, but I wanted to take that PHP file as a URL or as a file and get the result as a table in the HTML file.

PHP CODE:

<?php
$DBServer="localhost";
$DBUser="root";
$DBPass="";
$DBName="test";
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
$sql='SELECT userinfo.id, name, user_id, posts FROM userinfo, user_posts WHERE userinfo.id = user_posts.user_id';



$rs=$conn->query($sql);
$data = $rs->fetch_all(MYSQLI_ASSOC);
header('Content-Type: application/json');
echo json_encode($data);



?>

HTML CODE:

 <script>
       $(document).ready(function() {

      // Need to take PHP URL or file name instead of JSON data

     var data = {
        "report":  [{
            "id": "Abril",
            "name": "13",
            "user_id": "Lisboa",

        }]
      };  



      // Loop through data.report instead of data
      for (var i = 0; i < data.report.length; i++) {
        var tr = $('<tr/>');

        // Indexing into data.report for each td element
        $(tr).append("<td>" + data.report[i].id+ "</td>");
        $(tr).append("<td>" + data.report[i].name+ "</td>");
        $(tr).append("<td>" + data.report[i].user_id+ "</td>");

        $('.table1').append(tr);
      }
    });
    </script>

    <div class="container">
            <div class="row">
                <div class="table-responsive">
                    <table class="table1 table"  >
                        <thead>
                            <tr>
                            <th>ID</th>
                            <th>NAME</th>
                            <th>USER ID</th>


                             </tr>
                        </thead>

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

Answer 1

You could use fetch api to load the data from the php file and display on the screen as table.

Example.

(async function() {
  try {
    let tableEle = document.getElementById("content");
    let response = await fetch("<php file url>");
    let body = await response.json();
    let rows = "";
    for (const item of body) {
      rows += `<tr>
        <td>${item.id}</td>
        <td>${item.name}<t/d>
        <td>${item.user_id}</td>
      </tr>`;
    }
    tableEle.querySelector("tbody").innerHTML = rows;
  } catch (error) {
    console.log(error);
  }
})();

HTML:

 <div class="container">
      <div class="row">
        <div class="table-responsive">
          <table class="table1 table" id="content">
            <thead>
              <tr>
                <th>ID</th>
                <th>Name</th>
                <th>User ID</th>
              </tr>
            </thead>
            <tbody></tbody>
          </table>
        </div>
      </div>
    </div>

Working sample with jsonplaceholder api.

(async function() {
  try {
    let tableEle = document.getElementById("content");
    let response = await fetch("https://jsonplaceholder.typicode.com/posts");
    let body = await response.json();
    let rows = "";
    for (const item of body) {
      rows += `<tr>
        <td>${item.id}</td>
        <td>${item.title}</td>
        <td>${item.userId}</td>
      </tr>`;
    }
    tableEle.querySelector("tbody").innerHTML = rows;
  } catch (error) {
    console.log(error);
  }
})();

#content {
    font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 100%;
  }

  #content td,
  #customers th {
    border: 1px solid #ddd;
    padding: 8px;
  }

  #content tr:nth-child(even) {
    background-color: #f2f2f2;
  }

  #content tr:hover {
    background-color: #ddd;
  }

  #content th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: #4caf50;
    color: white;
  }

<div class="container">
      <div class="row">
        <div class="table-responsive">
          <table class="table1 table" id="content">
            <thead>
              <tr>
                <th>ID</th>
                <th>Title</th>
                <th>UserID</th>
              </tr>
            </thead>
            <tbody>
            </tbody>
          </table>
        </div>
      </div>
    </div>

Answer 2

Welcome, Mohamed.

Since you are using already jQuery I would perhaps use their ajax functionality. https://api.jquery.com/jquery.ajax/

$.ajax({
    url : 'https://server.example.org/index.php',
    type : 'GET',
    dataType:'json',
    success: function(data) {              
       // Here you add your functionality after the call has succeeded.
       // alert('Data: ' + data); 

       // Loop through data.report instead of data
       for (var i = 0; i < data.report.length; i++) {
         var tr = $('<tr/>');

         // Indexing into data.report for each td element
         $(tr).append("<td>" + data.report[i].Mes + "</td>");
         $(tr).append("<td>" + data.report[i].Dia + "</td>");
         $(tr).append("<td>" + data.report[i].Local + "</td>");

         $('.table1').append(tr);
      }
    },
    error: function(request, error) {
        alert("Request: " + JSON.stringify(request));
    }
});