我一直在努力想出最好的方法来输出金融交易的分页结果和运行总计，首先是最近的交易，最后是第一个(最旧的)交易，但无法似乎找到了一种有效的方法。

单独使用 OFFSET 和 LIMIT 提取结果是行不通的，因为我正在尝试显示运行总计。

出于绝望，我最终选择了一个多维数组，其中主数组中的每个数组都包含 x 个条目，并通过调用每个条目 block 来访问结果(例如，$transArr[0] 将包含前 38 条记录，$transArr[1] 接下来的 38 条，等等)。我确信这是一种非常低效的处理方式，我会喜欢任何和所有建议。

这是我想出的 - 抱歉，它有很多代码，包括分页链接和数据格式。这只是一个类中的一个对象。

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>";
    }
}

再次感谢您的任何建议!

更新

根据提供的答案，这是我更新的 SQL。这显示了正确的运行余额(运行余额 = 运行余额 - 之前的金额)，但我无法尝试创建分页结果。

$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");

最佳答案

这有点难看，但您可以让 MySQL 使用一些服务器端变量为您计算总计。您希望按照从最近到最旧的顺序列出交易这一事实有点麻烦，但很容易处理:

初始化一个变量:

SELECT @running := 0; 

主要查询:

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

这将按日期远期顺序计算运行总数。然后将其包装在另一个查询中，以反转排序顺序并应用限制子句

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

这有点低效，因为内部查询将获取整个表以便计算运行总计，然后外部查询将删除除 $offset 值以外的所有行以仅显示该“页面”。

关于php - 带有总计的分页结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7957323/