Paginated results with a running total

Question

I have been trying to think of the best way to output paginated results for financial transactions with a running total, starting with the most recent transaction first and the first (oldest) transaction last, and cannot seem to find an efficient way to go about it.

Pulling the results with OFFSET and LIMIT alone will not work because I am trying to display a running total.

Out of desperation I finally went with a multidimensional array, where each array within the primary array holds x number of entries, and access the results by calling each chunk of entries (for example, $transArr[0] would contain the first 38 records, $transArr[1] the next 38, etc). I am sure that this is a horribly inefficient way of handling this and I would love any and all suggestions.

Here is what I have come up with - sorry, it's a lot of code, including the paginated links and data formatting. This is but one object in a class.

public function fetchTransactionsDev($currPage = null) {
    global $db;
    //Balance the account, set accountBalance variable
    $this->balanceAccount();
    $accountBalance = $this->accountBalance;
    $runningTotal = $accountBalance; //Start the Running Total as the balance
    $prevAmount = 0; //Starts at 0, will be used to calculate running total below

    //Fetch number of rows and calculate number of pages for paginated links
    $numRows = $db->query("SELECT COUNT(*) FROM transactions");
    $numRows = $numRows->fetchColumn();
    $this->totalTrans = $numRows;
    //Number of rows to display per page
    $rowsPerPage = 35;
    //Find out total pages, factoring in that the array starts at 0
    $totalPages = ceil($numRows / $rowsPerPage) - 1;
    //Get current page or set default
    if (isset($currPage) && is_numeric($currPage)) {
        $currentPage = (int) $currPage;
    } else {
        $currentPage = 0;
    }
    //Set $currPage to $totalPages if greater than total
    if ($currentPage > $totalPages) {
        $currentPage = $totalPages;
    }
    if ($currentPage < 1) {
        $currentPage = 0;
    }
    //Offset of the list, based on current page
    $offset = ($currentPage - 1) * $rowsPerPage;

    //Array to hold transactions; counters for number of arrays and number of entries per array
    $transArr = array();
    $arrCount = 0;
    $i = 0;

    //Fetch the transactions
    $sql = "SELECT amount, payee, cat, date FROM transactions ORDER BY id DESC, date DESC";
    $fetchTransactionsSQL = $db->query($sql);
    while ($transactionDetails = $fetchTransactionsSQL->fetch()) {
        $date = date("m/d", strtotime($transactionDetails['date']));
        $payee = stripslashes($transactionDetails['payee']);
        $category = $transactionDetails['cat'];
        $amount = $transactionDetails['amount'];
        $runningTotal -= $prevAmount;
        $amountOutput = money_format("%n", $amount);
        $runningTotalOutput = money_format("%n", $runningTotal);
        //Add new array to $transArr with a maximum of x num entries
        if ($i <= $rowsPerPage) {
            $transArr[$arrCount][] = array("date" => $date, "payee" => $payee, "category" => $category, 
            "amountOutput" => $amountOutput, "runningTotalOutput" => $runningTotalOutput);
            $i++;
        } else {
            //If over x number of entries, start a new array under $transArr and reset increment counter
            $arrCount++;
            $i = 0;
            $transArr[$arrCount][] = array("date" => $date, "payee" => $payee, "category" => $category, 
            "amountOutput" => $amountOutput, "runningTotalOutput" => $runningTotalOutput);;
        }
        if ($arrCount > $currentPage) {
            break;
        }
        $prevAmount = $amount; //Needed for calculating running balance
    }
    //Output the results to table
    foreach ($transArr[$currentPage] as $transaction) {
        echo "
            <tr>
                <td>{$transaction['date']}</td>
                <td><strong>{$transaction['payee']}</strong></td>
                <td>{$transaction['category']}</td>
                <td>{$transaction['amountOutput']}</td>
                <td>{$transaction['runningTotalOutput']}</td>
            </tr>                    
        ";
    }
    //Create paginated links
    if ($currentPage > 0) {
        $prevPage = $currentPage - 1;
        $this->pageLinks = "<a href='{$_SERVER['PHP_SELF']}?currentPage=$prevPage'>Prev</a>";
    }
    if ($currentPage != $totalPages) {
        $nextPage = $currentPage + 1;
        $runningBal = $runningTotal - $prevAmount;
        $this->pageLinks .= "  <a href='{$_SERVER['PHP_SELF']}?currentPage=$nextPage'>Next</a>";
    }
}

Again, thanks for any suggestions!

UPDATE

Here is my updated SQL, along the lines of an answer provided. This shows the correct running balance (Running Balance = Running Balance - Previous Amount) but I am stuck trying to create paginated results.

$dough = new doughDev;
$dough->balanceAccount();
$accountBalance = $dough->accountBalance;
$setRunning = $db->query("SET @running := $accountBalance, @prevAmount = 0");
$getRunning = $db->query("SELECT amount, @running := @running - @prevAmount AS running, @prevAmount := amount AS prevAmount FROM transactions ORDER BY id DESC, date DESC");

Answer 1

