Display Data from two tables using JQuery

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, bpm_category as BPMC WHERE 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, bpm_category as BPMC WHERE 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.

https://stackoverflow.com/questions/65838399/display-data-from-two-tables-using-jquery January 22, 2021 at 10:07AM