title: PHP / mySQL Date/Time calculations (OTRS)
body: <p>I'm trying to put together a couple of PHP pages to act as a dashboard for OTRS. The idea is to output it on a display in the office to show tickets that are approaching first response and update escalations. I'm not a dev so struggling through this so far. I've managed to get it outputting only tickets that have an escalation time set...great! however I want to show the time until escalation. This is where I'm absolutely clueless. I've tried muddling my way through php/mysql guides without a great deal of luck.</p>
  
  <p>I've included my code so far...in not only a hope that someone could point me in the direction for adding a time to escalation column (which I'm guessing involves manipulating the escalation_update_time (Unix) in accordance with the current date/time?), but also that it may help someone do the same thing I'm trying to achieve...which would be pretty useful! (obv changed IP/username/pass).</p>
  
  <p>So to summarize; ideally the things I want to add to the script below are:</p>
  
  <p>The ability to display the escalation time in standard date/time format (rather than Unix)
  The ability to display a countdown until escalation, e.g. 3 hours 15 mins, or 2 days 3 hours 14mins, etc for each ticket</p>
  
  <p>This isn't specific to OTRS I guess, and applies to any mySQL/PHP environment, so hopefully there's a whizz kid out there that understands this stuff!</p>
  
  <p>Thank you in advance for any guidance or help anyone may be able to offer :)</p>
  
  <pre><code>&lt;html&gt;&lt;head&gt;&lt;title&gt;OTRS - First Contact&lt;/title&gt;
  &lt;link rel="stylesheet" type="text/css" href="styles.css" /&gt;
  &lt;/head&gt;&lt;body&gt;
  &lt;?php
  $db_host = '192.168.1.254';
  $db_user = 'myuser';
  $db_pwd = 'mypassword';
  
  $database = 'otrs';
  $table = 'users';
  
  if (!mysql_connect($db_host, $db_user, $db_pwd))
  die("Can't connect to database");
  
  if (!mysql_select_db($database))
  die("Can't select database");
  
  // sending query
  $result = mysql_query("SELECT  q.name AS queue_name, t.tn, t.customer_id,a .a_from,      t.title, a.create_time, create_time_unix, escalation_update_time
  FROM    queue AS q, ticket AS t, article AS a, (
          SELECT  _a.ticket_id, MAX(_a.create_time) AS create_time
          FROM    ticket AS _t, ticket_state AS _t_s, article AS _a
          WHERE   _t_s.name IN ('new', 'open')
          AND     _t.ticket_state_id = _t_s.id
          AND     _a.ticket_id = _t.id            
          GROUP   BY _a.ticket_id
      ) a_max
  WHERE   q.id = t.queue_id
  AND     t.id = a_max.ticket_id
  AND     a.create_time = a_max.create_time
  AND     q.name = 'Service Desk'
  AND      escalation_response_time &gt;0
  GROUP   BY t.id
  ORDER   BY a.create_time ASC");
  if (!$result) {
  die("Query to show fields from table failed");
  }
  
  $fields_num = mysql_num_fields($result);
  
  echo "&lt;h1&gt;Ticket Escalations (First Contact)&lt;/h1&gt;";
  echo "&lt;table border='1'&gt;&lt;tr&gt;";
  // printing table headers
  
  echo "&lt;td&gt;Queue&lt;/td&gt;";
  echo "&lt;td&gt;Ticket#&lt;/td&gt;";
  echo "&lt;td&gt;Store&lt;/td&gt;";
  echo "&lt;td&gt;Customer&lt;/td&gt;";
  echo "&lt;td&gt;Subject&lt;/td&gt;";
  echo "&lt;td&gt;Last Update&lt;/td&gt;";
  echo "&lt;td&gt;Created Time (Unix)&lt;/td&gt;";
  echo "&lt;td&gt;Escalation Time&lt;/td&gt;";
  
  echo "&lt;/tr&gt;\n";
  // printing table rows
  
  while($row = mysql_fetch_row($result))
  {
  echo "&lt;tr&gt;";
  
  // $row is array... foreach( .. ) puts every element
  // of $row to $cell variable
  foreach($row as $cell)
      echo "&lt;td&gt;$cell&lt;/td&gt;";   
  echo "&lt;/tr&gt;\n";
  }
  mysql_free_result($result);
  ?&gt;
  &lt;/body&gt;&lt;/html&gt;
  </code></pre>