How to use PHP to connect to sql server

Question

I want to use PHP to connect to sql server database.

I installed xampp 1.7.0(php 5.2) and SQLSRV20. I've added the extensions in php.ini and I get this error:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to 
server: 10.85.80.229 in C:\xampp\htdocs\xampp\test.php on line 07

Code:

<?php
$myServer = "10.85.80.229";
$myUser = "root";
$myPass = "pass";
$myDB = "testdb";

$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer"); 
?>

What does this error message mean and how do I connect to SQL Server?

i want to connect to mssql ie sql server. i have xampp(mysql,apache) running — maha, Sep 05 '13 at 10:14

score 13 · Answer 1 · answered Apr 10 '14 at 09:42

13

enable mssql in php.ini

;extension=php_mssql.dll

to

extension=php_mssql.dll

answered Apr 10 '14 at 09:42

Agha Umair Ahmed

1,037
7
12

score 12 · Answer 2 · answered Dec 21 '14 at 08:20

<?php  
$serverName = "ServerName"; 
$uid = "sqlusername";   
$pwd = "sqlpassword";  
$databaseName = "DBName"; 

$connectionInfo = array( "UID"=>$uid,                            
                         "PWD"=>$pwd,                            
                         "Database"=>$databaseName); 

/* Connect using SQL Server Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionInfo);  

$tsql = "SELECT id, FirstName, LastName, Email FROM tblContact";  

/* Execute the query. */  

$stmt = sqlsrv_query( $conn, $tsql);  

if ( $stmt )  
{  
     echo "Statement executed.<br>\n";  
}   
else   
{  
     echo "Error in statement execution.\n";  
     die( print_r( sqlsrv_errors(), true));  
}  

/* Iterate through the result set printing a row of data upon each iteration.*/  

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))  
{  
     echo "Col1: ".$row[0]."\n";  
     echo "Col2: ".$row[1]."\n";  
     echo "Col3: ".$row[2]."<br>\n";  
     echo "-----------------<br>\n";  
}  

/* Free statement and connection resources. */  
sqlsrv_free_stmt( $stmt);  
sqlsrv_close( $conn);  
?>

http://robsphp.blogspot.ae/2012/09/how-to-install-microsofts-sql-server.html

score 3 · Answer 3 · edited May 04 '16 at 10:30

if your using sqlsrv_connect you have to download and install MS sql driver for your php. download it here http://www.microsoft.com/en-us/download/details.aspx?id=20098 extract it to your php folder or ext in xampp folder then add this on the end of the line in your php.ini file

extension=php_pdo_sqlsrv_55_ts.dll
extension=php_sqlsrv_55_ts.dll

im using xampp version 5.5 so its name php_pdo_sqlsrv_55_ts.dll & php_sqlsrv_55_ts.dll

if you are using xampp version 5.5 dll files is not included in the link...hope it helps

score 2 · Answer 4 · edited Mar 31 '14 at 19:24

2

Try this code

$serverName = "serverName\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

edited Mar 31 '14 at 19:24

Eric Leschinski

146,994
96
417
335

answered Sep 05 '13 at 09:48

Guru

621
1
4
12

2

Call to undefined function sqlsrv_connect() in C:\xampp\htdocs\xampp\test.php on line 4 – maha Sep 05 '13 at 10:41

hassan javaid · Answer 5 · 2017-05-02T09:06:58.537

2

 $server_name = "your server name";
 $database_name = "your database name";
 try
  {
   $conn = new PDO("sqlsrv:Server=$server_name;Database=$database_name;ConnectionPooling=0", "user_name", "password");
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}
catch(PDOException $e)
{

    $e->getMessage();

}

edited May 02 '17 at 09:06

answered Apr 29 '17 at 05:42

hassan javaid

82
11

