为什么我的总和查询打破了我的循环? [英] Why is my Sum query breaking my Loop?

问题描述

我有一个循环遍历表并显示客户ID的循环. 它工作正常.在循环的顶部，它将变量设置为等于客户ID，然后将其回显到屏幕.如果表中有七个项目，则会显示七个OrderId.

I have a loop that iterates through a table and displays the customer ID. It works fine. At the top of the loop it sets a variable to be equal to the customer ID and then echos it to the screen. If there are seven items in the table it displays seven OrderIds.

但是，如果我随后尝试使用该客户ID来执行sum语句，则该程序将首次显示结果，但在第一次迭代后立即退出而不会出现错误，而不是遍历，就像记录集是被摧毁.

However, if I then try to use that Customer ID to execute a sum statement the program displays the results the first time through but then quits without an error after the first iteration, instead of looping through, almost as if the recordset was being destroyed.

我完全不知道为什么.代码如下.预先非常感谢:

I am at a complete loss to understand why. The code is below. Many thanks in advance:

include ("../Connections/PDOConnection.php")

$Query= "
SELECT distinct
    OrderID,
    CustID,
    Name
FROM
    dbo.JNO_OrderHeader
WHERE
    CustID is Not Null 
";

$stmt = $pdo->prepare($Query);

if ($stmt->execute()) {

    while ($Order=$stmt->fetch(PDO::FETCH_ASSOC)){

        $OrderID = $Order['OrderID'];
        echo "<br>OrderID: " . $OrderID;


        $Query1 = "SELECT SUM(dbo.JNO_OrderDetail.ExtendedPrice) AS TotalOrderPrice 
                   FROM dbo.JNO_OrderDetail 
                   WHERE dbo.JNO_OrderDetail.OrderId = :OrderID";

        $stmt1 = $pdo->prepare($Query1);
        $stmt1->bindValue("OrderID", $OrderID);
        $stmt1->execute();
        $OrderTotal=$stmt1->fetch(PDO::FETCH_ASSOC);    

        echo "<br>Totals for order number ". $OrderID .": " . $OrderTotal['TotalOrderPrice'];
    }
}

?>

推荐答案

人们所说的是什么错误?

As people are saying, what is the error?

乍一看可能是$pdo的重用，就像尝试循环中的其他变量一样尝试$pdo1.

At a glance it might be the reuse of $pdo, try $pdo1 like you did on the other variables in the loop.

无论如何，像这样循环和发出多个SQL语句并不是最佳选择.考虑加入并做这样的事情吗?

Anyway, the looping and issuing multiple SQL statements like that is not optimal. Consider a join and doing something like this?

SELECT
    oh.OrderID,
    oh.CustID,
    oh.Name
    SUM(od.ExtendedPrice) AS TotalOrderPrice
FROM
    dbo.JNO_OrderHeader oh
LEFT JOIN
    dbo.JNO_OrderDetail od ON od.OrderId = oh.OrderID
WHERE
    oh.CustID IS NOT NULL
GROUP BY 
    oh.OrderID,
    oh.CustID,
    oh.Name 

这篇关于为什么我的总和查询打破了我的循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！