使用奇怪的表名运行查询到MS SQL服务器

Question

For an application we need to connect to an SQL Server 2008 to do some queries.

I run the queries in PHP on an Codeigniter Framework. I get access to this SQL database by an program called "Microsoft SQL server management studio". The tables have strange names with dollar signs: TRP$Lease Car for example.

The studio get data by this query:

SELECT TOP 1000 *
  FROM [Mdatabase].[dbo].[TRP$Lease Car]

When I run this query in my PHP installation it fails on the $ sign:

$data = $this->db->query("SELECT * FROM dbo.TRP$Lease Car");

Like this:

A PHP Error was encountered

Severity: Notice

Message: Undefined variable: Lease

Filename: views/welcome_message.php

Line Number: 5 A Database Error Occurred

Error Number: 42S02

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbo.TRP'.

How do I need to call these tables when running an query from my PHP?

EDIT

The problem only appears on tables with an space in the name... An query with TRP$Invoice works. In this case TRP$Lease Car is the problem I think..

Answer 1

This is due to the fact that you have your query in double-quotes. When PHP encounters a double-quoted string containing $something, it will treat $something as a variable and attempt to substitute its value. In this case, you have TPR$Lease in your string - PHP attempts to resolve variable $Lease - which you haven't defined - and gives you the warning message you indicated. There are two ways of dealing with it.

1. Escape the dollar sign: "select * from TPR\$Lease" - thus telling PHP that you really do need the dollar sign there.

2. Use single-quoted string: 'select * from TPR$Lease' - php will not do variable substitution in single-quoted strings.

Obviously, adjust the actual SQL statements according to your specific schema.

EDIT: if your table name contains spaces, then you have to use square brackets [ ... ] around the tabla name:

"select * from [TPR\$Lease Car]"

Note however that this is MS SQL specific syntax. This will not work on most other database engines, as (to my knowledge) only MS allows spaces in table names.