如何为表格Time Difference列设置倒计时及到期前1小时20分弹窗提示？

Alright, let's get this working. The key here is to track remaining time dynamically, update it every second, and trigger an alert when we hit that 1h20m threshold. Here's how to adjust your code step by step:

Step 1: Update Your SQL Query to Get Raw Seconds #

First, we need the total number of seconds in the time difference (instead of just the formatted string) to calculate the countdown accurately. Modify your SQL to include TIMESTAMPDIFF(SECOND, arrival, departure) AS total_seconds:

$sql="SELECT *, 
TIME_FORMAT(arrival, '%h:%i %p') AS arrival, 
TIME_FORMAT(departure, '%h:%i %p') AS departure, 
TIME_FORMAT(timediff(departure, arrival), '%H:%i') AS difftime,
TIMESTAMPDIFF(SECOND, arrival, departure) AS total_seconds 
FROM master where Status = 'Pending'";

Step 2: Modify the Table Cell to Store Countdown Data #

Update the Time Difference cell to hold the total seconds as a data attribute, and give it a class so we can target it with JavaScript:

<td>
  <span class="countdown-timer" data-total-seconds="<?php echo htmlentities($row->total_seconds); ?>" style="color: red;">
    <?php echo htmlentities($row->difftime); ?>
  </span>
</td>

Step 3: Add JavaScript to Handle Countdown and Alerts #

Add this script right before the closing </body> tag. It initializes timers for each countdown cell, updates the display every second, and shows an alert when only 80 minutes (4800 seconds) are left:

<script>
// Convert seconds to HH:MM format
function formatTime(seconds) {
  const hours = Math.floor(seconds / 3600);
  const minutes = Math.floor((seconds % 3600) / 60);
  return `${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}`;
}

// Start countdowns when the page loads
document.addEventListener('DOMContentLoaded', function() {
  const timers = document.querySelectorAll('.countdown-timer');
  
  timers.forEach(timerElement => {
    let remainingSeconds = parseInt(timerElement.dataset.totalSeconds);
    
    // Update display immediately
    timerElement.textContent = formatTime(remainingSeconds);
    
    // Update every second
    const interval = setInterval(() => {
      if (remainingSeconds <= 0) {
        clearInterval(interval);
        timerElement.textContent = 'Expired';
        return;
      }
      
      remainingSeconds--;
      timerElement.textContent = formatTime(remainingSeconds);
      
      // Trigger alert at 1h20m remaining (4800 seconds)
      if (remainingSeconds === 4800) {
        alert('Warning: A time difference is about to expire in 1 hour 20 minutes!');
      }
    }, 1000);
  });
});
</script>

Full Modified Code #

Putting it all together, your final code will look like this:

<body>
  <div class="card-body">
    <table class="table">
      <thead>
        <tr>
          <th>IdCard</th>
          <th>Name</th>
          <th>Company</th>
          <th>Floors</th>
          <th>Arrival</th>
          <th>Departure</th>
          <th>Time Difference</th>
        </tr>
      </thead>
      <?php 
      include('includes/dbconnection.php'); 
      $sql="SELECT *, 
      TIME_FORMAT(arrival, '%h:%i %p') AS arrival, 
      TIME_FORMAT(departure, '%h:%i %p') AS departure, 
      TIME_FORMAT(timediff(departure, arrival), '%H:%i') AS difftime,
      TIMESTAMPDIFF(SECOND, arrival, departure) AS total_seconds 
      FROM master where Status = 'Pending'"; 
      $query = $dbh -> prepare($sql); 
      $query->execute(); 
      $results=$query->fetchAll(PDO::FETCH_OBJ); 
      $cnt=1; 
      if($query->rowCount() > 0) { 
        foreach($results as $row) { ?>
          <tr>
            <td><?php echo htmlentities($row->idcard);?></td>
            <td><?php echo htmlentities($row->name);?></td>
            <td><?php echo htmlentities($row->company);?></td>
            <td><?php echo htmlentities($row->floors);?></td>
            <td><?php echo htmlentities($row->arrival);?></td>
            <td><?php echo htmlentities($row->departure);?></td>
            <td>
              <span class="countdown-timer" data-total-seconds="<?php echo htmlentities($row->total_seconds); ?>" style="color: red;">
                <?php echo htmlentities($row->difftime); ?>
              </span>
            </td>
          </tr>
        <?php $cnt=$cnt+1; } 
      } ?>
    </table>
  </div>

  <script>
    // Convert seconds to HH:MM format
    function formatTime(seconds) {
      const hours = Math.floor(seconds / 3600);
      const minutes = Math.floor((seconds % 3600) / 60);
      return `${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}`;
    }

    // Start countdowns when the page loads
    document.addEventListener('DOMContentLoaded', function() {
      const timers = document.querySelectorAll('.countdown-timer');
      
      timers.forEach(timerElement => {
        let remainingSeconds = parseInt(timerElement.dataset.totalSeconds);
        
        // Update display immediately
        timerElement.textContent = formatTime(remainingSeconds);
        
        // Update every second
        const interval = setInterval(() => {
          if (remainingSeconds <= 0) {
            clearInterval(interval);
            timerElement.textContent = 'Expired';
            return;
          }
          
          remainingSeconds--;
          timerElement.textContent = formatTime(remainingSeconds);
          
          // Trigger alert at 1h20m remaining (4800 seconds)
          if (remainingSeconds === 4800) {
            alert('Warning: A time difference is about to expire in 1 hour 20 minutes!');
          }
        }, 1000);
      });
    });
  </script>
</body>

Quick Notes: #

• The formatTime function keeps the display consistent with your original HH:MM format.
• Each timer runs independently, and we clear the interval once time hits zero to avoid unnecessary processing.
• If you need to adjust the alert threshold, just change the number in the remainingSeconds === 4800 check (e.g., 3600 for 1 hour).

内容的提问来源于stack exchange，提问作者Eliana