Update db data without losing collapsed rows-CodePudding

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" >
    <thead >
    <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?

CodePudding user response：

If this were me, I would track which last machine ID is pulled through and then send that on your load() call. You can then call $('#tableMachines').prepend(data) rather than reloading the whole table - you only reload the latest rows. That way your users choices will be unaffected however you may need to re-bind click/triggers so the new rows expand/hide as well.

CodePudding user response：

Before loading machine.php, you can create a cookie named "expanded_set" in your jquery code that loads machine.php. Each time visitor toggles, you can populate the cookie. In machine.php file, your cookie will be accessible by $_COOKIE['user_expandeds']. Then you can customize machine.php to handle the already-toggled ones to show them also.

I'm not sure of this method for timing issues however, you may also send the "expanded_set" to PHP via a POST type form with a hidden input field with a name which is submitted by jquery automatically also. You can access the posted field with its name as $_POST['expanded_set'].

cookie method may fail if cookies are not allowed by the visitor but using a form method can not be disallowed.