Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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
share|improve this question

1 Answer

up vote 1 down vote accepted

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)

share|improve this answer
Thanks. That seems to be along the right lines, but the output still isn't quite right - I am only getting one grouping returned as the $last_work variable stops any further groupings being added. – Nick Feb 22 at 13:11
Well, in the case it's not the same it should add a new "head". Could I see the output to have a new try at the solution? :) – Sikian Feb 22 at 13:18
I have added the current output to the end of my question. – Nick Feb 22 at 13:23
OK, I worked out the issue. It was that, as in the case of 'Therapy Sessions' above, sometimes the work is the same but the amount is different, so I needed to add another variable $last_amount and then check against this as well, i.e. if($work != $last_work OR $amount != $last_amount). If you want to amend your answer I can mark it as correct. Thanks – Nick Feb 22 at 14:19
I amended the answer, but I'm still not sure if it's the correct one. I'll keep thinking about it :) Btw, I changed the spacing so the output is similar to the one you were looking for. – Sikian Feb 22 at 17:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.