PHP获取MySQL参与者排名时未定义数组键问题的解决

问题背景 #

我正在编写一个PHP定时任务（cron job）脚本，需求是：读取Java写入到performancerequests.txt中的participantID，然后从MySQL数据库中获取该参与者的积分（Points）、剩余商品数量（Quantity）以及基于积分的排名（Rank），最终将结果写入performance.txt文件。

我的代码如下：

<?php

//connect to mysql database procedurally
$conn = mysqli_connect("localhost","root","","anka");

if(!$conn){
    echo "Connection error" . mysqli_connect_error("localhost","root","","anka");
}

$participantid = 0;
$participantid = fopen('performancerequests.txt',"r") or die("Request not taken.");
$content = fread($participantid, filesize("performancerequests.txt"));
file_put_contents("performancerequests.txt","");


//should return rank, points, products left(quantity) 
$sql1 = "SELECT points from participants where id='$content'";
$sql2 = "SELECT quantity from products where participant_id='$content'";
$sql3 = "SELECT points, ROW_NUMBER() OVER( ORDER BY points ) RowNumber from participants where id='$content'";

$result1 = mysqli_query($conn, $sql1);
$result2 = mysqli_query($conn, $sql2);
$result3 = mysqli_query($conn, $sql3);

while ($row1 = $result1->fetch_assoc()) {
    $points = $row1['points'];
}

while ($row2 = $result2->fetch_assoc()) {
    $quantity = $row2['quantity'];
}

while ($row3 = $result3->fetch_assoc()) {
    $rank = $row3['rank'];
}


$handle = fopen("performance.txt", "w") or die("File does not exist.");
if(fwrite($handle,$content." "."Points: ".$points." "."Quantity: ".$quantity." "."Rank: ".$rank) == false ){
    echo "Error Writing.";
}

?>

涉及的MySQL表结构：

CREATE TABLE participants (
    id bigint(20),
    name varchar(255),
    password varchar(255),
    product varchar(255),
    DOB date,
    points int(11),    
    PRIMARY KEY(id)
);

CREATE TABLE products (
    id bigint(20),
    name varchar(255),
    quantity varchar(255),
    rate varchar(255),
    description varchar(255),
    FOREIGN KEY(participant_id) REFERENCES participants(id),    
    PRIMARY KEY(id)
);

测试运行时，出现“未定义数组键”警告，且生成的文本文件中排名字段为空。

问题分析与修复方案 #

1. 排名字段为空的直接原因

你的sql3查询中，给排名列起的别名是RowNumber，但PHP代码中尝试读取$row3['rank']，数组键名不匹配，导致无法获取排名值。同时常规排名逻辑是积分越高排名越靠前，需要给ORDER BY points加上DESC：

SELECT points, ROW_NUMBER() OVER(ORDER BY points DESC) AS rank FROM participants WHERE id='$content'

2. "未定义数组键"警告的修复

当数据库查询无结果（比如participantID不存在）时，对应的while循环不会执行，$points、$quantity、$rank变量未定义，触发警告。需要提前初始化变量，并处理无结果的情况：

// 初始化变量，避免未定义警告
$points = 'N/A';
$quantity = 'N/A';
$rank = 'N/A';

// 积分查询（单结果无需while循环）
if ($result1 && mysqli_num_rows($result1) > 0) {
    $row1 = mysqli_fetch_assoc($result1);
    $points = $row1['points'];
}

// 商品数量查询
if ($result2 && mysqli_num_rows($result2) > 0) {
    $row2 = mysqli_fetch_assoc($result2);
    $quantity = $row2['quantity'];
}

// 排名查询
if ($result3 && mysqli_num_rows($result3) > 0) {
    $row3 = mysqli_fetch_assoc($result3);
    $rank = $row3['rank'];
}

3. 修复严重的SQL注入风险

直接拼接$content到SQL语句中存在极高注入风险，必须使用预处理语句，示例：

// 预处理积分查询
$sql1 = "SELECT points FROM participants WHERE id = ?";
$stmt1 = mysqli_prepare($conn, $sql1);
mysqli_stmt_bind_param($stmt1, "i", $content); // "i"对应整数类型，匹配participants.id的bigint类型
mysqli_stmt_execute($stmt1);
$result1 = mysqli_stmt_get_result($stmt1);

同理对sql2和sql3做相同处理。

4. 文件读取的潜在问题修复

读取到的participantID可能包含换行或空格，导致查询失败，需要修剪无效字符：

$content = trim(fread($participantid, filesize("performancerequests.txt")));

5. 表结构错误修复

products表中外键依赖participant_id字段，但建表语句未定义该字段，修正后：

CREATE TABLE products (
    id bigint(20),
    name varchar(255),
    quantity varchar(255),
    rate varchar(255),
    description varchar(255),
    participant_id bigint(20), -- 新增字段
    FOREIGN KEY(participant_id) REFERENCES participants(id),    
    PRIMARY KEY(id)
);

修复后的完整代码 #

<?php
// 连接MySQL数据库
$conn = mysqli_connect("localhost", "root", "", "anka");

if(!$conn){
    die("Connection error: " . mysqli_connect_error());
}

// 读取并清理participantID
$fileHandle = fopen('performancerequests.txt', "r") or die("Request not taken.");
$participantId = trim(fread($fileHandle, filesize("performancerequests.txt")));
fclose($fileHandle);
file_put_contents("performancerequests.txt", "");

// 初始化变量
$points = 'N/A';
$quantity = 'N/A';
$rank = 'N/A';

// 1. 查询积分（预处理）
$sqlPoints = "SELECT points FROM participants WHERE id = ?";
$stmtPoints = mysqli_prepare($conn, $sqlPoints);
mysqli_stmt_bind_param($stmtPoints, "i", $participantId);
mysqli_stmt_execute($stmtPoints);
$resultPoints = mysqli_stmt_get_result($stmtPoints);
if ($resultPoints && mysqli_num_rows($resultPoints) > 0) {
    $row = mysqli_fetch_assoc($resultPoints);
    $points = $row['points'];
}

// 2. 查询商品剩余数量（预处理）
$sqlQuantity = "SELECT quantity FROM products WHERE participant_id = ?";
$stmtQuantity = mysqli_prepare($conn, $sqlQuantity);
mysqli_stmt_bind_param($stmtQuantity, "i", $participantId);
mysqli_stmt_execute($stmtQuantity);
$resultQuantity = mysqli_stmt_get_result($stmtQuantity);
if ($resultQuantity && mysqli_num_rows($resultQuantity) > 0) {
    $row = mysqli_fetch_assoc($resultQuantity);
    $quantity = $row['quantity'];
}

// 3. 查询排名（预处理，积分降序）
$sqlRank = "SELECT ROW_NUMBER() OVER(ORDER BY points DESC) AS rank FROM participants WHERE id = ?";
$stmtRank = mysqli_prepare($conn, $sqlRank);
mysqli_stmt_bind_param($stmtRank, "i", $participantId);
mysqli_stmt_execute($stmtRank);
$resultRank = mysqli_stmt_get_result($stmtRank);
if ($resultRank && mysqli_num_rows($resultRank) > 0) {
    $row = mysqli_fetch_assoc($resultRank);
    $rank = $row['rank'];
}

// 写入结果文件
$output = "$participantId Points: $points Quantity: $quantity Rank: $rank";
$handle = fopen("performance.txt", "w") or die("File does not exist.");
if(fwrite($handle, $output) === false ){
    echo "Error Writing.";
}
fclose($handle);

// 关闭数据库连接
mysqli_close($conn);
?>

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