如何用PHP和MySQL实现按日统计的文章浏览量计数器

Hey there! The problem you're facing is that your days_views table lacks a unique constraint to prevent duplicate entries for the same article on the same day. Right now, only the auto-incrementing day_views_id is set as the primary key—so every time your code runs, it'll insert a new row instead of updating the existing one for that day. Here's how to fix this properly:

Step 1: Add a Unique Composite Index to Your Table #

You need to create a unique constraint that combines article_id and day. This tells MySQL that no two rows can have the same article_id and day value pair. Run this SQL query to modify your table:

ALTER TABLE `days_views`
ADD UNIQUE KEY `unique_article_day` (`article_id`, `day`);

This is the critical missing piece—without this, the ON DUPLICATE KEY UPDATE clause in your INSERT statement has no way to detect duplicate entries.

Step 2: Keep (and Secure) Your INSERT Statement #

Your existing INSERT logic is correct once the unique index is in place, but you should fix the SQL injection vulnerability in your PHP code (directly using $_GET['p_id'] in a query is risky). Here's the improved PHP code using prepared statements:

$article_id = $_GET['p_id'];

// Validate the article ID first (make sure it's an integer)
if (!is_numeric($article_id)) {
    // Handle invalid ID (e.g., redirect to error page)
    header("Location: error.php");
    exit;
}

// Prepare the daily view count query
$view_query = "INSERT INTO days_views (`article_id`, `day_views_count`, `day`) 
               VALUES (?, 1, CURDATE()) 
               ON DUPLICATE KEY UPDATE `day_views_count` = `day_views_count` + 1";

// Use prepared statements to prevent SQL injection
$stmt = mysqli_prepare($connection, $view_query);
mysqli_stmt_bind_param($stmt, "i", $article_id);
mysqli_stmt_execute($stmt);

// Optional: Check if the query ran successfully
if (mysqli_stmt_affected_rows($stmt) === -1) {
    // Handle error (log it, show a message, etc.)
    error_log("Failed to update daily views: " . mysqli_error($connection));
}

mysqli_stmt_close($stmt);

What This Does: #

• When a user views an article, it first checks if there's already a row for that article_id and today's date (CURDATE()).
• If the row exists: It increments day_views_count by 1.
• If the row doesn't exist: It creates a new row with day_views_count set to 1.

Step 3: Verify It Works #

To test, you can run these steps:

• Visit an article once—check the days_views table, you should see one row with day_views_count = 1.
• Visit the same article again on the same day—refresh the table, and the day_views_count should now be 2 (no new row added).
• Wait until the next day (or manually change CURDATE() to a different date for testing) and visit the article—you'll see a new row for the new date.

Bonus: Update Your Total Article Views (Optional) #

If you still want to keep the total view count in your articles table, you can keep your original UPDATE query alongside the daily tracking code—just make sure to run both inside a transaction if you want to ensure consistency:

// Start a transaction
mysqli_begin_transaction($connection);

try {
    // Update total views
    $total_query = "UPDATE articles SET article_views_count = article_views_count + 1 WHERE article_id = ?";
    $total_stmt = mysqli_prepare($connection, $total_query);
    mysqli_stmt_bind_param($total_stmt, "i", $article_id);
    mysqli_stmt_execute($total_stmt);

    // Update daily views (the code from Step 2)
    $daily_query = "INSERT INTO days_views (`article_id`, `day_views_count`, `day`) 
                   VALUES (?, 1, CURDATE()) 
                   ON DUPLICATE KEY UPDATE `day_views_count` = `day_views_count` + 1";
    $daily_stmt = mysqli_prepare($connection, $daily_query);
    mysqli_stmt_bind_param($daily_stmt, "i", $article_id);
    mysqli_stmt_execute($daily_stmt);

    // Commit the transaction
    mysqli_commit($connection);
} catch (Exception $e) {
    // Rollback on error
    mysqli_rollback($connection);
    error_log("View count update failed: " . $e->getMessage());
}

// Close statements
mysqli_stmt_close($total_stmt);
mysqli_stmt_close($daily_stmt);

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