John Davidson

Message:

Sorry for being dumb, I'm quite new to PHP.
I have a table filled with data from a database:

machine.php

<?php require "database.php"; // here the connection stuff

const DOT_ONLINE = "&#128994;";

const DOT_OFFLINE = "&#128308;";

?>



<table id="tableMachines" class="table table-hover">

    <thead class="thead-dark">

    <tr>

        <th scope="col">Online</th>

        <th scope="col">Name</th>

        <th scope="col">Type</th>

        <th scope="col">Address</th>

    </tr>

    </thead>

    <tbody>



    <?php

    try {

        $stmt = $db->prepare("SELECT * FROM machines ORDER BY address;");

        $stmt->execute();

        $result = $stmt->setFetchMode(PDO::FETCH_OBJ);



        foreach ($stmt->fetchAll() as $row) {

            $rowId = "row" . $row->name;

            $output = "<tr data-bs-toggle='collapse' data-bs-target='#$rowId'>"

                . "<td>" . ($row->online ? DOT_ONLINE : DOT_OFFLINE) . "</td>"

                . "<td>" . $row->name . "</td>"

                . "<td>" . $row->type . "</td>"

                . "<td>" . $row->address . "</td>"

                ."</tr>";



            echo $output;



            $output = "<tr id='" . $rowId . "' class='collapse fade'>"

                ."<td colspan='4'>"

                ."<table class='table table-borderless'>"

                ."<tr><td>Order: " . $row->job . "</td></tr>"

                ."<tr><td>Length: " . $row->length . "</td></tr>"

                ."</table>"

                ."</td>"

                ."</tr>";



            echo $output;

        }



    } catch (PDOException $e) {

        echo "Error: " . $e->getMessage();

    }

    ?>



    </tbody>

</table>



<?php

$db = null;

As you can see, the user can click on each row to show some further details.
In the main page I put the above file inside a div:

<div id="tableMachines">

    <?php require 'machines.php' ?>

</div>

Periodically I fetch new data:

$(document).ready(function() {

    setInterval(function () {

        $('#tableMachines').load('machines.php');

    }, 5000);

});

It works fine, the problem is when the div reloads I lose the rows that were shown by the user.

It's true I can keep track of which rows were expanded and manually trigger them again after reloading the PHP code, but I don't think this is the correct approach.

Would you mind to help me to understand how to update the data without interfere with the UI?

---

My Answer:
One approach you can take is to use AJAX to fetch the updated data from the server without reloading the entire page. This way, you can update the table data without losing the collapsed rows that were previously expanded by the user.

Here's an example of how you can modify your code to use AJAX:

1. Remove the setInterval function that reloads the entire page every 5 seconds:

javascript
$(document).ready(function() {
// Remove the setInterval function
});


2. Modify your PHP code to return the table data as JSON instead of directly outputting HTML:

php
// Fetch data from the database
$data = array();
foreach ($stmt->fetchAll() as $row) {
$data[] = array(
'online' => $row->online,
'name' => $row->name,
'type' => $row->type,
'address' => $row->address,
'job' => $row->job,
'length' => $row->length
);
}

// Output the data as JSON
echo json_encode($data);


3. Update your JavaScript code to use AJAX to fetch the updated data and update the table:

javascript
$(document).ready(function() {
function updateTable() {
$.ajax({
url: 'machines.php',
type: 'GET',
dataType: 'json',
success: function(data) {
// Update the table with the new data
$('#tableMachines tbody').empty();
$.each(data, function(index, row) {
var rowId = 'row' + row.name;
var onlineIcon = row.online ? '🟢' : '🔴';
var output = '';
output += '' + onlineIcon + '';
output += '' + row.name + '';
output += '' + row.type + '';
output += '' + row.address + '';
output += '';
output += '';
output += '';
output += '';
output += '';
output += '';
output += '

Order: ' + row.job + '

Length: ' + row.length + '

';
output += '';
output += '';
$('#tableMachines tbody').append(output);
});
}
});
}

// Call the updateTable function initially
updateTable();

// Call the updateTable function every 5 seconds
setInterval(updateTable, 5000);
});


With this approach, the table data will be updated periodically without losing the collapsed rows that were previously expanded by the user.