Loop PHP, NO DUPLICATE Field from by group, sum mysql

debugcn Published at Dev

Rikudo Pain

this is my table 'dp'

Year      | Month     | Payment|  Payer_ID | Payment_Recipient |
2008/2009 | July      | 100000 |     1     |        John       |
2008/2009 | August    | 200000 |     1     |        Jane       |
2009/2010 | August    | 150000 |     1     |        Jane       |
2009/2010 | September | 175000 |     1     |        John       |

I need output like this in my php page :

Year      |       July     |      August   |     September | 
2008/2009 | 100000 | John  | 200000 | Jane |    0   |   -  | 
2009/2010 |    0   |   -   | 150000 | Jane | 175000 | John |

I use this sql query :

$query_sql = "SELECT
   year
 , SUM(IF(Month='July', Payment, 0)) As 'July'
 , SUM(IF(Month='August', Payment, 0)) As 'August'
 , SUM(IF(Month='September', Payment, 0)) As 'September'
FROM 
 dp
WHERE 
 Payer_ID = 1
GROUP BY
 Year";


<table>
<tr>
<td>year</td>
<td>July</td>
<td>August</td>
<td>September</td>
</tr>
<?php
while($data=mysql_fetch_array($sql)){
?>
<tr>
<td><?php echo $data['year']; ?></td>
<td><?php echo $data['July']; ?></td>
<td><?php echo $data['August']; ?></td>
<td><?php echo $data['September']; ?></td>
</tr>
<?php } ?>
</table>

and the output always like this :

Year      |  July  | August | September | 
2009/2010 |    0   | 150000 | 175000    |

only show 1 year (not looping).

Can somebody help me to fix my code in 'sum(if' to include payment recipient and fix my variable in php to loop?

Kickstart

Crude SQL way of doing it:-

   SELECT DISTINCT dp.Year, SubJuly.Payment, SubJuly.Payment_Recipient, SubAugust.Payment, SubAugust.Payment_Recipient, SubSeptember.Payment, SubSeptember.Payment_Recipient
    FROM dp
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'July'
    ) SubJuly
    ON dp.Year = SubJuly.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'August'
    ) SubAugust
    ON dp.Year = SubAugust.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'September'
    ) SubSeptember
    ON dp.Year = SubSeptember.Year
WHERE  dp.Payer_ID = 1

It could probably done more efficiently by using a list of months / years and LEFT JOINing that against the original table, then just working on the change of row in the php.

Lobbing that back into your original php:-

<?php


$query_sql = "   SELECT DISTINCT dp.Year, SubJuly.Payment, SubJuly.Payment_Recipient, SubAugust.Payment, SubAugust.Payment_Recipient, SubSeptember.Payment, SubSeptember.Payment_Recipient
    FROM dp
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'July'
    ) SubJuly
    ON dp.Year = SubJuly.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'August'
    ) SubAugust
    ON dp.Year = SubAugust.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'September'
    ) SubSeptember
    ON dp.Year = SubSeptember.Year
WHERE  dp.Payer_ID = 1";

....................
?>
<table>
<tr>
<td>year</td>
<td colspan='2'>July</td>
<td colspan='2'>August</td>
<td colspan='2'>September</td>
</tr>
<?php
while($data=mysql_fetch_assoc($sql))
{
    echo "<tr><td>".implode("</td><td>", $data)."</td></tr>";
} 
?>
</table>

Note you probably should be using mysqli_* functions rather than the mysql_* functions.

Collected from the Internet

Please contact [email protected] to delete if infringement.