Dynamically Generated Headers and Data via PHP Problem

Joshua37

The following code allows me to generate dynamic headers and data via a result set produced by PHP. Unfortunately, the headers generate but the first element in the data field is being removed and not rendering with the rest of the data. Almost like the headers are covering/hiding it. When I comment out the "HEADER" portion the missing data returns. How can I resolve this problem?

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM sampleTable";

$result = $conn->query($sql);
$conn->close();

<?php
>
```
?>


```html
<div class="card mb-3">
    <div class="card-header">
        <i class="fa fa-table"></i> Modules</div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
                <thead>
                    <!--HEADERS-->
                    <?php
                    if ($result->num_rows > 0) {
                        echo "<tr>";
                        foreach(array_keys($result->fetch_assoc()) as $field){
                            echo "<th>" . $field . "</th>";
                        }
                        echo "</tr>";
                    } else {
                        echo "<h1 align=\"center\">No Results Found</h1>";
                    }
                    ?>
                </thead>
                <tbody>
                    <!--DATA-->
                    <?php
                    if ($result->num_rows > 0) {
                        while($data = $result->fetch_assoc()) {
                            echo "<tr>";
                            foreach ($data as $field => $value){
                                echo "<td>" . $value . "</td>";
                            }
                            echo "</tr>";
                        }
                    }
                    ?>
                </tbody>
            </table>
        </div>
    </div>
    <div class="card-footer small text-muted">Updated yesterday at 11:59 PM</div>
</div>

<!--Not included in current file - included in base php file-->

<script type="text/javascript">
    $('#dataTable').DataTable( {
        "scrollX": true,
        "scrollY": '55vh',
        "scrollCollapse": true,
        "paging": false,
        dom: 'iftB',
        buttons: [
        'copy', 'csv', 'excel', 'pdf', 'print'
        ]
    } );
</script>

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

This is really a PHP issue rather than DataTables specific - however, I know a bit of PHP so I can identify that the issue is the use of $result->fetch_assoc() in the header. That is getting the first row from the result set, meaning it is no longer available in your tbody loop, since that first row has already been read.

One way to do this would be to move your while loop to get the data from the tbody to before the table is created, reading it into an array, then loop over the array.

Also your HTML isn't valid if there are no results. The h1 cannot be a child of the thead.

Allan