php - Display Data from two tables

Question

Good day, I'm having a hard querying data from two tables using JQuery.

I'm able to display the records without any issue if I'm just querying from 1 table.

    <?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "hh_bpm");


$query = "SELECT * FROM bpm_category_periodstats ";




if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="first_name">' . $row["Category_ID"] . '</div>';
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="last_name">' . $row["Period1"] . '</div>';
  $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="Middle_Name">' . $row["Period1"] . '</div>';
    $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="Middle_Name">' . $row["Period1"] . '</div>';
 $sub_array[] = '<button type="button" name="delete" class="btn btn-danger btn-xs delete" id="'.$row["bpmTest_ID"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM bpm_category_periodstats";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>

The issue is I want to display the Category Name, rather than the ID. And I can do that if I will join the two tables. But if I do that it is giving me an error.

Please see this screenshot: enter image description here

This is the SQL statements for two tables

SELECT BPMC.Category, BPMTEST. * FROM bpm_category_periodstats as BPMTEST INNER JOIN bpm_category as BPMC ON BPMTEST.Category_ID = BPMC.Cat_ID

I've tested it on mysql and it is giving me the correct information.

<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "hh_bpm");


$query = "SELECT BPMC.Category, BPMTEST. * FROM bpm_category_periodstats as BPMTEST INNER JOIN bpm_category as BPMC ON BPMTEST.Category_ID = BPMC.Cat_ID";


if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="first_name">' . $row["Category"] . '</div>';
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="last_name">' . $row["Period1"] . '</div>';
  $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="Middle_Name">' . $row["Period1"] . '</div>';
    $sub_array[] = '<div contenteditable class="update" data-id="'.$row["bpmTest_ID"].'" data-column="Middle_Name">' . $row["Period1"] . '</div>';
 $sub_array[] = '<button type="button" name="delete" class="btn btn-danger btn-xs delete" id="'.$row["bpmTest_ID"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT BPMC.Category, BPMTEST. * FROM bpm_category_periodstats as BPMTEST INNER JOIN bpm_category as BPMC ON BPMTEST.Category_ID = BPMC.Cat_ID";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>

Please help.

P.S

This is the index file where I'm calling fetch.php file where the codes above is saved.

<html>
 <head>
  <title>Base Pricing Model</title>
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
  <style>
  body
  {
   margin:0;
   padding:0;
   background-color:#f1f1f1;
  }
  .box
  {
   width:1270px;
   padding:20px;
   background-color:#fff;
   border:1px solid #ccc;
   border-radius:5px;
   margin-top:25px;
   box-sizing:border-box;
  }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">Base Pricing Model - Prototype</h1>
   <br />
   <div class="table-responsive">
   <br />
    <div align="right">
     <button type="button" name="add" id="add" class="btn btn-info">Add</button>
    </div>
    <br />
    <div id="alert_message"></div>
    <table id="user_data" class="table table-bordered table-striped">
     <thead>
      <tr>
      <th>Category</th>
       <th>Period 1</th>
       <th>Period 2</th>
       <th>Period 3</th>
       <th></th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>

<script type="text/javascript" language="javascript" >
 $(document).ready(function(){
  
  fetch_data();

  function fetch_data()
  {
   var dataTable = $('#user_data').DataTable({
    "processing" : true,
    "serverSide" : true,
    "order" : [],
    "ajax" : {
     url:"fetch.php",
     type:"POST"
    }
   });
  }
  
 });
</script>

question from:https://stackoverflow.com/questions/65838399/display-data-from-two-tables

Answer 1

Waitting for answers