"No data available in Table" even data exists within the table

Question

Why does my table displays the error "No data available in the Table" and shows "Showing 0 of 0 entries" below. Here is the screenshot

I have no idea why does this happen. I'm using mysqli_fetch_assoc to get the data from my query.

<div class="card mb-3">
    <div class="card-header">
        <i class="fas fa-table"></i>
        Data Table
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
                <thead>
                    <tr>
                        <th>Ref. No.</th>
                        <th>Date/Time</th>
                        <th>Line No.</th>
                        <th>Optr. Name</th>
                        <th>Weight</th>
                        <th>Moisture</th>
                        <th>Product ID</th>
                        <th>Item Code</th>
                    </tr>
                    <?php while ($rows = mysqli_fetch_assoc($sql)) { ?>
                        <tr>
                            <td><?php echo $rows['mb_refno']; ?></td>
                            <td><?php echo $rows['mb_weight_dt']; ?></td>
                            <td><?php echo $rows['mb_line_id']; ?></td>
                            <td><?php echo $rows['mb_opt_name']; ?></td>
                            <td><?php echo $rows['mb_weight']; ?></td>
                            <td><?php echo $rows['mb_moisture']; ?></td>
                            <td><?php echo $rows['mb_prod_id']; ?></td>
                            <td><?php echo $rows['mb_prod_code']; ?></td>
                        </tr>
                    <?php } ?> 
                </tbody>
            </table>
        </div>
    </div>
    <div class="card-footer small text-muted">Updated yesterday at 11:59 PM</div>
</div>


<tbody>
  <?php while ($rows = mysqli_fetch_assoc($sql)) { ?>
    <tr>
      <td><?php echo $rows['mb_refno']; ?></td>
      <td><?php echo $rows['mb_weight_dt']; ?></td>
      <td><?php echo $rows['mb_line_id']; ?></td>
      <td><?php echo $rows['mb_opt_name']; ?></td>
      <td><?php echo $rows['mb_weight']; ?></td>
      <td><?php echo $rows['mb_moisture']; ?></td>
      <td><?php echo $rows['mb_prod_id']; ?></td>
      <td><?php echo $rows['mb_prod_code']; ?></td>
    </tr>
    <?php } ?>  
</tbody>

Answer 1

FYI dataTables requires a well formed table. It must contain <thead> and <tbody> tags, otherwise it throws this error. Also, check to make sure all your rows including header row have the same number of columns.

<div class="card mb-3">
    <div class="card-header">
        <i class="fas fa-table"></i>
        Data Table
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
                <thead>
                    <tr>
                        <th>Ref. No.</th>
                        <th>Date/Time</th>
                        <th>Line No.</th>
                        <th>Optr. Name</th>
                        <th>Weight</th>
                        <th>Moisture</th>
                        <th>Product ID</th>
                        <th>Item Code</th>
                    </tr>
                 </thead>
                 <tbody>
                    <?php while ($rows = mysqli_fetch_assoc($sql)) { ?>
                        <tr>
                            <td><?php echo $rows['mb_refno']; ?></td>
                            <td><?php echo $rows['mb_weight_dt']; ?></td>
                            <td><?php echo $rows['mb_line_id']; ?></td>
                            <td><?php echo $rows['mb_opt_name']; ?></td>
                            <td><?php echo $rows['mb_weight']; ?></td>
                            <td><?php echo $rows['mb_moisture']; ?></td>
                            <td><?php echo $rows['mb_prod_id']; ?></td>
                            <td><?php echo $rows['mb_prod_code']; ?></td>
                        </tr>
                    <?php } ?> 
                </tbody>
            </table>
        </div>
    </div>
    <div class="card-footer small text-muted">Updated yesterday at 11:59 PM</div>
</div>

Answer 2

You can only read the results of your query with mysqli_fetch_assoc once. If you attempt to read it again, you get the error message. If you really want to read the result of the query twice, you must first do the query again.