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

Question

I have the below SQL query which returns a result containing financial transactions on a loan, made on specific dates...

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

This gives the following result

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

I now need to loop through all dates that the loan has been active, and find the 'ledger_type_id' and 'amount' for transactions that occurred on the current date in the loop. The ledger table does not contain a record for every day, however I need to look at each individual day that the loan has been active for other purposes not relevant to this question.

$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.....

}

I have tried a few things using in_array() but without success.

Answer 1

When you're working in SQL and you think I need a loop, that should have you think, wait a minute. SQL is declarative, not procedural.

In SQL, the question of handling every day in a date range is usually done with a table (physical or virtual) containing all the days. We can call it the calendar table. Then we can LEFT JOIN the table with actual data to that table. In your example:

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

This will give a result set with at least one row for each calendar day.

The trick is to get an appropriate calendar table. Here's a way to do that.

             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)

This will give you a virtual table (subquery) covering the range of dates in your ledger.

So you query will look like this:

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

If you happen to be using the MariaDB fork of MySQL, the handy-dandy table seq_0_to_999999 is predefined for you as a sequence table. Otherwise you can create it easily as a series of views, like this:

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

I have more on this topic at http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/