php - 将 sql 变量从 INNER JOIN SQL 查询传递给 PHP 脚本

Question

我有以下 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 fiddle 的示例表上看到它的运行情况 here .

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

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

Answer 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 和 否则)