select from table synthax error near table name

Question

hello

I'm trying to access a tabel but I'm getting the following error and was sitting hours in front of the code and just can't see the problem.

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 156 [code] => 156 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'user'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'user'. )

$sql = 'SELECT nick FROM user WHERE nick = (?)'; 
		$params = array($nick);
		$result = sqlsrv_query($conn, $sql, $params);
		if ($result == false)
		{
			$nick;
			echo "searching username in DB";
			die (print_r(sqlsrv_errors(),true));	
		}

I also tried to change the "(?)" inb the select statement to the variabel $nick which caused the same error.

I used code that was written for PHP4 and mysql. Now it's supposed to run on Windows with php 5.3 connecting to SQL2012.

The connection to the DB does not give any error and if I change the column name and enter a column name that doesn't exist I'm getting the error message that the column wasn't found.

I kinda feel dumb, shouldn't be that hard but I'm just not seeing the problem there. Any advice?

Answer 1

The word user is a reserved word in T-SQL: http://msdn.microsoft.com/en-us/library/ms189822.aspx

Try writing your query like this:

$sql = 'SELECT nick FROM [user] WHERE nick = (?)';

Notice the square brackets around user. That should work.

Hope that helps.

-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 2

I get the same error with the brackes. Interesting:

If I add the brackets and change the "(?)" in the select statement to $nick I'm getting a new error message:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 126 [code] => 126 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid pseudocolumn "$user". [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid pseudocolumn "$user". ) )

Answer 3

Hmmm. I've created a table called user with a column called nick. This code executes without error and returns the expected results:

$nick = "blah";
$sql = 'select nick from [user] WHERE nick=(?)';
$params = array($nick);
$stmt = sqlsrv_query($conn, $sql, $params);
if($stmt === false)
 die(print_r(sqlsrv_errors(), true)); 
 
while($row = sqlsrv_fetch_array($stmt))
{
 print_r($row);
}

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 4

doh... I got a second sql statment later in the code which caused the same error message as the first one. 

After I put the brackets in the statment I posted it was working. I just didn't notice that the same error message i recieved came from another statment with the same problem. So your first post was right the missing brackets were the problem. Sorry for the additional work you had and thanks for your support. Building a table and the code was actually far more work on your side then I expected anyone to do :D

I noticed that after I exchanged my code with yours and still got the same error.