Michael LB Michael LB - 4 months ago 51
MySQL Question

What's the most efficient way to calculate a running total/balance when using pagination (PHP, MySQL)

I have a MySQL table that stores mileage records logged by employees. I have a PHP page that outputs the mileage records for each employee in a table (newest to oldest) along with a running balance. This all works fine.

The mileage records are growing faster than anticipated and it's now become apparent that pagination is required. I have programmed the pagination and this works fine. The problem I now have is that the pagination has 'broken' the running balance in the way that it ignores any values outside of the records that I have selected. For example, imagine your bank account just provided a balance for the month of July without taking into account what was in your account in June.

Suggestions on how to tackle this problem in theory (I don't need line by line code) would be much appreciated as Google isn't throwing up much help.

EDIT 1

Database and data

-- phpMyAdmin SQL Dump
-- version 4.0.10.14
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Jul 21, 2016 at 07:11 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `propsyst_main`
--

-- --------------------------------------------------------

--
-- Table structure for table `employee_mileage`
--

CREATE TABLE IF NOT EXISTS `employee_mileage` (
`employee_mileage_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_mileage_employee_id` int(11) DEFAULT NULL,
`employee_mileage_vehicle_id` smallint(6) DEFAULT NULL,
`employee_mileage_start_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_end_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_mileage` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`employee_mileage_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;

--
-- Dumping data for table `employee_mileage`
--

INSERT INTO `employee_mileage` (`employee_mileage_id`, `employee_mileage_employee_id`, `employee_mileage_vehicle_id`, `employee_mileage_start_postcode`, `employee_mileage_end_postcode`, `employee_mileage_mileage`) VALUES
(1, 1, 2, 'L17 0BZ', 'L36 9TJ', '1.00'),
(2, 1, 2, 'L17 0BZ', 'L36 9TJ', '2.00'),
(3, 1, 2, 'L17 0BZ', 'L36 9TJ', '3.00'),
(4, 1, 2, 'L17 0BZ', 'L36 9TJ', '4.00'),
(5, 1, 2, 'L17 0BZ', 'L36 9TJ', '5.00'),
(6, 1, 2, 'L17 0BZ', 'L36 9TJ', '6.00'),
(7, 1, 2, 'L17 0BZ', 'L36 9TJ', '7.00');


EDIT 2

Query attempt which is not working;

$statement = "SELECT *
from (
SELECT em.*, e.*,
@balance := @balance + em.employee_mileage_mileage as balance
FROM employee_mileage em
CROSS JOIN (select @balance := 0) init
INNER JOIN employee e
on em.employee_mileage_employee_id = e.employee_id
where em.employee_mileage_employee_id = " . $employee_id . "
order by em.employee_mileage_id
) as base
ORDER BY em.employee_mileage_id DESC";

$employee_mileage_query = mysqli_query($con,"{$statement} LIMIT {$startpoint} , {$per_page}") or die(mysql_error());

Answer

I would suggest calculating the running balance in your SQL query.

Here is an example of what that SQL statement could look like:

SELECT *
FROM (
        SELECT     em.*, e.*,
                   @balance := @balance + em.employee_mileage_mileage as balance
        FROM       employee_mileage em
        CROSS JOIN (select   @balance := 0) init
        INNER JOIN employee e
                ON em.employee_mileage_employee_id = e.employee_id
        WHERE      em.employee_mileage_employee_id = ?
        ORDER BY   em.employee_mileage_id
        ) as base
ORDER BY   base.employee_mileage_id DESC
LIMIT   ?, 2

The parameters in the above statement (marked with ?) should be bound to:

  • the employee_id you want to show the data for
  • the start row of the current page (zero-based)

The inner query goes through all rows for that particular employee and adds the running balance to every record. Then the outer query reverses the sort order and applies the limit to it for paging purposes.

This solution needs no calculation to happen in PHP; the running balance is readily available in the query result set, and is correct on every page.

Here is a small SQL fiddle demonstrating it for getting the second page, with 2 rows per page, from a total set of 7 rows, including the correct balance.

Comments