how about not using PDO? – Artanis Zeratul Mar 15 '19 at 01:38
By only using PDO you can connect php with sql server. Otherwise you have to use mysql. – hassan javaid Mar 19 '19 at 10:24
Thanks hassan. I tried this http://php.net/manual/en/function.odbc-connect.php and it worked in my case. – Artanis Zeratul Mar 19 '19 at 19:42

score 1 · Answer 6 · answered Sep 05 '13 at 09:32

1

Use localhost instead of your IP address.

e.g,

$myServer = "localhost";

And also double check your mysql username and password.

answered Sep 05 '13 at 09:32

Manish Chauhan

595
2
7
14

i have tried with localhost aswell, also by adding port. but to no fruit – maha Sep 05 '13 at 09:35

score 1 · Answer 7 · answered Sep 05 '13 at 09:41

for further investigation: print out the mssql error message:

$dbhandle = mssql_connect($myServer, $myUser, $myPass) or die("Could not connect to database: ".mssql_get_last_message());

It is also important to specify the port: On MS SQL Server 2000, separate it with a comma:

$myServer = "10.85.80.229:1443";

or

$myServer = "10.85.80.229,1443";

score 1 · Answer 8 · edited Jan 27 '16 at 07:05

1

$dbhandle = sqlsrv_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

Hope it help.

edited Jan 27 '16 at 07:05

Lewis Hai

1,114
10
22

answered Jan 27 '16 at 06:26

nimesh

11
1

score 1 · Answer 9 · answered Sep 26 '20 at 18:22

first download below software https://www.microsoft.com/en-us/download/details.aspx?id=30679 - need to install

https://www.microsoft.com/en-us/download/details.aspx?id=20098 - when you run this software . it will extract dll file. and paste two dll file(php_pdo_sqlsrv_55_ts.dll,extension=php_sqlsrv_55_ts.dll) this location C:\wamp\bin\php\php5.6.40\ext\ (pls make sure your current version)

2)edit php.ini file add below line extension=php_pdo_sqlsrv_55_ts.dll extension=php_sqlsrv_55_ts.dll

Please refer screenshort add dll in your php.ini file

score 1 · Answer 10 · edited Jun 01 '22 at 11:39

MS SQL connect to php

Install the drive from Microsoft website
After install, you will get some files. Store it in your system temp folder
Check your php version, thread or non thread, and window bit - 32 or 64 (Thread or non thread, this is get you by phpinfo())
According to your system & xampp configration (php version and all) copy 2 files (php_sqlsrv & php_pdo_sqlsrv) to xampp/php/ext folder .
Add to php.ini file :

extension=php_sqlsrv_72_ts_x64  
extension=php_pdo_sqlsrv_72_ts_x64

(php_sqlsrv_72_ts_x64.dll and php_pdo_sqlsrv_72_ts_x64.dll are the files which you copied in 4th step)

Next here is the Php Code to connect to database :

$serverName ="DESKTOP-me\\MSSQLSERVER01"; (servername\\instanceName)


// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.

$connectionInfo = array("Database"=>"testdb","CharacterSet"=>"UTF-8");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     //echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

//$sql = "INSERT INTO dbo.master ('name') VALUES ('test')";
$sql = "SELECT *  FROM dbo.master";
$stmt = sqlsrv_query( $conn, $sql );

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      echo $row['id'].", ".$row['name']."<br />";
}

sqlsrv_free_stmt( $stmt);

rjose · Answer 11 · 2022-08-26T05:29:43.617

1

Take a look at the matrix to understand the compatibility of different versions of Windows/SQL Server/PHP drivers.

Download PHP driver dll - https://github.com/microsoft/msphpsql/releases/
Copy the dll files to /ext folder.
Edit php.ini and add dll names under Dynamic Extensions.
Restart the webserver -Apache or IIS

Eg: using 8.1 version with SQL Server 2019.

extension=php_sqlsrv_81_ts
extension=php_pdo_sqlsrv_81_ts

Database Connection test Code - https://www.php.net/manual/en/function.sqlsrv-connect.php

