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