php - 在 mySQL 查询中使用 MAX() 会产生有问题的结果

Question

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

<?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 查询中使用该结果。

**********更新************< em>*

查询:

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);

Answer 1

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

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

然后像往常一样处理您的结果。