将SQL变量从INNER JOIN SQL查询传递到PHP脚本

一尘不染

将SQL变量从INNER JOIN SQL查询传递到PHP脚本

sql

我有以下INNER JOIN查询：

SELECT  b.*, c.date2
FROM    (
            SELECT a.work, a.amount, 
                   COUNT(*) totalCount, 
                   SUM(Amount) totalAmount
            FROM work_times a WHERE Organisation=?
            GROUP BY a.work, a.amount
        ) b
        INNER JOIN
        (
            SELECT a.work, a.amount, DATE_FORMAT(Date,'%D %M %Y') date2,
                    date
            FROM work_times a
        ) c ON b.work = c.work and b.amount=c.amount
ORDER BY b.work, b.totalCount, c.date

您可以在此处的SQL小提琴上的示例表上看到它的运行情况。

我的目标是返回以下内容：

5 consultancy sessions @ 拢50 each: 拢250

1st February 2013
8th February 2013
15th February 2013
22nd February 2013
1st March 2013

3 therapy sessions @ 拢40 each: 拢120

2nd February 2013
9th February 2013
16th February 2013

2 therapy sessions @ 拢20 each: 拢40

3rd February 2013
10th February 2013

但是使用以下PHP：

$stmt->bind_param("s", $name1);
$stmt->execute();
$stmt->store_result();  
$stmt->bind_result($work,$amount,$count,$total_group,$date);

while ($stmt->fetch()) {

        if ($count>1) {
           echo $count." ".$work."s @ &pound;".$amount." each<br><br>";
           echo date("jS F Y",strtotime($date))."<br><br>";
           $total_work=$total_work+$total_group;
        }
        else {
           echo $count." ".$work." @ &pound;".$amount."<br><br>";
           echo date("jS F Y",strtotime($date))."<br><br>";
           $total_work=$total_work+$total_group;
        }

        }

我得到每一行一行，而不是分组，即：

5 Consultancy Sessions @ 拢50.00

1st February 2013

5 Consultancy Sessions @ 拢50.00

8th February 2013

5 Consultancy Sessions @ 拢50.00

15th February 2013

...etc

而且我不确定如何修改PHP以获取所需的输出。

当前输出

5 Consultancy Sessions @ 拢50.00

1st February 2013

8th February 2013

15th February 2013

22nd February 2013

1st March 2013

2nd February 2013

9th February 2013

16th February 2013

3rd February 2013

10th February 2013

阅读 213

2021-03-08

共1个答案

一尘不染

问题似乎在于您在为每一行调用“ head”。因此，您应该首先检查它是否已经被调用。希望以下内容对您有所帮助：

$stmt->bind_param("s", $name1);
$stmt->execute();
$stmt->store_result();  
$stmt->bind_result($work,$amount,$count,$total_group,$date);

$last_work = "";
while ($stmt->fetch()) {
    if($work != $last_work || $amount != $last_amount){
        if ($count>1) {
           echo "<br>".$count." ".$work."s @ &pound;".$amount." each<br><br>";

        }
        else {
           echo "<br>".$count." ".$work." @ &pound;".$amount."<br><br>";
        }
        $last_work = $work;
        $last_amount = $amount;
    }
    echo date("jS F Y",strtotime($date))."<br>";
    $total_work=$total_work+$total_group;
}

在这两种情况下（和），我将echo date和移到$total_work相同的位置$count >1``else

2021-03-08