edited Aug 26 '22 at 05:29

answered Aug 01 '22 at 07:34

rjose

557
5
13

Thanks. Can you guide me, how do I install this driver in live server? – Sourav Das Jan 23 '23 at 12:23

score 1 · Answer 12 · answered Aug 12 '22 at 06:32

1

For those who want to connect to database(MSSQL) using Windows Auth, mssql_connect' was removed in the 7.0 PHP version. So you can use this instead;

$serverName="serverName";
$uid = '';
$pwd = "";
$dbase = "nameOfDB";

$conninfro = array("Database"=>$dbase, "UID"=>$uid, "PWD"=>$pwd);

$con = sqlsrv_connect( $serverName, $conninfro);
if( $con === false){
    die( print_r( sqlsrv_errors(), true));
}

answered Aug 12 '22 at 06:32

Mahmut Salman

111
1
10

Thanks. Can you guide me, how do I install this driver in live server? – Sourav Das Jan 23 '23 at 12:22

score 0 · Answer 13 · edited Mar 31 '14 at 19:43

For the following code you have to enable mssql in the php.ini as described at this link: http://www.php.net/manual/en/mssql.installation.php

$myServer = "10.85.80.229";
$myUser = "root";
$myPass = "pass";
$myDB = "testdb";

$conn = mssql_connect($myServer,$myUser,$myPass);
if (!$conn)
{ 
  die('Not connected : ' . mssql_get_last_message());
} 
$db_selected = mssql_select_db($myDB, $conn);
if (!$db_selected) 
{
  die ('Can\'t use db : ' . mssql_get_last_message());
}

score 0 · Answer 14 · answered Jan 03 '14 at 11:28

0

I've been having the same problem (well I hope the same). Anyways it turned out to be my version of ntwdblib.dll, which was out of date in my PHP folder.

http://dba.fyicenter.com/faq/sql_server_2/Finding_ntwdblib_dll_Version_2000_80_194_0.html

answered Jan 03 '14 at 11:28

Dean Anderson

11
1

score 0 · Answer 15 · answered Jul 22 '21 at 17:18

0

$serverName="ServerName";
$uid = 'sa';
$pwd = "password";
$dbase = "dbname";

$conninfro = array("Database"=>$dbase, "UID"=>$uid, "PWD"=>$pwd);

$con = sqlsrv_connect( $serverName, $conninfro);
if( $con === false){
    die( print_r( sqlsrv_errors(), true));
}

answered Jul 22 '21 at 17:18

DC Design

1

3

Code-only answers are not particularly helpful. Please add some descriptions of how this code solves the problem. – 4b0 Jul 22 '21 at 17:23

score 0 · Answer 16 · answered Jan 03 '22 at 13:59

you can use PDO.

1-you must use php_pdo_sqlsrv_xx_nts.dll and php_sqlsrv_xx_nts.dll extensions.

note that you must use the proper version of those for your PHP.

2- use this code

    $servername = "IP";
    $username = "";
    $password = "";
    $db='';


try {
    $conn = new PDO("sqlsrv:Server=$servername;Database=$db", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

and then use this code:

$stmt=$conn->prepare("select * from table");
$stmt->execute();
$result=$stmt->fetchall();

score -2 · Answer 17 · edited Dec 29 '19 at 05:54

-2

Try this to be able to catch the thrown exception:

$server_name = "your server name";
$database_name = "your database name";
try {
   $conn = new PDO("sqlsrv:Server=$server_name;Database=$database_name;ConnectionPooling=0", "user_name", "password");
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    $e->getMessage();
}

edited Dec 29 '19 at 05:54

Peyman Mohamadpour

17,954
24
89
100

answered Dec 29 '19 at 04:08

Raj

1

This code makes no sense – Your Common Sense Aug 01 '22 at 07:57

How to use PHP to connect to sql server

17 Answers17

Linked

Related