将SQL变量从INNER JOIN SQL查询传递到PHP脚本 [英] Passing sql variables from INNER JOIN SQL query to PHP script

问题描述

我有以下INNER JOIN查询:

  SELECT b.*，c.date2从    (选择一项工作，一项金额，COUNT(*)个总数，SUM(金额)totalAmount来自work_times的WHERE Organization =?按工作分组，数量)b内部联接(SELECT a.work，a.amount，DATE_FORMAT(Date，'％D％M％Y')date2，日期FROM work_times a)c ON b.work = c.work和b.amount = c.amount按b.work，b.totalCount，c.date排序 

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

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

  5场咨询会议，每次50英镑:250英镑2013年2月1日2013年2月8日2013年2月15日2013年2月22日2013年3月1日3次疗程，每次40英镑:120英镑2013年2月2日2013年2月9日2013年2月16日2次治疗，每次20英镑:40英镑2013年2月3日2013年2月10日 

但是使用以下PHP:

  $ stmt-> bind_param("s"，$ name1);$ stmt-> execute();$ stmt-> store_result();$ stmt-> bind_result($ work，$ amount，$ count，$ total_group，$ date);while($ stmt-> fetch()){如果($ count> 1){echo $ count.".$ work."s @& pound;".$ amount."each< br>< br>";echo date("jS F Y"，strtotime($ date)).$ total_work = $ total_work + $ total_group;}别的 {echo $ count.".$ work."@£".$ amount.< br>< br>";echo date("jS F Y"，strtotime($ date)).$ total_work = $ total_work + $ total_group;}} 

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

  5次咨询会@£50.002013年2月1日5场顾问会议，每位£50.002013年2月8日5场顾问会议，每位£50.002013年2月15日...等等 

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

当前输出

  5次咨询会@£50.002013年2月1日2013年2月8日2013年2月15日2013年2月22日2013年3月1日2013年2月2日2013年2月9日2013年2月16日2013年2月3日2013年2月10日 

解决方案

问题似乎出在您为每一行调用"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){如果($ count> 1){echo< br>".$ count.".$ work."s @& pound;".$ amount.每个
}别的 {echo< br>".$ count.".$ work."@£".$ 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 )

I have the following INNER JOIN query:

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

You can see it in action on a sample table on SQL fiddle here.

My aim is to return the following:

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

But using the following 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;
        }

        }

I am getting one line for each row, rather than the grouping, i.e.:

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

And I am not sure how to amend my PHP to get the desired output.

CURRENT OUTPUT

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

解决方案

The problem seems to be in the fact that you're calling the "head" for each row. Therefore, you should first check if it's been already called. I hope the following may help you:

$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;
}

I moved the echo date and $total_work outside as they were being called equally in both cases ($count >1 and else)

这篇关于将SQL变量从INNER JOIN SQL查询传递到PHP脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！