zavin Posted July 14, 2008 Share Posted July 14, 2008 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? Link to comment https://forums.phpfreaks.com/topic/114656-payment-per-month/ Share on other sites More sharing options...
samshel Posted July 14, 2008 Share Posted July 14, 2008 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']."; Link to comment https://forums.phpfreaks.com/topic/114656-payment-per-month/#findComment-589610 Share on other sites More sharing options...
zavin Posted July 14, 2008 Author Share Posted July 14, 2008 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? Link to comment https://forums.phpfreaks.com/topic/114656-payment-per-month/#findComment-589633 Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 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. Link to comment https://forums.phpfreaks.com/topic/114656-payment-per-month/#findComment-590193 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.