如何通过jQuery+Ajax向数据库传值并统计下载点击量

Great start with your code—you’re already on the right path to tracking link clicks and storing the ID. Let’s refine this to make sure the AJAX request completes reliably before the download starts, and set up the backend to properly handle the database logic.

Updated Frontend Code #

The main issue with your original code is that the browser might navigate to the PDF before the AJAX request finishes. Here’s a fixed version that ensures tracking happens first:

<html>
<body>
  <a id="20_2018" rel="link1" href="folder.pdf">Download</a>
  
  <!-- Load jQuery (use a local copy if preferred) -->
  <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>
  <script type="text/javascript">
    $(document).ready(function(){
      $('a[rel="link1"]').click(function(e) {
        // Stop the link from opening immediately
        e.preventDefault();
        const clickedLink = this;
        const linkId = $(this).attr("id");
        
        $.ajax({
          type: 'POST',
          url: 'send.php',
          data: { id: linkId }, // Cleaner object notation instead of string concatenation
          success: function(response) {
            console.log("Click tracked:", response);
            // Proceed with download once tracking is done
            window.location.href = clickedLink.href;
          },
          error: function(xhr, status, error) {
            console.error("Tracking failed:", error);
            // Still let the user download even if tracking breaks
            window.location.href = clickedLink.href;
          }
        });
      });
    });
  </script>
</body>
</html>

Backend (send.php) #

Now we need to handle the incoming ID, either creating a new record or incrementing the click count for an existing one. We’ll use prepared statements to avoid SQL injection:

<?php
// Database credentials (replace with your own)
$servername = "localhost";
$username = "your_db_user";
$password = "your_db_password";
$dbname = "your_db_name";

// Connect to database
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Check if ID was sent
if (isset($_POST['id'])) {
  $linkId = $_POST['id'];
  
  // First, check if this ID already exists
  $checkQuery = $conn->prepare("SELECT click_count FROM link_clicks WHERE link_id = ?");
  $checkQuery->bind_param("s", $linkId);
  $checkQuery->execute();
  $result = $checkQuery->get_result();
  
  if ($result->num_rows > 0) {
    // Increment existing count
    $updateQuery = $conn->prepare("UPDATE link_clicks SET click_count = click_count + 1 WHERE link_id = ?");
    $updateQuery->bind_param("s", $linkId);
    $updateQuery->execute();
    echo "Count updated for ID: $linkId";
    $updateQuery->close();
  } else {
    // Insert new record with count 1
    $insertQuery = $conn->prepare("INSERT INTO link_clicks (link_id, click_count) VALUES (?, 1)");
    $insertQuery->bind_param("s", $linkId);
    $insertQuery->execute();
    echo "New record created for ID: $linkId";
    $insertQuery->close();
  }
  
  $checkQuery->close();
} else {
  echo "No ID received";
}

$conn->close();
?>

Create the Database Table #

First, run this SQL query to create the table that will store your link data:

CREATE TABLE link_clicks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  link_id VARCHAR(255) NOT NULL UNIQUE,
  click_count INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Key Notes #

• Security: Prepared statements prevent SQL injection, which is critical if your link IDs might contain special characters.
• Reliability: The e.preventDefault() and subsequent redirect ensures the AJAX request finishes before the download starts.
• Fallback: Even if tracking fails, the user still gets their download—no frustrating broken links.

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