在mySQL查询中使用MAX()会给出有问题的结果 - Using MAX() in mySQL query is giving problematic results

Using MAX() in mySQL query is giving problematic results

本文关键字：有问题结果查询 mySQL MAX | 更新日期: 2023-09-26

我正在尝试进行多个查询，以便通过用户名在数据库中找到最新的条目。
下面是我的代码:

<?php  
    require_once("../includes/db_connection.php");  
    $userID = $_POST["userID"];
    $returnString = array();
    // Query the max id value of a given key_id (find the most recent upload)
    $query = "SELECT MAX(id) FROM photos WHERE key_id = {$userID}";
    $result = mysqli_query($connection, $query);
    //additional while loop could go here
    //now get the url where from the max id value that we just queried
    $query = "SELECT url FROM photos WHERE id = {$urlID}";
    $result = mysqli_query($connection, $query);
    $returnString['url'] = $urlID;
    mysqli_free_result($result);        
    echo json_encode($returnString);

?>

我认为问题出在第一个问题。当我返回结果时，我得到:

{"maxID": "current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

当我创建一个while循环来捕获数组时(为什么我需要这样做，因为它只会返回1个值):

while($row = mysqli_fetch_assoc($result)) {$returnString[] = $row;}

然后我得到这个奇怪的结果:

[{"MAX(id)":"30"}]

30是正确的值，但是我不知道如何在我的下一个mySQL查询中使用该结果。

* * * * * * * * * * 更新 * * * * * * * * * * * * *

查询:

SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID});

工作完美时，使查询从mySQL内，但不从我的php脚本工作。它返回这个奇怪的字符串:

{"url":{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}}

下面是更新后的脚本:

require_once("../includes/db_connection.php");  
$userID = $_POST["userID"];
$returnString = array();
$query = "SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";
$result = mysqli_query($connection, $query);
mysqli_free_result($result);    
$returnString['url'] = $result; 
echo json_encode($returnString);

除非我在模式中遗漏了代码和注释中不明显的内容，否则您可以通过组合SQL命令来节省往返时间。

$query = "SELECT id AS urlID, url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";

然后像平常一样处理结果。

更新答案:

$query = "SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_array($result):
$url = $row['url'];
echo json_encode($url);
mysqli_free_result($result);

我认为真正的问题是在循环和使用数组的结果。

您应该更改为单个查询，如:

SELECT url, MAX(id) as id FROM photos WHERE key_id = {$userID}

MAX(id) as id返回聚合列名称为id

如果您只期望一行，则不需要使用while循环。只需将while更改为if以测试是否返回任何行，并将值分配给单个变量:

$id = {$row['id']};
$url = {$row['url']};

"funky"的结果是由于试图打印不需要的数组，并且已经存储了列名和值。