douju7503
douju7503
2014-04-03 22:11

在PHP中显示名为“TIME”的MS SQL列

已采纳

Some vendor made one of the columns on one of our systems "TIME" which holds date and time information. I'm using PHP's SQLSRV to connect to the database to display some of the table, and for some reason I always get blank output whenever the TIME column is included in the SQL statement.

This code produces nothing but "End of file" (proving it successfully reaches the end without error) -- EDIT: IT ACTUALLY DOESN'T DISPLAY END OF FILE, SO IT ERRORS OUT ON MY TIME PORTION OF THE SELECT STATEMENT:

<?php
   $serverName = "DatabaseServer";
   $connectionInfo = array("Database"=>"TheDatabase", "UID"=>"Username", "PWD"=>"Password");
   $conn = sqlsrv_connect($serverName, $connectionInfo);
   if($conn) {
      $sql = "SELECT TIME, AnotherColumn FROM Table";
      $query = sqlsrv_query($conn, $sql);
      if($query === false) die(print_r(sqlsrv_errors(), true));
      while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_NUMERIC)) {
         echo $row[0] . "<br />";
      }
   } else {
      echo "Connection failed.<br /><br />";
      die(print_r(sqlsrv_errors(), true));
   }
   echo "<br />End of file";
?>

If I change:

  • SELECT TIME to SELECT TIME AS 'something'
  • SQLSRV_FETCH_NUMERIC to SQLSRV_FETCH_ASSOC
  • echo $row[0] to echo $row['something']

it still doesn't work.

However, if I simply call SELECT AnotherColumn, YetAnotherColumn (any columns besides TIME) then everything gets displayed fine. I don't have admin privileges over this server to change the column names; is there anything I can do? My phpinfo() tells me I'm running version 5.3.6 on a Windows Server 2008 R2 x64 server. The database server is also 2008 R2 and running SQL Server 2008 R2.

EDIT: I'm not sure about the issue stemming from TIME being a reserved word. I just changed the column name in SQL Management Studio and changed my statement to reflect it (now called TIMEColumn) and had the same issue! The only thing different about that column is that it is the Primary Key and not allowed to be null.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongshang6062 dongshang6062 7年前

    Finally figured it out! Per a series of searches that led me HERE I found out that the MS SQLSRV Extension for PHP treats data returned from a DateTime field not as a string but as a native DateTime object. I was able to at least get the UNIX time from it by echo $row[0]->getTimestamp();

    EDIT: And in "normal" format via echo $row[0]->format('Y-m-d H:i')

    点赞 评论 复制链接分享
  • duanluan2047 duanluan2047 7年前

    TIME is a reserved word.

    Try quoting it or wrapping it in brackets like so:

    SELECT "TIME"...
    

    or

    SELECT [TIME]
    
    点赞 评论 复制链接分享

为你推荐