ページ付け(PHP、MySQL)を使用する場合、現在の合計/残高を計算する最も効率的な方法は何ですか?

マイケルLB

従業員によって記録されたマイレージレコードを格納するMySQLテーブルがあります。テーブル内の各従業員のマイレージレコード(最新から最古)を実行中の残高とともに出力するPHPページがあります。これはすべて正常に機能します。

マイレージの記録は予想よりも速く成長しており、ページ付けが必要であることが明らかになりました。ページネーションをプログラムしましたが、これは正常に機能します。私が今抱えている問題は、ページネーションが、選択したレコード以外の値を無視する方法で、実行中の残高を「壊した」ことです。たとえば、6月の口座の残高を考慮せずに、銀行口座が7月の残高を提供したとします。

グーグルはあまり助けを投げていないので、理論的にこの問題に取り組む方法についての提案(私は行ごとのコードは必要ありません)は大いにありがたいです。

編集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');

編集2

動作していないクエリの試行。

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

編集3

データベース;

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

クエリ;

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

出力;

ここに画像の説明を入力してください

トリンコット

SQLクエリで実行中の残高を計算することをお勧めします。

そのSQLステートメントがどのように見えるかの例を次に示します。

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

上記のステートメント(でマークされている?のパラメーターは、以下にバインドする必要があります。

  • データを表示するemployee_id
  • 現在のページの開始行(ゼロベース)

内部クエリは、その特定の従業員のすべての行を調べ、すべてのレコードに現在の残高を追加します。次に、外部クエリは並べ替え順序を逆にし、ページングの目的で制限を適用します。

このソリューションは、PHPで計算を行う必要はありません。実行中の残高はクエリ結果セットですぐに利用でき、すべてのページで正しいです。

これは、正しいバランスを含む6行の合計セットから、ページごとに2行の2番目のページを取得するための小さなSQLフィドルです。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

Related 関連記事

ホットタグ

アーカイブ