Using some of what I learned from other answers to this question (especially MySQL variables - thanks to Marc B), I formulated a fairly simple solution to this problem. For starters, part of the paginated link code includes an offset which would usually be used in a MySQL query. Instead of using it in this traditional sense, I start by setting a counter to 1 and incrementally increasing by 1 each time the while loop runs. When the counter reaches the offset value, I begin outputting the results, ending when it reaches the offset + total number of rows per page. When the end is reached, I break out of the while loop.

It appears to be pretty speedy. If anyone would like to make any suggestions, please feel free! Here is the final code:

public function fetchTransactions($currPage = null) {
        global $db;
        //Balance account
        $this->balanceAccount();
        $accountBalance = $this->accountBalance;
        //Fetch number of rows and calculate number of pages for paginated links
        $numRows = $db->query("SELECT COUNT(*) FROM transactions");
        $numRows = $numRows->fetchColumn();
        $this->totalTrans = $numRows;
        //Number of rows to display per page
        $rowsPerPage = 35;
        //Find out total pages
        $totalPages = ceil($numRows / $rowsPerPage);
        //Get current page or set default
        if (isset($currPage) && is_numeric($currPage)) {
            $currentPage = (int) $currPage;
        } else {
            $currentPage = 1;
        }
        //Set $currPage to $totalPages if greater than total
        if ($currentPage > $totalPages) {
            $currentPage = $totalPages;
        }
        if ($currentPage < 1) {
            $currentPage = 1;
        }
        //Offset of the list, based on current page
        $offset = ($currentPage - 1) * $rowsPerPage;
        //Set end point for records per page
        $end = $offset + $rowsPerPage;

        //Start counter for retrieving records for current page
        $i = 1;

        //Fetch the transactions
        $setRunning = $db->query("SET @running := $accountBalance, @prevAmount = 0"); //Sets varaible for calculating running total
        $sql = "SELECT amount, @running := @running - @prevAmount AS running, @prevAmount := amount AS prevAmount, payee, cat, date 
        FROM transactions ORDER BY id DESC, date DESC";
        $fetchTransactionsSQL = $db->query($sql);
        while ($transactionDetails = $fetchTransactionsSQL->fetch()) {
            $amount = $transactionDetails['amount'];
            //If counter reaches beginning of offset, start outputting results. End when the last row for result set is reached
            if ($i >= $offset && $i < $end) {
                $date = date("m/d", strtotime($transactionDetails['date']));
                $payee = stripslashes($transactionDetails['payee']);
                $category = $transactionDetails['cat'];
                $amountOutput = money_format("%n", $amount);
                $runningTotalOutput = money_format("%n", $transactionDetails['running']);
                echo "
                <tr>
                    <td>$date</td>
                    <td><strong>$payee</strong></td>
                    <td>$category</td>
                    <td>$amountOutput</td>
                    <td>$runningTotalOutput</td>
                </tr>                    
                ";
            }
            //If the end of the result set has been reached, break out of while loop. Else, increment the counter
            if ($i == $end) {
                break;
            } else {
                $i++;
            }
        }
        //Create paginated links
        if ($currentPage > 1) {
            $prevPage = $currentPage - 1;
            $this->pageLinks = "<a href='{$_SERVER['PHP_SELF']}?currentPage=$prevPage'>Prev</a>";
        }
        if ($currentPage != $totalPages) {
            $nextPage = $currentPage + 1;
            $runningBal = $runningTotal - $prevAmount;
            $this->pageLinks .= "  <a href='{$_SERVER['PHP_SELF']}?currentPage=$nextPage'>Next</a>";
        }
    }

Answer 2

My first thought was the same as @Marc B answer (+1). Using variables in the query.

Now you may not need a synchronous view of the data. If you can afford missing some recent transactions you should build a table from your first query, then adding the running total amount in the table. Your requests became simple paginated requests on this table (rebuild it via cron?). A temporary table would have been a good thing, but you're working in PHP and the temporary table stays there only for the session duration. So you would loose it after each browser request. MySQL cursors are not persistent either. So you should build a real table for that.

A more advanced solution is to fill this table table content after each new transaction success on the original table, and this could be done with triggers, one trigger to record the transaction in the running_total table, and maybe some triggers on this table to recompute the running total (insert/update/delete).

With this solution you loose the filtering capacity of a simple SQL query (not easy to filter the transactions, you've got all transactions only), but if you have a big amount of transactions the variable solution would be very slow on last pages, this one is fast when you read the table.

Answer 3

It's kind of ugly, but you could have MySQL do the running total for you using some server-side variables. The fact that you want transactions listed most-recent-to-oldest is a bit of a wrinkle, but easy enough to deal with:

Initialize a variable:

SELECT @running := 0; 

Primary query:

SELECT amount, @running := @running + amount AS running, payee, cat, date
FROM ...
ORDER BY date ASC

which'll calculate runnign total in a date-forward order. Then wrap this in another query, to reverse the sort order and apply the limit clause

SELECT amount, running, payee, cat, date
FROM (
    ... previous query here ...
) AS temp
ORDER BY date DESC
LIMIT $entries_to_show, $offset

This is somewhat inefficient, as the inner query will fetch the entire table so it can calculate the running total, then the outer query will lop off all but $offset worth of rows to display only that "page".

Answer 4

What you're looking for is MySQL's LIMIT and OFFSET functionality.

I'll be glad to provide an example if you'd like.

Good luck!