CODEWITHSUNDEEP
phpsqlsql-server
armani
armani

Reputation: 123

Showing MS SQL column named "TIME" in PHP

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:

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.

Upvotes: 0

Views: 180

Answers (2)

armani
armani

Reputation: 123

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')

Upvotes: 0

patsweet
patsweet

Reputation: 1558

TIME is a reserved word.

Try quoting it or wrapping it in brackets like so:

SELECT "TIME"...

or

SELECT [TIME]

Upvotes: 1

Related Questions