DataTable with JSON data from URL

I have an array that I generate froma URL.

$url = 'https://www.datastro.eu/api/explore/v2.1/catalog/datasets/orbits-for-current-comets-in-the-mpc-database/records?order_by=h%20ASC' ;
$data = file_get_contents($url);
$comets = json_decode($data, true);
if ($comets === null) {
    die("Error decoding JSON data.");
}
//echo '<pre>'; print_r($comets); echo '</pre>';

I populated a table:

<table class="table table-dark table-striped display" id="myTable">
<thead>
    <tr>
      <th scope="col">designation_and_name</th>
      <th scope="col">orbit_type</th>
      ...
    </tr>
  </thead>
  <tbody>
  <?php for ($i=0; $i<=$comete['total_count']; $i++) { ?>
    <tr>
      <th scope="row"><?php echo($comete['results'][$i]['designation_and_name']); ?></th>
      <td><?php echo($comete['results'][$i]['orbit_type']); ?></td>
      ...
    </tr>
    <?php } ?>
  </tbody>
</table>

So far so good, but when I insert DataTable script the table becomes empty.

<script>
  new DataTable('#myTable');
</script>

Please help me! I want to apply a tab stop to tables that would otherwise be unmanageable.

Solution

The API returns only 10 of the total records and since you are using a for loop based on the total_count value, when the index references a row out of range, a null value is returned (generating a suppressed PHP warning) and the related echo produces an empty value therefore your table has a bunch of empty rows. Since dataTables sorts by default by first row ascending, you get all the empty rows at the top: the table is not emptied you are seeing first empty rows.

Here are some code changes to make sure of this:

<?php
$url = 'https://www.datastro.eu/api/explore/v2.1/catalog/datasets/orbits-for-current-comets-in-the-mpc-database/records?order_by=h%20ASC' ;
$data = file_get_contents($url);
$comets = json_decode($data, true);
if ($comets === null) {
    die("Error decoding JSON data.");
}

//--- Prints the total rows counter and the total rows really received

echo '<br>', 'Total rows:', $comets['total_count'], '<br>', 'Total received:', count($comets['results']), '<br><br>';

?>

<!DOCTYPE html>
<html>
<head>
    <link href="https://cdn.datatables.net/2.0.3/css/dataTables.dataTables.css" rel="stylesheet">
</head>

<body>

<table class="table table-dark table-striped display" id="myTable">

    <thead>
    <tr>
        <th scope="col">designation_and_name</th>

        <th scope="col">orbit_type</th>
        <th scope="col">node</th>
        <th scope="col">e</th>
        <th scope="col">h</th>
        <th scope="col">g</th>
    </tr>
    </thead>

    <tbody>

    <!-- Using foreach we traverse the real rows received (here in the alternative syntax for greater readability) -->

    <?php foreach ($comets['results'] as $row):?>

        <tr>

            <!-- php echo in shorthand format -->
            <th scope="row"><?= $row['designation_and_name'] ?></th>

            <td><?= $row['orbit_type'] ?></td>
            <td><?= $row['node'] ?></td>
            <td><?= $row['e'] ?></td>
            <td><?= $row['h'] ?></td>
            <td><?= $row['g'] ?></td>
        </tr>

    <?php endforeach; ?>

    </tbody>

</table>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>

<script>

    document.addEventListener('DOMContentLoaded', () => {
        let dt = new DataTable('#myTable');

        // prints in the browser's console the default order set by DataTables
        console.log (dt.order());
    });

</script>

</body>

</html>

I suppose that the API needs an authorization to receive all the rows (check here)

Edit

Reading the documentation I see that you can use this endpoint to retrieve the full data, the format is a bit different so you can apply these changes to the code:

$url

$url = 'https://www.datastro.eu/api/explore/v2.1/catalog/datasets/orbits-for-current-comets-in-the-mpc-database/exports/json';

remove the printout of the total record
the foreach() becomes:

 <?php foreach ($comets as $row): ?>