-1

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.

Improve this question
3
  • There seems to be an inconsistency in variable names between PHP and JavaScript. Make sure to use the same variable name when decoding JSON data in PHP and initializing the DataTable in JavaScript. Commented Apr 11, 2024 at 16:01
  • Try the following: [PHP code goes here] <table class="table table-dark table-striped display" id="myTable"> <!-- Table header goes here --> <tbody> <?php for ($i = 0; $i < count($comets['results']); $i++) { ?> <tr> <th scope="row"><?php echo $comets['results'][$i]['designation_and_name']; ?></th> <td><?php echo $comets['results'][$i]['orbit_type']; ?></td> </tr> <?php } ?> </tbody> </table> <script> $(document).ready(function() { $('#myTable').DataTable(); }); </script> Commented Apr 11, 2024 at 16:01
  • Thanks for the reply. In fact the name of the arrays did not match but in my code they were correct. I just wrote them wrong in the question. If I use this URL: datastro.eu/api/explore/v2.1/catalog/datasets/… with where, order and limit clause datatable is ok but if i delete ?where=h%3C%3D6&order_by=h%20ASC&limit=100 the table is empty. Commented Apr 11, 2024 at 17:00

1 Answer 1

Reset to default
0

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): ?>
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you! Everything about it is correct.The API only returns 10 records if I don't indicate a limit in the query and in fact they were at the end of the table. I believe that the service to get them all is paid. However, I noticed that if I enter limit 100 in the query it returns 100 records + all the other empty rows, if I enter more than 100 it gives me a json file reading error. I'll try using foreach to avoid displaying blank lines. You were enlightening, thank you!
I've edited the answer with more informations
With these latest changes of yours I now have all the records in the table with datatable. You were invaluable, thank you. Unfortunately for those who don't know English like me it's always difficult to read documentation, I immediately get discouraged.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.