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

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".