D3nj1 D3nj1 - 3 years ago 185
MySQL Question

Loop through Dates in database and calculate days remaining to the date on each row

I am trying to figure out how to loop through the database and calculate the remaining days untill the individual date per row.

Date format 6/15/2017 m/d/y but printed it shows y/m/d as show later in the question. (Currently sitting as varchar in the DB, this will be changed to DATE)

Stack info: Xampp 5.6.30 (Apache + MariaDB + PHP + Perl) I don't use Perl.

I have gotten as far as getting an array with all the dates.

This is the code that gets the array:
$dates = array();

$Kal_get = "SELECT Next_Kal FROM Maaleinstrumenter_final";
$Result = mysqli_query($conn, $Kal_get);
WHILE ($Row = mysqli_fetch_assoc($Result))

Furthermore i have succeeded in calculating 1 row with (The date is not the right one and should be filled with data from $Row)

$date = strtotime("December 3, 2009");
$remaining = $date - time();
WHILE($day = floor($remaining / 86400));
Echo $day;

The 2 scripts are not joined together as i tried to make a variable that would loop the calculation but it looped endlessly. This might be fixable by nesting it in an if statement.

How would i proceed as to join the 2 scripts and get 300+ results and devide them into 3 groups (more than a month, less than a month and less than 10 days)

Things that i have observed:
The wile loop that assembles the array somehow shows up as 360+ arrays as showed here with a 2 row var_dump on $Row and not as 1 array like
$x['1'], $x['2']
and so on:

array(1) { ["Next_Kal"]=> string(10) "1993-12-12" }
array(1) { ["Next_Kal"]=> string(10) "0000-00-00" }

I goofed earlier on and reset all the data on the dates and i changed the first row to show an example.

Answer Source

I kept tinkering since it wouldn't fit in perfectly, this is what i ended up with as a successful script for calculating dates remaining on 366 rows.

$Kal_get = "SELECT Bunny_No, Next_Kal, DATEDIFF(NOW(), Next_Kal) FROM Maaleinstrumenter_final";
$Result = mysqli_query($conn, $Kal_get);
$a = 30;
$b = 10;
$moreThan2Months = array();
$lessThan1Month = array();
$lessThan10Days = array();

while($Row = mysqli_fetch_array($Result))
$absoluteDays = abs($Row['DATEDIFF(NOW(), Next_Kal)']);
if($absoluteDays > $a) {$moreThan2Months[] = ($Row['DATEDIFF(NOW(), Next_Kal)']);}
elseif($absoluteDays < $a && $absoluteDays >$b) {$lessThan1Month[] = $Row['DATEDIFF(NOW(), Next_Kal)'];}
elseif($absoluteDays < $b) {$lessThan10Days[] = $Row['DATEDIFF(NOW(), Next_Kal)'];}}
echo count(array_filter($lessThan1Month));

It is successfully calculating the dates and put them into $absoluteDays and split them into 3 arrays based on the If/Ifelse statements and then later counted and filtered to only show non-NULL elements inside the array, making it able to count dynamically.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download