Help! How to display running balance?

Calling all my geek friends!!!

I am developing a page that displays values with a running balance.

Sample data:

    ID Date Amount Category
    1 01/01/2013 1000 Cash
    2 01/10/2013 600 Cash
    3 01/05/2013 -500 Credit

Sample outputs:

(1) All Categories

    Date Amount Running Total
    01/10/2013 600 1100
    01/05/2013 -500 500
    01/01/2013 1000 1000

(2) Specific Category (e.g. Cash)

    Date Amount Running Total
    01/10/2013 600 1600
    01/01/2013 1000 1000

As you can see, I cannot “cheat” by computing the balance upon entry because (1) users can manually override the date and (2) list can be filtered by category.

Here are the solutions I have right now:

(1) Use nested SQL


    SET @runtot:=0;
    SELECT
        date, amount,
        (@runtot := @runtot + amount) AS rt
    FROM
        ledgers
    WHERE
        category = :variable:
    ORDER BY
        date ASC

(2) Use PHP

Simply select all records with corresponding category then use PHP to compute balance everytime a record is displayed.
Both solution is problematic in “paged” view mode (not sure what it is officially called but it is when you divide total records into different pages… [1][2]).

Sample of "Paged" view
Sample of “Paged” view

A workaround would be to to simply to use (either) query and grab and process all records… but display only the pertinent information but this might get really slow as the number of records go up.

Would you know of any other solution? If these are your only options as well, which would be the more efficient way to do it?

Thanks in advance 🙂

Coach Rye
Let's chat!

Coach Rye

Head Coach at Coach Rye
Ryan Salvanera is an Executive Coach who helps teams and individuals maximize their unique powers, taking them from good to great to AWESOME! Ryan is the Founder of Coach Rye and a Co-Founder of Wissen Solomon and StrengthsCoach.PH.

Ryan’s top five talent themes are:
Relator, Arranger, Learner, Maximizer and Individualization.

Do you want a 30-minute complimentary coaching session with Coach Rye? Check out his availability here: Calendly.
Coach Rye
Let's chat!

Latest posts by Coach Rye (see all)