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

Question 1

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());

EDIT 3

Database;

-- phpMyAdmin SQL Dump
-- version 4.0.10.14
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Jul 25, 2016 at 10:22 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_journey_date` date 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,
  `employee_mileage_date_created` datetime DEFAULT NULL,
  `employee_mileage_created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_mileage_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;

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

INSERT INTO `employee_mileage` (`employee_mileage_id`, `employee_mileage_employee_id`, `employee_mileage_vehicle_id`, `employee_mileage_journey_date`, `employee_mileage_start_postcode`, `employee_mileage_end_postcode`, `employee_mileage_mileage`, `employee_mileage_date_created`, `employee_mileage_created_by`) VALUES
(1, 32, 1, '2016-07-15', 'L17 0BZ', 'L6 5BJ', '4.19', '2016-07-25 10:15:01', 32),
(2, 32, 1, '2016-07-15', 'L6 5BJ', 'L17 0BZ', '4.19', '2016-07-25 10:15:01', 32),
(5, 32, 1, '2016-07-23', 'L17 0BZ', 'L17 1AE', '1.55', '2016-07-25 12:14:15', 32),
(3, 32, 1, '2016-07-21', 'L17 0BZ', 'L19 0PD', '2.03', '2016-07-25 12:09:24', 32),
(4, 32, 1, '2016-07-21', 'L19 0PD', 'L17 0BZ', '2.03', '2016-07-25 12:09:24', 32),
(6, 32, 1, '2016-07-23', 'L17 1AE', 'L17 0BZ', '1.55', '2016-07-25 12:14:15', 32);

Query;

$statement = "SELECT *
from (
        SELECT     em.*, e.*, v.*,
                   @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
        INNER JOIN vehicle v
        on em.employee_mileage_vehicle_id = v.vehicle_id
        WHERE      em.employee_mileage_employee_id = " . $employee_id . "
        ORDER BY   em.employee_mileage_id
        ) as base
ORDER BY base.employee_mileage_id DESC";

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

Output;

Question 2

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     *,
                   @balance := @balance + em.employee_mileage_mileage as balance
        FROM       (select     em.*, e.*, v.*
                    from       employee_mileage em
                    INNER JOIN employee e
                            on em.employee_mileage_employee_id = e.employee_id
                    INNER JOIN vehicle v
                            on em.employee_mileage_vehicle_id = v.vehicle_id
                    WHERE      em.employee_mileage_employee_id = ?
                    ORDER BY   em.employee_mileage_id ASC
                    ) em
        CROSS JOIN (select @balance := 0) init
        ) 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 6 rows, including the correct balance.

Question 3

After Searching on SO, I found the answer below:-

if(isset($_POST['submit'])){
$a = $_POST['userInput'];  //user enter text for search

$query2 = "select sum(buy_price) as buyPrice, sum(sell_price) as sellPrice from mobile where mob_bill='$a' or mob_imei='$a' or mob_model='$a' or mob_status='$a'";
$result2 = mysqli_query($conn,$query2);
$output2="";
while($row2=mysqli_fetch_array($result2)){
        $buyPrice = $row2['buyPrice'];
        $sellPrice = $row2['sellPrice'];
}

$sql = "select * from mobile where mob_bill='$a' or mob_imei='$a' or mob_model='$a' or mob_status='$a'";
$result = mysqli_query($conn,$sql);
$output="";
while($row=mysqli_fetch_array($result)){
        $bill = $row['mob_bill'];
        $imei = $row['mob_imei'];
        $model = $row['mob_model'];

    $output.="<tr>
                <td>$bill</td>
                <td>$imei</td>
                <td>$model</td>
            </tr>"; 

 }
}
else{


}

<?php echo $outout; ?>
<?php echo $buyPrice; ?>
<?php echo $sellPrice; ?>

Question 4

Another interesting idea I found:

A better design is to convert the pager to a “next” link. Assuming there are 20 results per page, the query should then use a LIMIT of 21 rows and display only 20. If the 21st row exists in the results, there’s a next page, and you can render the “next” link.

If you don't need the total count of the table it's indeed the fastests solution.

Question 5

If performance is an issue, you could use a MySQL variable:

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

Alternatively, you could remove the cumulative_sum column and calculate it on each query:

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

This calculates the running sum in a running way :)

Question 6

What about just using an integer column which defines the order? By default you assign numbers * 1000, like 1000, 2000, 3000.... and if you move 3000 between 1000 and 2000 you change it to 1500. So in most cases you don't need to update the other numbers at all. I use this approach and it works well. You could also use double but then you don't have control about the precision and rounding errors, so rather don't use it.

So the algorithm would look like: say you move B to position after A. First perform select to see the order of the record next to A. If it is at least +2 higher than the order of A then you just set order of B to fit in between. But if it's just +1 higher (there is no space after A), you select the bordering records of B to see how much space is on this side, divide by 2 and then add this value to the order of all the records between A and B. That's it!

(Note that you should use transaction/locking for any algorithm which contains more than a single query, so this applies to this case too. The easiest way is to use InnoDB transaction.)

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

Answers

php,mysql,mysqli,cumulative-sum