php - 从数组中的特定行获取数据

Question

我有下面的 SQL 查询，它返回包含在特定日期进行的贷款金融交易的结果...

$data = array( 'loan_id'=>130 );
$STH = $DBH->prepare("SELECT * FROM ledger WHERE loan_id = :loan_id");
$STH->execute($data);
$STH->setFetchMode(PDO::FETCH_ASSOC);
$row = $STH->fetchAll(); 

print_r($row);

这给出了以下结果

Array ( 
 [0] => Array ( 
        [ledger_id] => 38 
        [loan_id] => 130 
        [ledger_type_id] => 1 
        [amount] => 1.20 
        [ledger_date] => 2016-07-25 
    ) 

 [1] => Array ( 
        [ledger_id] => 39 
        [loan_id] => 130 
        [ledger_type_id] => 3 
        [amount] => 0.90 
        [ledger_date] => 2016-08-15 
    ) 

 [2] => Array ( 
        [ledger_id] => 40 
        [loan_id] => 130 
        [ledger_type_id] => 2 
        [amount] => 0.30 
        [ledger_date] => 2016-09-19 
    ) 
) 

我现在需要循环遍历贷款处于事件状态的所有日期，并找到循环中当前日期发生的交易的“ledger_type_id”和“金额”。分类帐表不包含每天的记录，但是我需要查看贷款出于与此问题无关的其他目的而处于事件状态的每一天。

$start_date = new DateTime('2016-07-13');
$today = new DateTime();
$today = $today->modify( '+1 day' ); // add one day to include today
$interval = new DateInterval('P1D'); 
$daterange = new DatePeriod($start_date, $interval ,$today);

foreach ($daterange as $date) {

    /*  
       The above SQL query returns a row with the 'ledger_date' 
       of '2016-08-15'

       I need to find the value of the ledger_type_id 
       for the same row from the above query 
       when $date = '2016-08-15' in this loop
    */

    // Do other unrelated stuff on each day of the loop.....

}

我使用 in_array() 尝试了一些方法，但没有成功。

Answer 1

当您使用 SQL 时，您认为我需要一个循环，这应该让您想到，等一下。SQL 是声明性的，而不是过程性的。

在 SQL 中，处理日期范围内的每一天的问题通常是通过包含所有日期的表(物理或虚拟)来完成。我们可以将其称为日历表。然后我们可以将具有实际数据的表LEFT JOIN添加到该表中。在您的示例中:

SELECT calendar.day, ledger.*
  FROM calendar
  LEFT JOIN ledger ON calendar.day = ledger.date
 WHERE loan_id = :loan_id
 ORDER BY calendar.day, ledger.id

这将给出每个日历日至少一行的结果集。

诀窍是获取适当的日历表。这是一种方法。

             SELECT mintime + INTERVAL seq.seq DAY AS day
               FROM (
                       SELECT MIN(DATE(ledger.date)) AS mintime,
                              MAX(DATE(ledger.date)) AS maxtime
                         FROM ledger
                    ) AS minmax
               JOIN seq_0_to_999999 AS seq 
                 ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)

这将为您提供一个涵盖分类帐中日期范围的虚拟表(子查询)。

所以你的查询将如下所示:

SELECT calendar.day, ledger.*
  FROM (
             SELECT mintime + INTERVAL seq.seq DAY AS day
               FROM (
                       SELECT MIN(DATE(ledger.date)) AS mintime,
                              MAX(DATE(ledger.date)) AS maxtime
                         FROM ledger
                    ) AS minmax
               JOIN seq_0_to_999999 AS seq 
                 ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)
       ) calendar
  LEFT JOIN ledger ON ledger.date >= calendar.day
                  AND ledger.date < calendar.day + INTERVAL 1 DAY
 WHERE loan_id = :loan_id
 ORDER BY calendar.day, ledger.id

如果您碰巧使用 MariaDB fork MySQL 中，方便的花花公子表 seq_0_to_999999 已为您预定义为 sequence table 。否则，您可以轻松地将其创建为一系列 View ，如下所示:

DROP TABLE IF EXISTS seq_0_to_9;
CREATE TABLE seq_0_to_9 AS
   SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;
DROP VIEW IF EXISTS seq_0_to_999;
CREATE VIEW seq_0_to_999 AS (
SELECT (a.seq + 10 * (b.seq + 10 * c.seq)) AS seq
  FROM seq_0_to_9 a
  JOIN seq_0_to_9 b
  JOIN seq_0_to_9 c
);
DROP VIEW IF EXISTS seq_0_to_999999;
CREATE VIEW seq_0_to_999999 AS (
SELECT (a.seq + (1000 * b.seq)) AS seq
  FROM seq_0_to_999 a
  JOIN seq_0_to_999 b
);

我在http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/有更多关于这个主题的信息。