MySQL查询返回null问题排查及数据提取至数组方法咨询

先给你梳理几个核心问题，这些都是导致你返回null和功能异常的关键：

1. 缺少错误检测，根本不知道哪里出错 #

mysqli_query执行失败时会返回false而非null，但你没做任何错误检查，连数据库连接是否成功都没验证。先加上这些调试代码，能直接定位问题：

// 先检查数据库连接是否成功
$con= mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_DATA);
if (!$con) {
    die(json_encode(['error' => '数据库连接失败: ' . mysqli_connect_error()]));
}

// 执行查询后检查错误
$result = mysqli_query($con,$query);
if (!$result) {
    die(json_encode(['error' => '查询失败: ' . mysqli_error($con)]));
}

加上这些后，你就能直接看到是SQL语法错误，还是参数格式有问题。

2. 致命问题：$n_quakes只是SQL字符串，从未执行查询 #

你定义了计数的SQL语句，但完全没调用mysqli_query去执行它，最后直接把字符串传给json_encode，这完全不对。要拿到计数结果，得执行查询并取出数值：

// 执行计数查询
$count_result = mysqli_query($con, $n_quakes);
if (!$count_result) {
    die(json_encode(['error' => '计数查询失败: ' . mysqli_error($con)]));
}
// 取出COUNT(*)的结果
$count_row = mysqli_fetch_row($count_result);
$total_quakes = $count_row[0];
mysqli_free_result($count_result);

3. 严重安全问题：直接拼接GET参数导致SQL注入，还可能引发语法错误 #

你直接把$_GET的参数拼进SQL里，这不仅会被SQL注入攻击，还可能因为参数格式（比如带特殊字符、空值）导致SQL语法错误。必须用预处理语句来处理：
举个替换查询部分的例子：

