如何使用Ajax和PHP实现无需页面刷新从数据库获取实时数据？

Hey there! Nice job getting the Ajax insert functionality working—you're halfway there. Let's fix that real-time data display bottleneck so your voting results update smoothly without page refreshes. Here are a few practical approaches tailored to your setup:

1. Simple Polling (Quickest to Implement) #

This is the easiest method for small-scale apps: we’ll set up a repeating Ajax request to fetch the latest vote data at regular intervals, then update the UI with the new numbers.

Step 1: Create a function to fetch and display votes #

function fetchVotingResults() {
  $.ajax({
    type: "GET",
    url: "get_votes.php", // You'll need to create this PHP file to return vote counts
    data: { _: new Date().getTime() }, // Add timestamp to avoid browser caching
    success: function(data) {
      // Parse the returned data (assuming it's JSON)
      const voteData = JSON.parse(data);
      
      // Update your UI elements with the new counts
      document.getElementById('color1-votes').textContent = voteData.color1;
      document.getElementById('color2-votes').textContent = voteData.color2;
    },
    error: function() {
      console.log("Failed to fetch vote results—retrying...");
    }
  });
}

Step 2: Trigger polling on page load + after successful insert #

Add this to your script to start polling when the page loads, and also trigger an immediate update right after a user submits a vote:

// Start polling every 3 seconds (adjust based on your needs)
setInterval(fetchVotingResults, 3000);

// Call it once immediately to load initial data
fetchVotingResults();

// Modify your existing insert Ajax to refresh results right after submission
$.ajax({
  type: "POST",
  url: "save.php",
  data: { color1: color1, color2: color2 },
  success: function(data){
    console.log(color2);
    // Refresh results immediately after successful insert
    fetchVotingResults();
  }
});

Step 3: Create the get_votes.php backend #

This file should query your database for the latest vote counts and return them as JSON:

<?php
// Connect to your database (replace with your credentials)
$conn = mysqli_connect("localhost", "username", "password", "voting_db");

// Fetch vote counts for color1 and color2
$result = mysqli_query($conn, "SELECT color1, color2 FROM vote_totals LIMIT 1");
$row = mysqli_fetch_assoc($result);

// Return as JSON
header('Content-Type: application/json');
echo json_encode($row);

mysqli_close($conn);
?>

2. Long Polling (More Efficient Than Simple Polling) #

Long polling reduces unnecessary requests by having the server hold the connection open until new data is available (or a timeout hits). This is great if you want more responsive updates without the overhead of frequent polling.

Client-Side Code #

function longPollVotes() {
  $.ajax({
    type: "GET",
    url: "long_poll_votes.php",
    success: function(data) {
      const voteData = JSON.parse(data);
      // Update UI same as before
      document.getElementById('color1-votes').textContent = voteData.color1;
      document.getElementById('color2-votes').textContent = voteData.color2;
      // Immediately restart the long poll after getting new data
      longPollVotes();
    },
    error: function() {
      // If there's an error, wait 2 seconds then retry
      setTimeout(longPollVotes, 2000);
    },
    timeout: 30000 // Time out after 30 seconds if no new data
  });
}

// Start the long poll when the page loads
longPollVotes();

Server-Side (long_poll_votes.php) #

<?php
set_time_limit(35); // Set timeout slightly longer than client's timeout

$conn = mysqli_connect("localhost", "username", "password", "voting_db");

// Store initial vote counts
$result = mysqli_query($conn, "SELECT color1, color2 FROM vote_totals LIMIT 1");
$initialData = mysqli_fetch_assoc($result);
$initialColor1 = $initialData['color1'];
$initialColor2 = $initialData['color2'];

// Wait for changes (check every 2 seconds)
while(true) {
  $result = mysqli_query($conn, "SELECT color1, color2 FROM vote_totals LIMIT 1");
  $currentData = mysqli_fetch_assoc($result);
  
  // If votes changed, return the new data
  if($currentData['color1'] != $initialColor1 || $currentData['color2'] != $initialColor2) {
    header('Content-Type: application/json');
    echo json_encode($currentData);
    exit();
  }
  
  sleep(2);
  // Check if we're approaching the time limit
  if(time() - $_SERVER['REQUEST_TIME'] > 28) {
    // Return current data even if no changes
    header('Content-Type: application/json');
    echo json_encode($currentData);
    exit();
  }
}
?>

3. WebSockets (Best for High Real-Time Needs) #

If you need instant updates (like live voting for a large audience), WebSockets are the way to go. They create a persistent bidirectional connection, so the server can push new vote data to all clients immediately when a vote is submitted.

Quick Implementation Outline #

• Backend: Use a WebSocket server (you can use Node.js with Socket.io, or PHP libraries like Ratchet).
• Client-Side: Connect to the WebSocket and listen for updates:

const socket = new WebSocket('ws://your-server:8080');

socket.onopen = function() {
  console.log("Connected to WebSocket server");
};

socket.onmessage = function(event) {
  const voteData = JSON.parse(event.data);
  // Update UI with new vote counts
  document.getElementById('color1-votes').textContent = voteData.color1;
  document.getElementById('color2-votes').textContent = voteData.color2;
};

// Modify your insert Ajax to notify the WebSocket server after successful save
$.ajax({
  type: "POST",
  url: "save.php",
  data: { color1: color1, color2: color2 },
  success: function(data){
    console.log(color2);
    // Send a message to the WebSocket server to broadcast the update
    socket.send('vote-updated');
  }
});

Key Tips to Avoid Bottlenecks #

• Cache Busting: Always add a timestamp parameter to your Ajax GET requests (like _: new Date().getTime()) to prevent browsers from caching old results.
• Return Only Needed Data: Instead of sending entire database rows, just send the updated vote counts to reduce bandwidth.
• Error Handling: Add error callbacks to your Ajax requests so your app can retry if a request fails.
• Throttle Polling: For simple polling, don't set the interval too low (2-5 seconds is usually enough for voting apps) to avoid overwhelming your server.

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