Days left to certain date. Data from MySQL

alfreds · #1 (**permalink**) 06-17-03, 08:51 AM

Hello everyone,

I have a problem of calculating the number of days left till the date (timestamp(8)) acquired from database. Here is the script I have written:

PHP Code:


			
$Year = date("Y");

$Month = date("m");

$Day = date("d");



$StopDate = date("Y-m-d", mktime(0,0,0,$Month, ($Day + 20), $Year));

$StartDate = date("Y-m-d");

    

    $query = "SELECT DATE_FORMAT(datums, '%d.%m.%Y') as date, notikums, id FROM kalendars WHERE ((datums >= '$StartDate') AND (datums <= '$StopDate')) ORDER BY datums ASC";

    $result = mysql_query($query);

    echo ("<table width='100%' border='0' cellspacing='0' cellpadding='4' class='txtbody'>");

    $Today = date("d.m.Y");    

        while ($rows = mysql_fetch_array($result))

        {

        $d = $rows["date"];

        $n = $rows["notikums"];

        $left = $rows["date"] - $Today;

        if ($left == 1) {

            $kad = "(Tomorrow)";

            } else {

            $kad = "(After $left days)";}

        if ($left == 0) {

            $kad = "(Today)";}

        echo ("<tr><td bgcolor=\"#F4F4F4\"><b>Date: $d<br/> $kad</b><font color=\"gray\"></font></td></tr><tr><td>$n<br/></td></tr>");

        }

The time interval in what the query works is 20 days.
The output is like this:

Date:[28.06.2003]
(After 11 days)
28.-29.06., EÈ autokrosâ v/a, 5. posms, Vâcija, Seelow

Date:[05.07.2003]
(After -12 days)
05.-06.07., EÈ autokrosâ v/a, 6.posms, Èehija, Nova Paka

Date:[05.07.2003]
(After -12 days)
05.-06.07., EÈ rallijkrosâ v/a, 5.posms, Zviedrija, Holjesbanan

Date:[06.07.2003]
(After -11 days)
LÈ autokrosâ v/a, 3. posms, Tukums, "Melnezers"

What is wrong with substracting two date formats?

!!! HotCGIScripts !!! · #2 (**permalink**) 06-17-03, 09:03 AM

You could not operate with dates in this way. What you do:
20.06.2003 - 17.06.2003 = 3 ok
but
01.07.2003 - 17.06.2003 = -16
why? because PHP get string '01.07.2003' and translate it into INT for first not number char so this is 1 for '01.07.2003' and 17 for 17.06.2003.

So you should calculate days pediod in SQL query.

$query = "SELECT DATE_FORMAT(datums, '%d.%m.%Y') as date,
to_days(datums - current_date) as period,notikums, id FROM kalendars WHERE ((datums >= '$StartDate') AND (datums <= '$StopDate')) ORDER BY datums ASC";