Hello Daniweb,

I hae built a time/date counter for a private ticketing/support system, but I have run into a bug. After submitting a ticket it's fine, the counter starts counting, Perfect.

Update the ticket to Closed/Answered/Anything else that changes the Status of the ticket, it "resets" the counter.

It pulls data from the MySQL Database that's auto created when someone submits a ticket as:

"YEAR-MONTH-DAY HOUR:MINUTE:SECONDS"

Upon the update the script only changes one section of it and does not update the timestamp.

SO a timestamp with: "2013-04-13 06:46:51" comes back as 0 Days, 0 hours, x Minutes, x seconds" even if it's been sitting there for 2 hours.

Code below:

unset($start_date);
                  unset($date);
                  unset($dt);
                  unset($sdate);
                  unset($st_date);
                  unset($st_time);
                  unset($td_time);
                  unset($td_date);
                  unset($difference);
                    $start_date = $row['submitDate'];
                    date_default_timezone_set("Australia/Perth");

                    $date = date('Y-m-d G:i:s');

                    $dt = explode(" ", $date);
                    $sdate = explode(" ", $start_date);

                    $st_date = $dt[0]; // Current Date (TODAY)
                    $st_time = $sdate[0]; // Database Date - When submitted.
                    $td_date = $dt[1]; // Current Time (TODAY)
                    $td_time = $sdate[1]; // Database Time - When submitted.

                    define("SECONDS_PER_HOUR", 60*60);

                    $td_date = strtotime($td_date);
                    $td_time = strtotime($td_time);
                    $difference = $td_date - $td_time;


                    $hoursDiff = round($difference / SECONDS_PER_HOUR, 0, PHP_ROUND_HALF_DOWN);

                    $minutesDiffRemainder = ($difference % SECONDS_PER_HOUR) / 60;

                    $minutesFinal = explode(".", $minutesDiffRemainder);


                    $diff = abs(strtotime($st_time) - strtotime($st_date));
                    $years = floor($diff / (365 * 60 * 60 * 24));
                    $months = ceil(($diff - ($years * 365 * 60 * 60 * 24)) / ((365 * 60 * 60 * 24) / 12));
                    $months2 = floor(($diff - ($years * 365 * 60 * 60 * 24)) / ((365 * 60 * 60 * 24) / 12));
                    $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months2 * 30 * 60 * 60 * 24)/ (60 * 60 * 24));

                    echo  "<strong>" . $days . " </strong> Days <strong>" . $hoursDiff . "</strong> Hours <strong>" . $minutesFinal[0] . " </strong>Minutes";

This all looks unnecesarily complicated (plus you don't show your db queries so we can see where the problem may be occuring) - have you looked at using PHP's DateTime class / functions?

$query = mysql_query("SELECT * FROM tickets WHERE sender='$username' AND currentStatus='Open' ORDER BY field(currentStatus, 'Closed','Close','Answered','Answer','Open','In Progress','Resolved','Spam')  DESC") or die(mysql_error());

That's the SQL database querie then:

while($row = mysql_fetch_array($query)) {

echo $row[''];
}

I checked the MySQL database and I stuffed up there, it would update the database when ever you "updated" anything in there, That's been fixed and so has this issue.

Member Avatar
diafol

Don't use maths to calculate datetime parts - they may fall down. Use the DateTime object as mentioned. Either that or use SQL built-in functions for date diffs.