Solved

How to make a sum per month of payments using php and mysql?

Posted on 2012-04-04
3
502 Views
Last Modified: 2012-07-16
HI

I need to perform a query from php that requests to a mysql database the sum of payments of a customer per month, I would like to know how to construct this query from php based on this table:

PAYMENTS
IdPayment, IdCustomer, PaymentDate, Amount

The query must show something like a balance since the first payment to the last one:

JANUARY 2012 (this date must be since the first payment of the customer)
IdCustomer, Total Amount Paid

FEBRUARY 2012
IdCustomer, Total Amount Paid
...

and so on.


Regards
0
Comment
Question by:dimensionav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
ID: 37808461
I'm not a PHP developer but the SQL should be something like:

select date_format(PaymentDate, '%M %Y') payment_month, IdCustomer, sum(Amount)
from PAYMENTS
group by year(PaymentDate), month(PaymentDate), IdCustomer
order by year(PaymentDate), month(PaymentDate), IdCustomer

Then in your PHP code you need to track the payment_month column of the result set - if it changes it means you have a new month and so you should print whatever headers you want.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37809726
There was a similar question in the Oracle zone recently. With 'balance' the Asker there didn't mean the sum per period but the total from the start until that point in time. I submitted a solution with a subquery but there was a better solution in PL/SQL using 'partition', not sure if anything like that is available in MySQL so I'll submit my 'low-tech' solution here as well:

SELECT p1.IdCustomer, date_format(p1.PaymentDate, '%M %Y') payment_month, (SELECT SUM(Amount) FROM PAYMENTS p2 WHERE p2.IdCustomer = p1.IdCustomer AND p2.PaymentDate <= LAST_DAY(p1.PaymentDate)) balance
FROM PAYMENTS p1
GROUP BY p1.IdCustomer, payment_month
ORDER BY p1.IdCustomer, YEAR(p1.PaymentDate), MONTH(p1.PaymentDate)

Open in new window

0
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 250 total points
ID: 37809768
For the php part, there's a lot of examples to be found on the net, like this one: http://www.anyexample.com/programming/php/php_mysql_example__display_table_as_html.xml 

Or do you need specific help, in that case please post the (relevant) code?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question