Payment per month

[zavin]

I am wanting to get a total of all payment made in a month. For example June would = all payments made in June.

The code I am using right now is:

$sql = "SELECT sum( mcgross ) AS `total` FROM `payments`WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= FROM_UNIXTIME(`payment_date`)";
$rs_result = mysql_query ($sql);
while ($row = mysql_fetch_assoc($rs_result))
echo "$".$row['total'].";

Doing it this way only shows the last 30 days. Does anyone know of a good solution?

[samshel]

Try this, not tested so may have some issues.

 

$intMonth = 6;//June
$sql = "SELECT sum( mcgross ) AS `total` FROM `payments`WHERE EXTRACT(MONTH FROM FROM_UNIXTIME(`payment_date`)) = '$intMonth'";
$rs_result = mysql_query ($sql);
while ($row = mysql_fetch_assoc($rs_result))
echo "Total: ".$row['total'].";

[zavin]

That works fine. But if I want to display the total for each month will it be best to have a line of code for each month or is there a better way to get the total for each month? Also in 12 months from now is this going to add the 2 June's together?

[samshel]

Yes it will add 2 june together, and yes there is a way to display total for each month, try following code.

 

$sql = "SELECT sum( mcgross ) AS `total`,EXTRACT(MONTH FROM FROM_UNIXTIME(`payment_date`)) as month, EXTRACT(YEAR FROM FROM_UNIXTIME(`payment_date`)) as year FROM `payments`GROUP BY EXTRACT(MONTH FROM FROM_UNIXTIME(`payment_date`)), EXTRACT(YEAR FROM FROM_UNIXTIME(`payment_date`)) ";
$rs_result = mysql_query ($sql);
while ($row = mysql_fetch_assoc($rs_result))
echo "Month-".$row['month']."-".$row['year']." Total: ".$row['total'].";

 

again, code is not tested so might have some small issues.