Connecting PHP to MS SQL Server

Question

<?php
$serverName = "(local)"; //serverName
$connectionInfo = array( "Database"=>"DabaseNew", "UID"=>"sa", "PWD"=>"*****");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn==true ) {
    echo "Connection established.<br />";
}else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT * FROM Dbo.DATABASE COMPANY SERVICES$Employee";
$stmt = sqlsrv_query( $conn, $sql);
if(!$stmt){
    die( print_r( sqlsrv_errors(), true));
}
$rows = sqlsrv_has_rows($stmt);
while($obj = sqlsrv_fetch_object( $stmt)){
    echo $obj->Description.", ".$obj->lName."<br />";
}
?>

I am trying to connect to php to my sql server using sqlsrv_connect. The above code gives me an error below;

Output: Connection established. Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SERVICES'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SERVICES'. ) )

SELECT * FROM Dbo.DATABASE COMPANY SERVICES$Employee Is that query ? — Abdullah
– Abdullah, Commented Jul 1, 2014 at 13:59
What do you want to select from which database under which conditions? — Daniel
– Daniel, Commented Jul 1, 2014 at 14:00
I think you meant: $sql = 'SELECT * FROM Dbo.[DATABASE COMPANY SERVICES$Employee]'; if your table name is DATABASE COMPANY SERVICES$Employee — sridesmet
– sridesmet, Commented Jul 1, 2014 at 14:11

Gerdinand · Accepted Answer · 2014-07-01 14:05:51Z

1

If there are spaces in the tablename you should run the query like this: $sql = "SELECT * FROM dbo.[DATABASE COMPANY SERVICES]"; Not sure what you are trying to do with $employee, because php sees it as a variable and tries to paste fill it in there (I think here $employee being NULL).

answered Jul 1, 2014 at 14:05

Gerdinand

13610 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerdinand Over a year ago

If however $employee is the name of the employee you want to get from the database you should use $sql = "SELECT * FROM dbo.[DATABASE COMPANY SERVICES] WHERE [Last Name] = '$employee'";

sridesmet · Accepted Answer · 2014-07-01 14:24:37Z

1

Try to switch

$sql = "SELECT * FROM Dbo.DATABASE COMPANY SERVICES$Employee";

with

$sql = 'SELECT * FROM Dbo.[DATABASE COMPANY SERVICES$Employee]';

Because php thinks $Employee is a variable if you use double quotes. The [] is used to tell the database that the table name is DATABASE COMPANY SERVICES$Employee otherwise the space will indicate the start of another sql command or variable.

Try not to use spaces in table names btw, it avoids confusion.

edited Jul 1, 2014 at 14:24

answered Jul 1, 2014 at 14:14

sridesmet

8959 silver badges20 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:05:43Z

0

You need to escape the $ character with \$, as php treats it as first character ov a variable. Try this:

$sql = "SELECT * FROM Dbo.[DATABASE COMPANY SERVICES\$Employee]";

EDIT:

To avoid escapingg you could also use single quotes ' instead of double quotes ". Then PHP does not resolve variables within the string. (see this question)

$sql = 'SELECT * FROM Dbo.[DATABASE COMPANY SERVICES$Employee]';

2nd EDIT:

To concatenate two strings use . operator like this:

$foo = "Hello ";
$bar = $foo."world!"; // gives "Hello world!"

As you can read within the answer linked within the first edit " double quotes resolve variables inbetween, while ' single quotes don't. your possible solution could be like this:

$query = 'SELECT [First Name] AS firstName, [Last Name] AS lastName
            FROM  Dbo.[DATABASE COMPANY SERVICES$Employee]
            WHERE [Employee Number] = 15 OR [E-Mail] = \''.mssql_escape($mail).'\'';

But you should NEVER directly send a GET parameter top your sql server. Anybody could infiltrate your database or even delete it. Therefore you should add a escape function like this one or consider using another db-library like PDO and build parameterized queries. It might me sufficient to escape single quotes within the variable with another single quote like this:

function mssql_escape($str) {
    return str_replace("'", "''", $str);
}

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jul 1, 2014 at 14:08

Daniel

3,5243 gold badges36 silver badges48 bronze badges

9 Comments

Daniel Over a year ago

I added another possible solution(Edit)

user3315848 Over a year ago

Array ( [0] => Array ( [0] => 42S02 [SQLSTATE] => 42S02 [1] => 208 [code] => 208 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbo.DATABASE COMPANY SERVICES$Employee'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbo.DATABASE COMPANY SERVICES$Employee'. ) )

user3315848 Over a year ago

I changed the double quotes with the single quotes and its worked. Thanks Daniel

Daniel Over a year ago

Both provided solutions should result in exactly the same manner.

user3315848 Over a year ago

My next problem now are the columns which have spaces between them. For example First Name and Last Name as in ($rows = sqlsrv_has_rows($stmt); while( $obj = sqlsrv_fetch_object( $stmt)) { echo $obj->First Name.", ".$obj->Last Name."<br />"; })

|

Collectives™ on Stack Overflow

Connecting PHP to MS SQL Server

3 Answers 3

1 Comment

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related