MySQL数据拉取状态下表格CSS动态切换及样式控制问询

Hey there! Let's build out that dynamic table you're looking for—here's a complete, working solution that hits all your requirements: matching beacon values to table cells, styling based on location and delivery averages, and reverting to defaults when you stop pulling data.

Core Logic Recap #

First, let's align on the key behaviors we're implementing:

• Beacon Matching: Each table row/cell is linked to a MySQL beacon_id via a data attribute
• Location-Based Backgrounds: Cell background color changes depending on the location value from your database
• Delivery Average Border Styles: Border thickness/style adjusts based on the delivery_avg metric
• Stop State: When data fetching is paused, all cells revert to a gray background and thin black border

Example MySQL Data Structure #

Let's start with a sample table structure to mirror your data:

CREATE TABLE delivery_metrics (
  beacon_id VARCHAR(20) PRIMARY KEY,
  location VARCHAR(50) NOT NULL,
  delivery_avg DECIMAL(5,2) NOT NULL
);

-- Sample test data
INSERT INTO delivery_metrics VALUES
('BEACON-001', 'North Warehouse', 4.7),
('BEACON-002', 'South Warehouse', 2.3),
('BEACON-003', 'North Warehouse', 3.5);

HTML Table Layout #

We'll create a table where each row has a data-beacon attribute matching the beacon_id from MySQL. This is how we'll map database entries to table cells:

<table id="deliveryTable">
  <thead>
    <tr>
      <th>Beacon ID</th>
      <th>Location</th>
      <th>Avg Delivery Time</th>
    </tr>
  </thead>
  <tbody>
    <tr data-beacon="BEACON-001">
      <td>BEACON-001</td>
      <td class="location-cell"></td>
      <td class="avg-cell"></td>
    </tr>
    <tr data-beacon="BEACON-002">
      <td>BEACON-002</td>
      <td class="location-cell"></td>
      <td class="avg-cell"></td>
    </tr>
    <tr data-beacon="BEACON-003">
      <td>BEACON-003</td>
      <td class="location-cell"></td>
      <td class="avg-cell"></td>
    </tr>
  </tbody>
</table>

<button id="toggleFetchBtn">Stop Fetching Data</button>

CSS Styling #

Define default styles and conditional classes for location backgrounds and border styles:

/* Default state (stopped fetching) */
#deliveryTable tbody tr {
  background-color: #e5e5e5; /* Gray */
  border: 1px solid #000; /* Thin black border */
  transition: all 0.3s ease; /* Smooth style transitions */
}

/* Location-specific background colors */
.location-north-warehouse { background-color: #c6f6d5; } /* Light green */
.location-south-warehouse { background-color: #fed7d7; } /* Light red */

/* Border styles based on delivery average */
.border-high-perf { border: 3px solid #2f855a; } /* Thick green for high averages */
.border-low-perf { border: 2px dashed #c53030; } /* Dashed red for low averages */
.border-normal-perf { border: 1px solid #000; } /* Default for mid-range */

/* Table styling for readability */
#deliveryTable {
  border-collapse: collapse;
  width: 100%;
  margin: 20px 0;
}
#deliveryTable th, #deliveryTable td {
  padding: 12px;
  text-align: left;
  border: 1px solid #ddd;
}

Full Implementation (Backend + Frontend) #

Backend Endpoint (PHP Example) #

Create a simple endpoint to fetch MySQL data and return it as JSON (you can use Node.js/Python/etc. if preferred):

// fetch_delivery_data.php
<?php
// Connect to MySQL
$conn = new mysqli('localhost', 'your_username', 'your_password', 'your_database');
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

// Fetch data
$result = $conn->query("SELECT beacon_id, location, delivery_avg FROM delivery_metrics");
$response = [];
while ($row = $result->fetch_assoc()) {
  $response[] = $row;
}

// Return JSON
header('Content-Type: application/json');
echo json_encode($response);
$conn->close();
?>

Frontend JavaScript #

This code handles fetching data at intervals, updating the table styles, and toggling the fetch state:

let fetchInterval;
const table = document.getElementById('deliveryTable');
const toggleBtn = document.getElementById('toggleFetchBtn');

// Update table with new data from MySQL
function updateTable(data) {
  // Reset all rows to default first
  const rows = table.querySelectorAll('tbody tr');
  rows.forEach(row => {
    row.className = '';
    const locationCell = row.querySelector('.location-cell');
    const avgCell = row.querySelector('.avg-cell');
    locationCell.textContent = '';
    avgCell.textContent = '';
  });

  // Apply styles and data for each matching beacon
  data.forEach(item => {
    const row = table.querySelector(`tbody tr[data-beacon="${item.beacon_id}"]`);
    if (!row) return;

    // Set location and background color
    const locationCell = row.querySelector('.location-cell');
    locationCell.textContent = item.location;
    const locationClass = `location-${item.location.toLowerCase().replace(/\s+/g, '-')}`;
    row.classList.add(locationClass);

    // Set delivery average and border style
    const avgCell = row.querySelector('.avg-cell');
    avgCell.textContent = `${item.delivery_avg} mins`;
    
    if (item.delivery_avg >= 4) {
      row.classList.add('border-high-perf');
    } else if (item.delivery_avg <= 3) {
      row.classList.add('border-low-perf');
    } else {
      row.classList.add('border-normal-perf');
    }
  });
}

// Fetch data from backend
function fetchData() {
  fetch('fetch_delivery_data.php')
    .then(res => res.json())
    .then(data => updateTable(data))
    .catch(err => console.error('Fetch error:', err));
}

// Toggle data fetching on/off
toggleBtn.addEventListener('click', () => {
  if (fetchInterval) {
    // Stop fetching and reset table to default
    clearInterval(fetchInterval);
    fetchInterval = null;
    toggleBtn.textContent = 'Start Fetching Data';
    
    const rows = table.querySelectorAll('tbody tr');
    rows.forEach(row => {
      row.className = '';
      row.style.backgroundColor = '#e5e5e5';
      row.style.border = '1px solid #000';
      row.querySelector('.location-cell').textContent = '';
      row.querySelector('.avg-cell').textContent = '';
    });
  } else {
    // Start fetching every 5 seconds
    fetchInterval = setInterval(fetchData, 5000);
    toggleBtn.textContent = 'Stop Fetching Data';
    fetchData(); // Fetch immediately on start
  }
});

// Start fetching when page loads
fetchInterval = setInterval(fetchData, 5000);
fetchData();

Key Customization Tips #

• Beacon Matching: Ensure your data-beacon attributes exactly match the beacon_id values in MySQL
• Location Styles: Add more location-* CSS classes to support additional warehouse/location values
• Border Thresholds: Adjust the delivery_avg values in the JavaScript (4 and 3) to match your performance metrics
• Fetch Interval: Change the 5000 value (milliseconds) to adjust how often data is pulled from MySQL

内容的提问来源于stack exchange，提问作者Avalon Parkes-Barton