Mattlinux1 Mattlinux1 - 6 months ago 19
SQL Question

How to get the nearest date match in PHP from a SQL Database

I am currently trying to get the nearest match of 2 sets of dates (d-m-Y) against a SQL database and then out put the matched InterestRate column name. Example of Database data below:

+---------------------+---------------------+--------------+
| StartDate | EndDate | InterestRate |
+---------------------+---------------------+--------------+
| 2011-07-01 00:00:00 | 2012-01-01 00:00:00 | 8.50 |
| 2012-01-01 00:00:00 | 2012-07-01 00:00:00 | 8.50 |
| 2012-07-01 00:00:00 | 2013-01-01 00:00:00 | 8.50 |
| 2013-01-01 00:00:00 | 2013-07-01 00:00:00 | 8.50 |
| 2013-07-01 00:00:00 | 2014-01-01 00:00:00 | 8.50 |
| 2014-01-01 00:00:00 | 2014-07-01 00:00:00 | 8.50 |
| 2014-07-01 00:00:00 | 2015-01-01 00:00:00 | 8.50 |
| 2015-01-01 00:00:00 | 2015-07-01 00:00:00 | 8.50 |
| 2015-07-01 00:00:00 | 2016-01-01 00:00:00 | 8.50 |
| 2016-01-01 00:00:00 | 2016-07-01 00:00:00 | 8.50 |
| 2010-07-01 00:00:00 | 2011-01-01 00:00:00 | 8.50 |
| 2010-01-01 00:00:00 | 2010-07-01 00:00:00 | 8.50 |
| 2009-07-01 00:00:00 | 2010-01-01 00:00:00 | 8.50 |
| 2009-01-01 00:00:00 | 2009-07-01 00:00:00 | 10.00 |
| 2008-07-01 00:00:00 | 2009-01-01 00:00:00 | 13.00 |
| 2008-01-01 00:00:00 | 2008-07-01 00:00:00 | 13.50 |
| 2007-07-01 00:00:00 | 2008-01-01 00:00:00 | 13.50 |
| 2007-01-01 00:00:00 | 2007-07-01 00:00:00 | 13.00 |
| 2006-07-01 00:00:00 | 2007-01-01 00:00:00 | 12.50 |
| 2006-01-01 00:00:00 | 2006-07-01 00:00:00 | 12.50 |
| 2005-07-01 00:00:00 | 2006-01-01 00:00:00 | 12.75 |
| 2005-01-01 00:00:00 | 2005-07-01 00:00:00 | 12.75 |
| 2004-07-01 00:00:00 | 2005-01-01 00:00:00 | 12.50 |
| 2004-01-01 00:00:00 | 2004-07-01 00:00:00 | 11.75 |
| 2003-07-01 00:00:00 | 2004-01-01 00:00:00 | 11.75 |
| 2003-01-01 00:00:00 | 2003-07-01 00:00:00 | 12.00 |
| 2002-08-07 00:00:00 | 2003-01-01 00:00:00 | 12.00 |
| 2011-01-01 00:00:00 | 2011-07-01 00:00:00 | 8.50 |
+---------------------+---------------------+--------------+


I have an example of my current code, this is mostly pseudo code and trying a few things out. If that helps.

//$XSS_BLOCK2 = "05-05-2016";
$XSS_BLOCK3 = "20-05-2016";
$today = date('d-m-Y');
$interest = 0;
$securesqlstring = $secureconn->prepare("SELECT * FROM LatePaymentRates");
$securesqlstring->execute();
while($row=$securesqlstring->fetch())
{
echo $row['StartDate'];
echo $row['EndDate'];
echo $row['InterestRate'];

$varsin = array($XSS_BLOCK3, $today);
$DateRange = new DateTime($varsin);
$databasein = array($row['StartDate'], $row['EndDate']);
$DateRanges = new DateTime($databasein);
if(($DateRange >= $DateRanges) && ($DateRange >= $DateRanges)) {

$dayrate = $row['InterestRate'] * $XSS_BLOCK3 / 36500;


$start_date = new DateTime($DateRange);
$end_date = new DateTime($DateRanges);
$dd = date_diff($end_date, $start_date) * $dayrate;
$interest += $dayrate;
}

}
$LatePaymentInterest = $interest;
if (!$securesqlstring) // If there is an error it will show this message.
{exit("Error in the SQL");}


Code that is being ported from C#

DateRange d = new DateRange(duedate, DateTime.Today); //Set Vars duedate && datatime OUTPUT: DateTime.Today{13/05/2016 00:00:00} duedate{09/05/2016 00:00:00}

DatabaseDataContext Database = new DatabaseDataContext();
var v = from a in Database.LatePaymentRates select a;
decimal interest = 0;
foreach (LatePaymentRate l in v)
{
DateRange ld = new DateRange(l.StartDate, l.EndDate); // $l->$StartDate, $l->$EndDate from database output vars EndDate{01/01/2012 00:00:00} StartDate{01/07/2011 00:00:00}
if (DateRange.intersects(d, ld))
{
decimal dayrate = l.InterestRate * balance / 36500; //input 300 bal and $l->InterestRate from database output var is set to 8.5
interest += DateRange.DateIntersection(d, ld).getDuration().Days * dayrate; //input 300 bal dayrate 0.0698630136986301369863013699 and interest 0 and $l->InterestRate is set to 8.5
} //dayrate 0.0698630136986301369863013699 interest 0.2794520547945205479452054796M
}
$LatePaymentInterest = $interest;

Answer

Do we really need to spell this one out? I feel like I must be missing something...

SELECT * FROM my_table WHERE '2016-05-20' BETWEEN startdate AND enddate;
Comments