基于Ajax与PHP实现动态图表数据过滤及数据库绘图技术问询

Alright, let's build that dynamic filtering feature for your chart using Ajax and PHP. I'll walk you through each step based on the code you already have, making sure it's straightforward and secure.

First, add filtering controls (like date pickers) and set up Chart.js integration with Ajax. This lets users input filters and triggers chart updates without reloading the page:

<!-- Filter Controls -->
<div class="filter-bar">
  <input type="date" id="startDate" placeholder="Start Date">
  <input type="date" id="endDate" placeholder="End Date">
  <button id="filterBtn">Apply Filters</button>
</div>

<!-- Chart Container (your existing code) -->
<div>
  <canvas id="mycanvas" height="150"></canvas>
</div>

<!-- Load Chart.js (use a CDN or local file) -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<script>
// Global chart instance to update later
let userLoginChart;

// Load initial chart on page load
window.addEventListener('load', () => fetchFilteredData());

// Bind filter button to data fetch
document.getElementById('filterBtn').addEventListener('click', fetchFilteredData);

function fetchFilteredData() {
  const startDate = document.getElementById('startDate').value;
  const endDate = document.getElementById('endDate').value;
  const clientID = <?php echo $clientID; ?>; // Pass your client ID safely

  // Send Ajax POST request to PHP endpoint
  fetch('fetch-chart-data.php', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: `startDate=${startDate}&endDate=${endDate}&clientID=${clientID}`
  })
  .then(response => response.json())
  .then(data => {
    // Destroy existing chart if it exists to avoid conflicts
    if (userLoginChart) userLoginChart.destroy();

    // Initialize new chart with filtered data
    const ctx = document.getElementById('mycanvas').getContext('2d');
    userLoginChart = new Chart(ctx, {
      type: 'line', // Switch to 'bar' or 'pie' if needed
      data: {
        labels: data.dates, // Array of formatted dates from PHP
        datasets: [{
          label: 'Daily Logins',
          data: data.loginCounts, // Array of user counts
          borderColor: '#2196F3',
          backgroundColor: 'rgba(33, 150, 243, 0.1)',
          tension: 0.3
        }]
      },
      options: {
        scales: {
          y: { beginAtZero: true, title: { display: true, text: 'Number of Users' } },
          x: { title: { display: true, text: 'Date' } }
        }
      }
    });
  })
  .catch(err => console.error('Failed to load chart data:', err));
}
</script>

Make a dedicated fetch-chart-data.php file to handle filtered queries, process data, and return JSON (ideal for Ajax):

<?php
// Initialize your MySQLi connection (update with your credentials)
$mysqli = new mysqli('localhost', 'db_user', 'db_pass', 'db_name');
if ($mysqli->connect_error) die("Connection failed: " . $mysqli->connect_error);

// Get parameters from Ajax request
$clientID = $_POST['clientID'] ?? '';
$startDate = $_POST['startDate'] ?? '';
$endDate = $_POST['endDate'] ?? '';

// Build safe SQL query with prepared statements (prevents SQL injection)
$sql = "SELECT COUNT(social_id) as total, DATE_FORMAT(login_at, '%d-%m') as datee 
        FROM social_users_connections 
        WHERE client_id = ? 
        AND login_at < DATE_ADD(NOW(), INTERVAL +1 MONTH)";

// Add date filters if provided
$params = [$clientID];
$paramTypes = 'i'; // clientID is integer

if (!empty($startDate)) {
  $sql .= " AND login_at >= ?";
  $params[] = $startDate . ' 00:00:00';
  $paramTypes .= 's';
}
if (!empty($endDate)) {
  $sql .= " AND login_at <= ?";
  $params[] = $endDate . ' 23:59:59';
  $paramTypes .= 's';
}

$sql .= " GROUP BY datee ORDER BY login_at";

// Execute prepared statement
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($paramTypes, ...$params);
$stmt->execute();
$result = $stmt->get_result();

// Format data for chart
$dates = [];
$loginCounts = [];
while ($row = $result->fetch_assoc()) {
  $dates[] = $row['datee'];
  $loginCounts[] = $row['total'];
}

// Return JSON response
echo json_encode([
  'dates' => $dates,
  'loginCounts' => $loginCounts
]);

// Cleanup
$stmt->close();
$mysqli->close();
?>

• Security: Always use prepared statements for SQL queries—never concatenate user input directly into your SQL. This prevents SQL injection attacks.
• Date Handling: The HTML date input uses YYYY-MM-DD format, so we append times to ensure we capture all records for the selected dates.
• Chart Updates: We destroy the existing chart before reinitializing it to avoid rendering conflicts.
• Error Handling: Add frontend alerts (e.g., alert('Failed to load data. Please try again.') in the catch block) to improve user experience.
• Client ID Safety: If $clientID is tied to a logged-in user, avoid echoing it directly in JS. Instead, fetch it from $_SESSION['clientID'] in the PHP endpoint for better security.

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