Untitled

query of time in sql table incorrect due to varied number of digits

date_default_timezone_set('America/Denver');

$today = date("Ymd");

mysql_select_db($dbname);

$query = sprintf("SELECT cal_name, cal_time, cal_description, cal_date FROM webcal_entry WHERE cal_date = '".$today."'");

$result = mysql_query($query);

$body = '';

$mail = '';

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

    $mail = sprintf("Event: %s nTime: %s nDesc: %s nn", $row[0], date("H:i",((strtotime($row[1])-25200))), $row[2]);

}

mysql_free_result($result);

       

date_default_timezone_set('America/Denver');

$today = date("Ymd");

mysql_select_db($dbname);

$query = sprintf("SELECT cal_name, cal_time, cal_description, cal_date FROM webcal_entry WHERE cal_date = '".$today."'");

$result = mysql_query($query);

$body = '';

$mail = '';

$stt = '';

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

    $mail = sprintf("Event: %s nTime: %s nDesc: %s nn", $row[0], ($stt = { if ($row[1] < 100000, + 1234500000) ELSE ($row[1] + 1234000000) }) date("H:i",((strtotime($stt)-25200))), $row[2])

};

       

/* I assume that the times and dates in your database are stored in UTC,

     so set the UTC timezone first. */

date_default_timezone_set('UTC');

 

/* $today might now contain a value of tomorrow if it's past 17:00 in your timezone.

     This is required though, as there could be an event at e.g. 18:00 of your time which would

     not be returned otherwise. sprintf is not needed, as you don't format anyting here */

$today = date("Ymd");

$query = "SELECT cal_name, cal_time, cal_description, cal_date FROM webcal_entry WHERE cal_date = " . $today;

 

mysql_select_db($dbname);

$result = mysql_query($query);

 

$body = '';

$mail = '';

 

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

    // Store the value of the returned time in a $fixedTime

    $fixedTime = $row[1];

 

    /* Simple approach: While the length of the time is less than 6 digits, prepend a leading 0

         This will even work for times like 00:00:15 */

    while (strlen($fixedTime) < 6)

        $fixedTime = '0' . $fixedTime;

 

    // Now store the unix timestamp (still in UTC!)

    $unixEpoch = strtotime($fixedTime);

 

    // Set the timezone to your local time ...

    date_default_timezone_set('America/Denver');

 

    // ... and $formattedTime receives the formatted time value in your timezone

    $formattedTime = date("H:i", $unixEpoch);

 

    // Now create the mail content

$mail .= sprintf("Event: %s nTime: %s nDesc: %s nn", $row[0], $formattedTime, $row[2]);

}

 

mysql_free_result($result);