请求修复MySQL按天汇总访问量的SQL查询及PHP脚本

Hey there! Let's get your visit data aggregation and PHP script sorted out. The main issues here are using deprecated mysql_* functions (they've been removed in PHP 7+) and crafting the right SQL query to group visits by day.

Step 1: Replace Deprecated MySQL Functions #

First off, the old mysql_connect and related functions are no longer supported in modern PHP versions. We'll switch to mysqli_* (MySQL Improved) which is secure, maintained, and works with all recent PHP releases.

Step 2: Correct SQL Query for Daily Aggregation #

To combine all visits from the same day into a total count, we'll use the DATE() function to extract just the date part from your datetime field, then group by that date and count the records.

Assuming your table is named visits and your datetime field is visit_time (adjust these to match your actual database schema), here's the query:

SELECT DATE(visit_time) AS visit_date, COUNT(*) AS total_visits
FROM visits
GROUP BY DATE(visit_time)
ORDER BY visit_date ASC;

If each record has a specific visit_count field (instead of 1 visit per record), replace COUNT(*) with SUM(visit_count) to get the total for each day.

Step 3: Fixed PHP Script #

Here's the full working script with mysqli, proper error handling, and data formatted ready for chart display:

<?php
$host = 'your_host'; // e.g., 'localhost'
$username = 'your_username';
$password = 'your_password';
$bdd_name = 'your_database';

// Connect to database (we include the database name directly in the connect call)
$conn = mysqli_connect($host, $username, $password, $bdd_name);

// Check if connection succeeded
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Define and run the aggregation query
$sql = "SELECT DATE(visit_time) AS visit_date, COUNT(*) AS total_visits FROM visits GROUP BY DATE(visit_time) ORDER BY visit_date ASC";
$result = mysqli_query($conn, $sql);

// Catch any query errors
if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

// Format data into an array perfect for charts
$chartData = [];
while ($row = mysqli_fetch_assoc($result)) {
    $chartData[] = [
        'date' => $row['visit_date'],
        'total_visits' => (int)$row['total_visits']
    ];
}

// Clean up the database connection
mysqli_close($conn);

// Example: Output the data (you can pass this to Chart.js or other libraries via json_encode($chartData))
print_r($chartData);
?>

Quick Tips: #

• Swap out your_host, your_username, etc., with your actual database credentials.
• Update visits (table name) and visit_time (datetime field) to match your database's structure.
• The $chartData array is structured to be easily converted to JSON (using json_encode()) for frontend chart libraries like Chart.js or Google Charts.
• If you ever add user input to your queries later, use prepared statements to prevent SQL injection (though it's not needed for this aggregation query).

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