0

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";
3
Contributors
4
Replies
27
Views
4 Years
Discussion Span
Last Post by diafol
0

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?

0
$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[''];
}
0

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.

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.