构建消息管理系统与排行榜

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

score INT DEFAULT 0

);

CREATE TABLE Messages (

message_id INT AUTO_INCREMENT PRIMARY KEY,

sender_id INT,

receiver_id INT,

content TEXT,

timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (sender_id) REFERENCES Users(user_id),

FOREIGN KEY (receiver_id) REFERENCES Users(user_id)

);

数据中台建设

function sendMessage($sender_id, $receiver_id, $content) {

global $db;

$stmt = $db->prepare("INSERT INTO Messages (sender_id, receiver_id, content) VALUES (?, ?, ?)");

$stmt->execute([$sender_id, $receiver_id, $content]);

}

// 更新用户分数

function updateScore($user_id, $points) {

global $db;

$stmt = $db->prepare("UPDATE Users SET score = score + ? WHERE user_id = ?");

$stmt->execute([$points, $user_id]);

}

function getLeaderboard() {

global $db;

$stmt = $db->query("SELECT * FROM Users ORDER BY score DESC LIMIT 10");

return $stmt->fetchAll(PDO::FETCH_ASSOC);

}