PDO 查询不起作用

Question

我花了几个小时查看相同的代码，试图找出为什么我的查询不起作用。我在下面列出的两个是不起作用的两个。

$getRequestIdQuery = "SELECT request_id
    FROM request_table
    WHERE request_key = '$requestKey'
    AND sort_order = $so";

$getRequestId = $pdo->prepare($getRequestIdQuery);
$getRequestId->execute();
foreach($getRequestId as $idRow)
{
    $requestId = $idRow['request_id'];
}

// This will update the ready status of the request id returned above
$updateReadyStatusQuery = "UPDATE request_table
    SET request_ready = 1
    WHERE request_id = $requestId";
$updateReadyStatus = $pdo->prepare($updateReadyStatusQuery);
$updateReadyStatus->execute();

只要文件副本返回 true，上面的代码就会运行。我已经确定它正在运行，因为上面省略了每次测试运行期间都会显示的错误日志。我还确信有问题的查询有效，因为我已在 phpmyadmin 中成功运行查询（如错误日志中显示的那样）。以下是仅在此之上几行的代码片段，该代码片段可以正确运行：

$checkForComposedQuery = "SELECT *
    FROM composed_files
    WHERE file_source_id = '$fsi'
    AND file_number = '$fn'";

$checkForComposed = $pdo->prepare($checkForComposedQuery);
$checkForComposed->execute();

有关可能导致此功能不起作用的任何提示吗？如果有帮助的话，上面的两个片段都发生在 foreach 循环中。

非常感谢。

更新：

以下内容合并了包含 Charles 在下面添加的建议的代码：

$gotCopied = copy($sourceHymnFile, $destHymnFile);

if ($gotCopied == true) {
    error_log("The file has been successfully copied.");

    $idRow;
    $getRequestIdQuery = "SELECT request_id
        FROM request_table
        WHERE request_key = ?
        AND sort_order = ?";
    $getRequestId = $pdo->prepare($getRequestIdQuery);
    $getRequestId->execute(array($requestKey, $so));
    error_log("this is the value of request key : ".$requestKey);
    // Displays correct $requestKey value
    error_log("This is the value of sort order : ".$so);
    // Displays correct $so value
    $idRow = $getRequestId->fetch(PDO::FETCH_ASSOC);
    $requestId = $idRow['request_id'];
    error_log("This is the value of the request id : ".$requestId);
    // No output in error log for $requestId above

    // This will update the ready status of the request id returned above
    $updateReadyStatusQuery = "UPDATE request_table
        SET ready = 1
        WHERE request_id = ?";
    error_log("This updates the status of the song request if the song is played : ".$updateReadyStatusQuery);
    $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery);
    $updateReadyStatus->execute(array($requestId));
}

对于输入的常量，以下内容正确运行：

    if ($gotCopied == true) {
    error_log("The file has been successfully copied.");

    $idRow;
    $getRequestIdQuery = "SELECT request_id
        FROM request_table
        WHERE request_key = ?
        AND sort_order = ?";
    $getRequestId = $pdo->prepare($getRequestIdQuery);
    $getRequestId->execute(array(5, 2));
    error_log("this is the value of request key : ".$requestKey);
    error_log("This is the value of sort order : ".$so);
    $idRow = $getRequestId->fetch(PDO::FETCH_ASSOC);
    $requestId = $idRow['request_id'];
    error_log("This is the value of the request id : ".$requestId);
    // No output in error log for $requestId above

    // This will update the ready status of the request id returned above
    $updateReadyStatusQuery = "UPDATE request_table
        SET ready = 1
        WHERE request_id = ?";
    error_log("This updates the status of the song request if the song is played : ".$updateReadyStatusQuery);
    $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery);
    // This execute works correctly if a value is set for $requestId
    $updateReadyStatus->execute(array($requestId));
}

Answer 1

你这里有两个问题。

首先，占位符和绑定。您的代码很容易受到 SQL 注入攻击。 PDO 包含一个可以帮助减轻这种威胁的工具。

$getRequestIdQuery = "SELECT request_id
    FROM request_table
    WHERE request_key = ? -- new!
    AND sort_order = ?";

$getRequestId = $pdo->prepare($getRequestIdQuery);
$getRequestId->execute(array($requestKey, $so));

查询中的 ? 是占位符。传递给 execute 的数组提供了任何占位符的替换列表。它们会根据需要自动转义和引用。

其次，您检索结果不正确。您需要调用 fetch 方法 (或语句句柄上的 fetchAll 方法） 。例如：

$idRow = $getRequestId->fetch(PDO::FETCH_ASSOC);
$requestId = $idRow['request_id'];

请注意，这里没有循环。您之前的循环本来应该有多个结果，但它在每个循环中覆盖了相同的变量。看起来您只期待一个结果，因此您只需要担心一个结果。

我们还应该更新您的其他查询以使用占位符。

$updateReadyStatusQuery = "UPDATE request_table
    SET request_ready = 1
    WHERE request_id = ?";
$updateReadyStatus = $pdo->prepare($updateReadyStatusQuery);
$updateReadyStatus->execute(array($requestId));

...还有你的第三个...

$checkForComposedQuery = "SELECT *
    FROM composed_files
    WHERE file_source_id = ?
    AND file_number = ?";

$checkForComposed = $pdo->prepare($checkForComposedQuery);
$checkForComposed->execute(array($fsi, $fn));