php - 我的mysql select语句(php，mysql)中的语法有什么问题

我有一张表，我试图提取名为order_num的列的最大值，该列具有33个整数1到33的整数。在此情况下，我希望值“ 33”为其最大数。

$ userid是从一个行表派生的整数，该表具有我要检索的字段ID

//get the currentUser ID so we know whos deck to ammend
$userIDSQL = "SELECT id FROM currentUser";
$userIdResult = mysqli_query($db, $userIDSQL) or die("SQL Error on fetching user ID: " . mysqli_error($db));

$result_array = array();
while ($row = mysqli_fetch_assoc($userIdResult)) {
    $result_array[] = $row['id'];
}

//the actual user id
$userId = $row['id'];
echo "user id is " . $userId;

在$ userId上执行print_r显示数组为空，这就是下面的代码不起作用的原因.. :(

...

$reOrderDeckSQL = "SELECT MAX(order_num) AS order_num FROM decks WHERE id='$userId'";
$reOrderDeckResult = mysqli_query($db, $reOrderDeckSQL) or die("SQL Error on reOrder: " . mysqli_error($db));

        $result_array = array();
        while ($row = mysqli_fetch_array($reOrderDeckResult)) {
            $result_array[] = $row['MAX(order_num)'];
            echo "the result is" . $result_array['order_num'];
            echo "the result is" . $row['order_num'];
            echo "the result is" . $result_array['MAX(order_num)']; //tried different methods to get the output.
        }

我得到的输出是

the result is  the result is  the result is

有谁知道我为什么不能从表中得到结果？

最佳答案

由于您使用了数组，因此第一个获取userId的代码无法正常工作，请更改为：

$userId = 0;
while ($row = mysqli_fetch_assoc($userIdResult)) {
    $userId = $row['id'];
}

如下所示，如果只期望一行，则删除while循环，仅调用fetch_assoc一次。

假设您只需要单行，则不需要while循环：

$reOrderDeckSQL = "SELECT MAX(order_num) AS order_num FROM decks WHERE id='$userId' LIMIT 1";
$reOrderDeckResult = mysqli_query($db, $reOrderDeckSQL) or die("SQL Error on reOrder: " . mysqli_error($db));

if($reOrderDeckResult && mysqli_num_rows($reOrderDeckResult) == 1)
{
    $row = mysqli_fetch_assoc($reOrderDeckResult);
    echo 'result: ' . $row['order_num'];
}
else
{
    echo 'No rows found!!';
}

我还向查询添加了LIMIT 1，并检查是否有任何行。

关于php - 我的mysql select语句(php，mysql)中的语法有什么问题，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17110127/