How to display time countdown using PHP and MySQL

Question

How do I get a time countdown using PHP?

I want to display something like 3Days 4Hours. I have a date field coming from MySQL table and want to calculate it with today's date. As of now I have only date and not the time stored in the database, but eventually I will. So at that time, I might show as 3Days 4Hours 10Minutes 43seconds.

This is what I tried but am getting some wrong answer:

$datetime1 = new DateTime($starton);//$starton - date stored in db
$datetime2 = new DateTime(date());
$interval = $datetime1->diff($datetime2);
echo $interval->format('%d days);

I am confused if this works based of server time or the zone where the user is coming from. Please guide me. When I have the time field, I guess I might need jQuery to show the seconds live and so the minutes too.

Answer 1

IMHO when you are thinking about comparing time, you automatically start talking Unix time. Essentially, Unix time is a count in seconds that always increments. When you have 2 Unix timestamps, then you can use simple arithmetic to figure the difference and then translate the difference into human readable form.

Note that Unix timestamps are common in almost all programming languages, so you can choose whether to do this in PHP, MySQL or JavaScript and it could all turn out the same way. I'll show you the PHP version.

So you want to do this:

1. Find the Unix timestamp for your target event
2. Store that in the MySQL database
3. Retrieve from the database
4. Get current Unix timestamp
5. Subtract to get difference
6. Multiply/divide to get human readable format

The PHP code:

//Unix timestamp to Dec. 21, 2012 (midnight)
$unix_time = mktime(0,0,0,12,21,2012); 
//Connect to MySQL
//"INSERT INTO table (target_timestamp) VALUES ($unix_time);"


//----- user goes to page -----
//Connect to MySQL
$sql = "SELECT target_timestamp FROM table WHERE foo = bar";
$unix_time = do_query($sql);//do_query not defined, assume it gets the timestamp
$current_time = time();
$diff = $unix_time - $current_time
//$diff should be positive and not 0
if( 1 > $diff ){
   exit('Target Event Already Passed (or is passing this very instant)');
} else {
   $w = $diff / 86400 / 7;
   $d = $diff / 86400 % 7;
   $h = $diff / 3600 % 24;
   $m = $diff / 60 % 60; 
   $s = $diff % 60;

   return "{$w} weeks, {$d} days, {$h} hours, {$m} minutes and {$s} secs away!"
}