// 以带地理条件的查询为例，用预处理
if(isset($_GET['lat-sup'])) {
    $query = "SELECT * FROM earthquakes 
              WHERE milliseconds BETWEEN ? AND ? 
                AND magnitude BETWEEN ? AND ? 
                AND ipocentro BETWEEN ? AND ? 
                AND latitude BETWEEN ? AND ? 
                AND longitude BETWEEN ? AND ? 
              OFFSET ? LIMIT 400";
    $stmt = mysqli_prepare($con, $query);
    // 绑定参数，"iddddddidd"表示对应参数的类型（i=int, d=float），根据你的字段类型调整
    mysqli_stmt_bind_param($stmt, "iddddddidd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max, $lat_inf, $lat_sup, $lng_sin, $lng_des, $offset);
} else {
    // 不带地理条件的预处理语句
    $query = "SELECT * FROM earthquakes 
              WHERE milliseconds BETWEEN ? AND ? 
                AND magnitude BETWEEN ? AND ? 
                AND ipocentro BETWEEN ? AND ? 
              OFFSET ? LIMIT 400";
    $stmt = mysqli_prepare($con, $query);
    mysqli_stmt_bind_param($stmt, "idddddd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max, $offset);
}
// 执行预处理语句
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

预处理语句会自动处理参数的格式和转义，彻底避免注入和语法错误。

4. mysqli_free_result的使用错误 #

你传了$array_quakes给mysqli_free_result，但这个函数需要的是查询结果集（也就是$result或者$count_result），不是你自己组装的数组。正确的用法是：

mysqli_free_result($result);
// 如果你用了预处理，还要关闭语句
mysqli_stmt_close($stmt);

5. json_encode的参数错误 #

json_encode只接受一个参数，你传了两个（$n_quakes和$array_quakes），这会导致只编码第一个参数。应该把计数和地震数据放到一个数组里再编码：

// 先初始化数组，避免无结果时报错
$array_quakes = [];
while ($row = mysqli_fetch_assoc($result)) {
    $array_quakes[] = $row;
}

// 组合成一个关联数组再编码
echo json_encode([
    'total' => $total_quakes,
    'quakes' => $array_quakes
]);

6. 变量类型的小问题 #

你把所有$_GET参数转成字符串，但如果这些字段是数字类型（比如milliseconds、magnitude），直接转成整数/浮点型更合适，避免预处理时的类型混淆：

$ieri = (int)$_GET['ieri'];
$oggi = (int)$_GET['oggi'];
$magn_min = (float)$_GET['magn-min'];
$magn_max = (float)$_GET['magn-max'];
// 其他参数同理，根据字段类型转换

完整修正后的核心代码片段 #

include_once("../config.php");
$con= mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_DATA);
if (!$con) {
    die(json_encode(['error' => '数据库连接失败: ' . mysqli_connect_error()]));
}

// 转换参数类型
$ieri = (int)$_GET['ieri'];
$oggi = (int)$_GET['oggi'];
$magn_min = (float)$_GET['magn-min'];
$magn_max = (float)$_GET['magn-max'];
$ipo_min = (float)$_GET['ipo-min'];
$ipo_max = (float)$_GET['ipo-max'];
$offset = (int)(($_GET['id-call'] - 1)*400);

// 处理查询
if(isset($_GET['lat-sup'])) {
    $lat_sup = (float)$_GET['lat-sup'];
    $lat_inf = (float)$_GET['lat-inf'];
    $lng_sin = (float)$_GET['lng-sin'];
    $lng_des = (float)$_GET['lng-des'];
    
    // 数据查询
    $query = "SELECT * FROM earthquakes 
              WHERE milliseconds BETWEEN ? AND ? 
                AND magnitude BETWEEN ? AND ? 
                AND ipocentro BETWEEN ? AND ? 
                AND latitude BETWEEN ? AND ? 
                AND longitude BETWEEN ? AND ? 
              OFFSET ? LIMIT 400";
    $stmt = mysqli_prepare($con, $query);
    mysqli_stmt_bind_param($stmt, "iddddddidd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max, $lat_inf, $lat_sup, $lng_sin, $lng_des, $offset);
    
    // 计数查询
    $count_query = "SELECT COUNT(*) FROM earthquakes 
                    WHERE milliseconds BETWEEN ? AND ? 
                      AND magnitude BETWEEN ? AND ? 
                      AND ipocentro BETWEEN ? AND ? 
                      AND latitude BETWEEN ? AND ? 
                      AND longitude BETWEEN ? AND ?";
    $count_stmt = mysqli_prepare($con, $count_query);
    mysqli_stmt_bind_param($count_stmt, "iddddddidd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max, $lat_inf, $lat_sup, $lng_sin, $lng_des);
} else {
    // 数据查询
    $query = "SELECT * FROM earthquakes 
              WHERE milliseconds BETWEEN ? AND ? 
                AND magnitude BETWEEN ? AND ? 
                AND ipocentro BETWEEN ? AND ? 
              OFFSET ? LIMIT 400";
    $stmt = mysqli_prepare($con, $query);
    mysqli_stmt_bind_param($stmt, "idddddd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max, $offset);
    
    // 计数查询
    $count_query = "SELECT COUNT(*) FROM earthquakes 
                    WHERE milliseconds BETWEEN ? AND ? 
                      AND magnitude BETWEEN ? AND ? 
                      AND ipocentro BETWEEN ? AND ?";
    $count_stmt = mysqli_prepare($con, $count_query);
    mysqli_stmt_bind_param($count_stmt, "iddddd", $ieri, $oggi, $magn_min, $magn_max, $ipo_min, $ipo_max);
}

// 执行计数查询
mysqli_stmt_execute($count_stmt);
$count_result = mysqli_stmt_get_result($count_stmt);
$count_row = mysqli_fetch_row($count_result);
$total_quakes = $count_row[0];
mysqli_free_result($count_result);
mysqli_stmt_close($count_stmt);

// 执行数据查询
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$array_quakes = [];
while ($row = mysqli_fetch_assoc($result)) {
    $array_quakes[] = $row;
}
mysqli_free_result($result);
mysqli_stmt_close($stmt);

// 返回JSON
echo json_encode([
    'total' => $total_quakes,
    'quakes' => $array_quakes
]);

mysqli_close($con);

先按照上面的步骤加错误检测，应该能马上找到最初的问题（比如SQL语法错了），然后逐步替换成预处理语句，就能彻底解决null的问题，同时让代码更安全。

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