如何在phpMyAdmin同步用户表积分或在PHP数据网格展示总积分？

Hey there! Let's walk through two reliable solutions to meet your needs—either syncing the total task points to the users table's points column for persistent storage, or calculating and displaying the total directly in your PHP datagrid. Here's how each works:

This approach is great if you need the total points stored permanently (e.g., for frequent, fast access). You can set this up using MySQL triggers (directly in phpMyAdmin) or a one-time PHP script to initialize the data.

方法1：用MySQL触发器自动同步 #

Triggers will update the users.points value automatically whenever a task's PointsAwarded is added, updated, or deleted:

1. Open phpMyAdmin, select your database, and navigate to the tasks table.
2. Switch to the Triggers tab, then click Add Trigger.
3. Configure the trigger details:
   • Trigger name: update_user_points_after_task_change
   • Trigger timing: AFTER
   • Trigger event: Select INSERT, UPDATE, and DELETE (to cover all changes)
   • Table: tasks
4. Paste this SQL into the "Trigger body" field (adjust field names if your schema differs):

DELIMITER //
CREATE TRIGGER update_user_points_after_task_change
AFTER INSERT, UPDATE, DELETE ON tasks
FOR EACH ROW
BEGIN
    -- Update the user's total points by summing their tasks
    UPDATE users
    SET points = (
        SELECT COALESCE(SUM(PointsAwarded), 0)
        FROM tasks
        WHERE UserID = COALESCE(NEW.UserID, OLD.UserID)
    )
    WHERE id = COALESCE(NEW.UserID, OLD.UserID);
END //
DELIMITER ;

Note: COALESCE(NEW.UserID, OLD.UserID) handles both insert/update (uses NEW) and delete (uses OLD) events. Make sure UserID is the field in tasks that links to users.id.

方法2：一次性PHP脚本初始化数据 #

If you already have existing tasks data, run this script once to populate the users.points column before enabling triggers:

// Replace with your database credentials
$host = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database';

// Connect to database
$conn = new mysqli($host, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Calculate and update total points for all users
$sql = "
    UPDATE users u
    SET points = (
        SELECT COALESCE(SUM(t.PointsAwarded), 0)
        FROM tasks t
        WHERE t.UserID = u.id
    )
";

if ($conn->query($sql) === TRUE) {
    echo "Successfully synced initial points data!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();

This method avoids storing redundant data by calculating the total points on-the-fly when fetching user data. Perfect if you want to ensure the total is always up-to-date without extra maintenance.

Modify your existing datagrid SQL query to include a calculated total points column:

// Updated datagrid with real-time total points calculation
$grid = new C_DataGrid(
    "SELECT 
        u.id, 
        u.FirstName, 
        u.LastName, 
        COALESCE(SUM(t.PointsAwarded), 0) AS TotalPoints
     FROM users u
     LEFT JOIN tasks t ON u.id = t.UserID
     GROUP BY u.id, u.FirstName, u.LastName
     ORDER BY TotalPoints DESC", // Optional: Sort by total points
    "id",
    "Ranking"
);

• LEFT JOIN ensures users with no tasks still appear in the grid.
• COALESCE(..., 0) converts NULL (for users with no tasks) to 0 for cleaner display.
• Add ORDER BY TotalPoints DESC if you want the ranking to show highest points first.

Performance Tip #

If your tasks table has a lot of rows, add an index to speed up the sum calculation:

CREATE INDEX idx_tasks_userid_points ON tasks(UserID, PointsAwarded);

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