[RESOLVED] Help needed to find the "time left"-the difference between "tomorrow time" & Sysdate

[thegreatdanton]

Hi, I need a little help please with my php. Basically what i'm trying to do is find out the difference between a future date/time and the current date/time. I have the following printing out on screen at the moment:

Two columns -
1. The first one is called "Tomorrow Time" which is basically a timestamp + 24hours. This shows the time + 24 from when the data entered the database.

2. The second one is called "Current Time" and this obviously shows the current date/time as per the system.

Here is the code that is printing out the two columns on screen:

			<?php
			$con = mysql_connect("xxx","xxx","xxx");
			if (!$con)
			 {
			 die('Could not connect: ' . mysql_error());
			 }

		mysql_select_db("xxx", $con);

		$result = mysql_query("SELECT tomorrowtime, SYSDATE() FROM proposal");

		echo "<table id='myTable' cellpadding='0'>
		<thead>
		<th axis='string'>Tomorrow Time</th>
		<th axis='string'>Current Time</th>
		</thead>";


		while($row = mysql_fetch_array($result))
		 {
		 echo "<tr>";
			echo "<td>" . $row['tomorrowtime'] . "</td>";
			echo "<td>" . $row['SYSDATE()'] . "</td>";
		echo "</tr>";
			echo "</tbody>";
		 }
		echo "</table>";

		mysql_close($con);
		?> 

Please note that tomorrowtime is a row in the MySQL proposal table. What i want to do is have a third column called "Time Left" which will basically show the difference between the Current Time (SYSDATE) and the Tomorrow Time. Can anyone please advise how i can print this out in the third column.

Any advice would be greatly appreciated.

Thank you

[ixalmida]

$result = mysql_query("SELECT tomorrowtime, SYSDATE(), 
  TIMEDIFF(tomorrowtime, SYSDATE()) AS timeleft FROM proposal"); 

[thegreatdanton]

Thank you for your response. That works. I've just one more question. Would it be possible at all to echo the time left in the format: 3 hours 24 minutes because at the moment it is printing out as follows: 03:24:35?

Thank you

[ixalmida]

$result = mysql_query("SELECT tomorrowtime, SYSDATE(), 
  TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours %l minutes') AS timeleft
  FROM proposal");

[thegreatdanton]

Hi ixalmida,

thanks again for replying. You're help is very much appreciated. It's printing out in the format of 3 hours 24 minutes rather than 03:24:35. However it doesn't appear to be working correctly as when i refresh the page after a few minutes it continues to say 3 hours 24 minutes, whereas when it was in the "03:24:35" format the time would reduce when i refreshed the page.

Do anyone have any idea why this is?

Thank you

[thegreatdanton]

I have it working now. There was a small problem with the "i".

The correct code is:

$result = mysql_query("SELECT tomorrowtime, SYSDATE(), 
  TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes and %s seconds') AS timeleft 
  FROM proposal");

Thank you again for your help