Broken total results

Question

I am trying to find a better way to output pagination results for financial transactions with a total amount starting from the last transaction of the first and first (oldest) transactions of the last and cannot seem to find an effective way to do this.

Pulling results with only OFFSET and LIMIT will not work, because I'm trying to display the current amount.

Out of desperation, I finally went with a multidimensional array, where each array in the main array contains x number of records and accesses the results by calling each fragment of records (for example, $transArr[0] will contain the first 38 records, $transArr[1] next 38, etc.). I'm sure this is a terribly inefficient way to handle this, and I would love any suggestions.

Here's what I came up with - sorry, this is a lot of code, including paginated links and data formatting. This is only one object in the 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, according to the answer provided. This shows the correct current balance (Running Balance = Running Balance - previous amount), but I'm 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. To begin with, the paginated code part includes the offset that is commonly used in a MySQL query. Instead of using it in this traditional sense, I start by setting the counter to 1 and incrementing by 1 every time the while loop is running. When the counter reaches the offset value, I start to output the results, ending when it reaches the offset + the total number of lines on the page. When the end is reached, I exit the while loop.

It looks pretty fast. If anyone wishes to make any suggestions, please feel free to! 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

This is ugly, but you could get MySQL to execute the total for you using some server variables. The fact that you want the transactions listed in the most recent-oldest list to be slightly wrinkled but easy enough to solve:

Initialize the variable:

 SELECT @running := 0; 

Initial request:

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

which will calculate the total in order of date. Then wrap this in another query to reverse the sort order and apply the restriction 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, since the internal query will retrieve the entire table so that it can calculate the current amount, then the external query will cut off all rows except $ offset to display only this "page".

Answer 3

What you are looking for is MySQL LIMIT and OFFSET .

I would be happy to provide an example if you want.

Good luck

Answer 4

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

Now you may not need to synchronize data viewing. If you can afford to skip some recent transactions, you should create a table from your first query and then add the current total amount to the table. Your queries have become just paginated queries for this table (rebuild it via cron?). Temporary table would be a good thing, but you are working in PHP, and the temporary table remains there only for the duration of the session. Thus, you will lose it after every browser request. MySQL cursors are also not persistent. Therefore, for this you need to create a real table.

A more complicated solution is to populate this table table contents after each success of a new transaction in the source table, and this can be done using triggers , one trigger to record the transaction in the run_total table, and possibly some triggers in this table to recount the total number ( insert / update / delete).

With this solution, you lose the filtering ability of a simple SQL query (it’s not easy to filter transactions, you only have all transactions), but if you have a large volume of transactions, the variable solution will be very slow on the last pages, this is fast when you read the table.