Unable to add another column to display mySQL data

hi im having difficulty in adding another non AJAX column of mySQL data which in this case is “Sub-Category” without the webpage returning an invalid JSON response can I know how to fix this ?

<?php 
$connect = mysqli_connect("localhost", "root", "password", "database");
$query = "SELECT * FROM commodity ORDER BY commodity_name ASC";
$result = mysqli_query($connect, $query);
?>
<html>
 <head>
  <title>Supplier Dictionary</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>

 </head>
 <body>
  <div class="container">
   <h1 align="center">SUPPLIER DICTIONARY</h1>
   <br />

   <div class="table-responsive">
    <table id="my30_data" class="table table-bordered table-striped">
     <thead>
      <tr>

       <th>No.</th>

       <th>Supplier Name</th>

       <th>
        <select name="commodity" id="commodity" class="form-control">
         <option value="">Commodity Search</option>
         <?php 
         while($row = mysqli_fetch_array($result))
         {
          echo '<option value="'.$row["commodity_id"].'">'.$row["commodity_name"].'</option>';
         }
         ?>
        </select>
       </th>

       <th>Status</th>

       <th>Sub-Category</th>




      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>



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

 load_data();

 function load_data(is_commodity)
 {
  var dataTable = $('#my30_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"fetch.php",
    type:"POST",
    data:{is_commodity:is_commodity}
   },
   "columnDefs":[
    {
     "targets":[2],
     "orderable":false,
    },
   ],
  });
 }

 $(document).on('change', '#commodity', function(){
  var commodity = $(this).val();
  $('#my30_data').DataTable().destroy();
  if(commodity != '')
  {
   load_data(commodity);
  }
  else
  {
   load_data();
  }
 });
});
</script>

and this is my fetch.php code where I added Sub-Category to the code and Im not sure what other changes I need to do to this coding without returning an invalid JSON response.

<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "password", "database");
$column = array("my30_rsl.my30_rsl_id", "my30_rsl.SupplierName", "commodity.commodity_name", "my30_rsl.Status","my30_rsl.Sub-Category");
$query = "
 SELECT * FROM my30_rsl
 INNER JOIN commodity 
 ON commodity.commodity_id = my30_rsl.Commodity
";
$query .= " WHERE ";
if(isset($_POST["is_commodity"]))
{
 $query .= "my30_rsl.Commodity = '".$_POST["is_commodity"]."' AND ";
}
if(isset($_POST["search"]["value"]))
{
 $query .= '(my30_rsl.my30_rsl_id LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR my30_rsl.SupplierName LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR commodity.commodity_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR my30_rsl.Status LIKE "%'.$_POST["search"]["value"].'%") ';
 $query .= 'OR my30_rsl.Sub-Category LIKE "%'.$_POST["search"]["value"].'%") ';

}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY my30_rsl.my30_rsl_id ASC ';
}

$query1 = '';

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[] = $row["my30_rsl_id"];
 $sub_array[] = $row["SupplierName"];
 $sub_array[] = $row["commodity_name"];
 $sub_array[] = $row["Status"];
 $sub_array[] = $row["Sub-Category"];

 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM my30_rsl";
 $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);

?>

why is the json response invalid?

I checked for error it gave me:
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\fetch.php on line <i>41</i></th></tr>
and:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\fetch.php on line 47

line 41 is $number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
line 47 is while($row = mysqli_fetch_array($result))

then you have to check for errors in your statement, after the query, refer to

http://php.net/manual/de/mysqli.error.php

try the following code when in development environment

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1 Like

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.