Querying SQLServer 2008 R2 Database

Beginners - Learning PHP
#1

Hi Professionals

I have installed php successfully i think as I can display a hello world with a php file.

The problem I am encountering is that I am trying to query the SQLServer database and it just gets as far as “Error connecting to SQL Server”

any ideas, here is the code

<?php $serverName = "d3licsql02"; $database = "TestData"; // Get UID and PWD from variables. $uid = "sa"; $pwd = "secret"; echo $serverName; echo "
"; echo $database; echo "
"; echo $uid; echo "
"; echo $pwd; echo "
"; try { $conn = new PDO( "sqlsrv:server=$serverName;Database = $database", $uid, $pwd); $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch( PDOException $e ) { die( "Error connecting to SQL Server" ); } echo "Connected to SQL Server\n"; $query = 'select * from Postcodes.Suburb'; $stmt = $conn->query( $query ); while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){ print_r( $row ); } // Free statement and connection resources. $stmt = null; $conn = null; ?>
#2

Have you added the PDO_SQLSRV extension?

http://php.net/manual/en/ref.pdo-sqlsrv.php

“The PDO_SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file.”

#3

Thanks and ok I have dowloaded the SQLSRV30 exe and clicked this went through the setup process and specified the ext directory.

Still confused as to what to do next as its still the same error when i try my script

#4

oh and which is the appropriate dll file

#5

I’m not entirely sure. What kind of server are you running?

From the manual:

“If you are running non-thread-safe PHP (PHP 5.3), use the php_pdo_sqlsrv_53_nts.dll file. (You should use a non-thread-safe version if you are using IIS as your web server). If you are running thread-safe PHP, use the php_pdo_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_pdo_sqlsrv_54_nts.dll or php_pdo_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.”

In your php.ini file you’ll see a section for extensions “Dynamic Extensions”

This is where you would add the appropriate dll files. So for example if you are using PHP5.3 you could add

extension=php_pdo_sqlsrv_53_nts.dll

#6

Also I have to wonder if this is a correct hostname of if you modified it for posting here?

$serverName = “d3licsql02”

#7

ok I think I have the correct host name I have also done what you said and I have also changed the code to see if i can log on and this is what I get

got server namegot db namegot connectionConnection couldn’t be established. Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘NT AUTHORITY\IUSR’. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘NT AUTHORITY\IUSR’. ) [1] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘NT AUTHORITY\IUSR’. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘NT AUTHORITY\IUSR’. ) )

code is as follows

<?php $server='d3licsql02'; echo "got server name"; $connectinfo=array("Database"=>"TestData"); //connect to DB echo "got db name"; $db=sqlsrv_connect($server,$connectinfo); echo "got connection"; if($db) { echo "Connection established. "; } else { echo "Connection couldn't be established. "; die(print_r( sqlsrv_errors(), true)); } ?>

I can switch back to the original code if necessary[/code]

#8

I noticed in your test you didn’t specify UID/PWD

[php]$connectinfo=array(“Database”=>“TestData”, “UID” => “sa”, “PWD” => “secret”);[/php]

#9

Matt

you are a star this now works thanks very much

just one more thing if you dont mind seeing as im new to php

how to I pull and display suburb and state out of the postcode table

thanks
Alan

#10

I’m not sure if I can answer that without seeing the table structures.

Is this a correct query?

select * from Postcodes.Suburb

OR is Suburb a column in table Postcodes? e.g.

select Suburb from Postcodes
#11

I have this, the two columns in the postcodes table in the TestData database but I do not know how to display the results with php

$tsql = "SELECT suburb,state

           FROM TestData.Postcodes";

eg

Suburb State
cornubia QLD
newcastle NSW
toowong QLD

#12

What is your current code? Did you go back to PDO?

#13

Hi Matt

No i did not go back to pdo but I managed to get it working this way

<?php $server='d3licsql02'; $connectinfo=array("Database"=>"TestData", "UID" => "sa", "PWD" => "secret"); //connect to DB $conn=sqlsrv_connect($server,$connectinfo); if( $conn === false ) { die( print_r( sqlsrv_errors(), true)); } $sql = "SELECT software_manufacturer, product,product_version,path FROM softusecomp"; $stmt = sqlsrv_query( $conn, $sql); if( $stmt === false) { die( print_r( sqlsrv_errors(), true) ); } while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) { echo $row['software_manufacturer'].", ".$row['product'].", ".$row['product_version'].", ".$row['path']."
"; } sqlsrv_free_stmt( $stmt); ?>
#14

Looks good to me.

I still recommend PDO. It would allow you to use the same PHP code with a MySQL database for example.

Sponsor our Newsletter | Privacy Policy | Terms of Service