PHP is an easy-to-learn and powerful open source scripting language. Microsoft SQL Server is a robust database product, which handles terabytes of data. Combine them to create database-driven Web sites that can handle large amounts of data and traffic.

Web developers must constantly cope with the pitfalls of integrating multiple platforms and technologies to produce seamless software solutions. PHP is an easy-to-learn and powerful open source scripting language. Microsoft SQL Server is a robust database product which can handle terabytes of data. It makes a great deal of sense to combine the two to create database-driven Web sites that can handle large amounts of data and traffic.

The goal of this article is to help you integrate PHP and Microsoft SQL Server. I will look primarily at two PHP functions:

  • Unified ODBC functions
  • MSSQL functions

The article includes a list of resources in case you want to deploy a solution hosted on the Linux platform. I've also included a section at the end to help you troubleshoot common integration problems.

The code in this article was tested using PHP version 4.3.4 running on the Apache Win32 v.2.0.48 and Microsoft SQL Server 2000 Developer Edition. The article skims over things such as SQL Server administration and security. The MSDN library is an essential resource if you need more information.

Setting up your Data Source Name (DSN)
If you want to connect to SQL Server using ODBC, you must first set up a DSN. A System DSN allows you to connect to a database server using an alias rather than writing out a long connection string. To launch the DSN wizard (Figure A), simply go to Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).

Figure A
DSN wizard


First, select SQL Server as the driver to connect to your database (Figure B). Then select a name for the DSN (I chose "sqlserver" but you can pick any name you want). Finally, select what database you want to connect to.

Figure B
Selecting a data source name


Next, are your security settings: In the examples in this article, I used SQL Server authentication using the default user account (username:sa, password:<blank>), as shown in Figure C. In a real world situation, you are advised to set up a new account with a strong username/password combination. Windows NT authentication can also be used but keep in mind that the database must reside on the same server as your PHP server to make it work.

Figure C
Security settings


Finally, you have the option to pick a default database to connect to. The examples in this article reference the Northwind database, as in Figure D, but you can easily customise the code to work with your own database.

Figure D
Selecting a default database


Unified ODBC functions
Open Database Connectivity (ODBC) is a powerful API developed by Microsoft to allow developers to interface with any compliant database (which includes Microsoft SQL Server, Oracle, DB/2 and many others). ODBC is automatically enabled in most current PHP distributions. The complete list of Unified ODBC function methods and properties are available in the PHP manual.

Here is some sample code to connect to the default Northwind database using odbc_connect. First, you define three variables to store the DSN name, username, and password:
<?
$dsn="sqlserver";
$username="sa";
$password="";

Then you call the odbc_connect function to connect to the database using the appropriate credentials. odbc_exec is then used to send a query to SQL Server:
$sqlconnect=odbc_connect($dsn,$username,$password);
$sqlquery="SELECT companyName FROM Customers;";
$process=odbc_exec($sqlconnect, $sqlquery);

Finally, you extract all of the companies from the Customers table and display them onscreen in a list. Once the entire list has been displayed, the connection to the database is closed:
while(odbc_fetch_row($process)){
$companyName = odbc_result($process,"companyName");
echo "$companyName<br>"; }
odbc_close($sqlconnect);
?>

MSSQL functions
PHP has a set of native functions to access Microsoft SQL Server. While the manual officially recommends that you install the extensions on the Windows platform, it is possible to make it function in Linux by using the FreeTDS libraries. Here is the entire list of properties and methods for this function.

Before you can use the mssql functions, you must enable the extensions. You can accomplish this in a few simple steps:

  • Install the Microsoft SQL Client Tools on the same server as your Windows PHP installation. The tools are readily available on the SQL Server CD.
  • Edit your php.ini file and uncomment (remove the semicolon from) the following line of code. If this line is missing in your php.ini, you must manually add it in:
    extension=php�_mssql.dll
  • The php_mssql.dll file can be obtained in the PHP downloads for Win32. You must make sure that you place the dll in both the /extensions and /WINNT/system32 folder.
  • Restart the Web server.

The following example is identical in functionality to the ODBC code above. The only difference is that you are using a DSN-less connection to access the database. You must specify a server and port rather than a database alias. Second of all, the methods used in this example are specific to the MSSQL function:
<?
$server="localhost,1433";
$username="sa";
$password="";
$sqlconnect=mssql_connect($server, $username, $password);
$sqldb=mssql_select_db("Northwind",$sqlconnect);
$sqlquery="SELECT companyName FROM Customers;";
$results= mssql_query($sqlquery);
while ($row=mssql_fetch_array($results)){
echo $row['companyName']."<br>\n";}
mssql_close($sqlconnect);
?>

What about Linux?
If you have PHP installed on a Linux box, there are many options available to you:

Linux ODBC Drivers: iODBC is an open source project to provide ODBC database functionality to platforms such as Linux, Solaris, FreeBSD and Apple. In conjunction with the PHP ODBC functions, you should have no problems connecting to a SQL Server. UnixODBC also produces Linux database drivers. Alternatively, if you are looking for a robust commercial solution OpenLink has designed Linux drivers for ODBC, JDBC, OLE DB, and ADO.NET.

Sybase & TDS: Sybase and Microsoft SQL Server both support the Tabular Data Stream (TDS) protocol. You can connect PHP to SQL Server by using the open source FreeTDS libraries. Here is a great tutorial covering this topic in detail.

Troubleshoot your SQL Server connection
What should you do if you get errors and are unable to connect to your SQL Server? Here are some guidelines to help you troubleshoot most of the connectivity problems you might encounter:

Configure SQL Server correctly: Before you try running queries on the server using PHP, try out your queries locally using the Query Analyzer. If your queries aren't yielding the desired results, then you have to go back and check your table and field names, structure, and so forth.

Test SQL Server's connections: You can verify that SQL Server is accepting connections using telnet. In a command window, type the following:
telnet <sql server name or ip address> 1433

If you get an error message such as "Could not open a connection to host on port 1433: Connect Failed…" then you should open the SQL Server Enterprise Manager and check the server and port setting. This problem can also be caused by a firewall or if the SQL Server service has been stopped unexpectedly. If you get a blank screen, it means that SQL Server is accepting incoming connections.

Check your PHP modules: If you get a message such as "Fatal error: Call to undefined function: mssql_connect()" it means that your MSSQL or ODBC modules have not been installed correctly. Check that the proper extension has been uncommented in your php.ini file, the appropriate dlls are copied to the /extensions and /WINNT/system32 folders, the right Linux drivers are installed, etc..

Set sufficient security privileges: If you get an error message along the lines of "Username not associated with a trusted SQL Server...", check your account permissions using the SQL Server Enterprise Manager.

Point to the correct database: Microsoft SQL Server 2000 can contain an untold number of databases. When you are setting up your DSN, you can specify a default database. If you plan to access multiple databases, you can programmatically specify the database using the mssql_select_db method.

Check your syntax: The MSSQL and ODBC functions differ in syntax—always refer to the PHP manual.

Related links

Comments

1

tom lawson - 20/05/05

Hi,

Great article.

I have tried to connect php to "SQL Server 2005 CTP" on Windows 2000 following your suggestions without success:

$server="localhost,1126";
$username="sa";
$p****word="kuba12tv";
$sqlconnect=mssql_connect($server, $username, $p****word);
$sqldb=mssql_select_db("test",$sqlconnect);
$sqlquery="SELECT * FROM test;";
$results= mssql_query($sqlquery);
while ($row=mssql_fetch_array($results)){
echo $row['t_name']."
\n";}
mssql_close($sqlconnect);
?>

$sqlconnect=odbc_connect("sqlncWin");
$sqlquery="SELECT t_name FROM test;";
$process=odbc_exec($sqlconnect, $sqlquery);
$result = odbc_result($process,"t_name");
echo $result;
}
?>

No error messages, nothing. The "SQL Server 2005 CTP" install checks out OK from its end. All your troubleshooting suggestions come back OK as well.

Any thoughts?

Thanks

Tom Lawson

» Report offensive content

2

Catherine - 23/05/05

Cool help!Thanks.
regards.

» Report offensive content

3

Franz K. - 31/01/06

I really liked you article and I tried to use it on my machine. The ODBC test comes back successfully but when I use the following little script it tells me that no connection can be made.
Chould there be PHP problem

» Report offensive content

4

Matt Overington - 02/02/06

Comments from the Author (Jean-Luc David):

A lot has changed in two years including the database and PHP API. Here are comments from the PHP site that may be useful:

http://ca.php.net/function.mssql-connect

The recently released SQL Server 2005 Express will work with MSSQL functions. Here are some caveats:

1) The version of ntwdblib.dll supplied with PHP will not work with Server 2005 (Win32 only). You must use a newer version. The version supplied with SQL Sever 2000 (2000.80.194.0) seems to work well.
2) You must set up mssql_connect to reference the server AND the instance name, eg:
mssql_connect("MyPC\SQLEXPRESS","MyUser","MyPwd");

Although the MSSQL functions work, you might want to consider using the new 'pdo_odbc' (PHP 5.1) with SQL Server 2005 Express for any new code you are writing.

Another suggestion is making sure the user has installed the "Microsoft SQL Server Native Client" feature pack (which includes ODBC drivers for SQL Server 2005). It should be included on the SQL Server 2005 DVD. Otherwise, users can download the drivers from:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

Pubs and Northwind (the databases referenced in the article) are not installed by default with SQL Server 2005. You have to install them separately via this download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034

Final suggestion, the user should walk through the process of connecting to a new database one step at a time. Use the odbc_connect return code as an indicator whether the connection was actually successful, then try a very basic queries, ect. It makes it easier to troubleshoot rather than copy/pasting code and trying to figure out where it went wrong.

» Report offensive content

5

Nicolas Seydoux - 15/03/06

thank you!

i fixed the troubles in easyphp 1.8 with that :
1) go to http://www.webzila.com/
2) search for ntwdblib.dll
3) dl the one with 2000.80.194.0
4) replace your ntwdblib.dll in easyphp/php/
relaunch the server , and voila !

» Report offensive content

6

Sidney Correia - 24/09/06

I like you article, but I only get'i to connect by odbc.

» Report offensive content

7

Cursaru Ionut - Cornel - 04/10/06

Y use this code for the conection with Microsoft Sql server and i receive this error "Fatal error: Call to undefined function mssql_connect() in C:\Inetpub\wwwroot\firstPHP\default.php on line 13".
Somebody told me that i have to cnge someting in the PHP.ini to activate the connection with SQL (i have PHP 5.1.6 version and i use IIS).Also y need to now the seting for MySQL.

<?

$server="localhost";

$username="sa";

$password="sa";

$sqlconnect=mssql_connect($server,$username,$password);

$sqldb=mssql_select_db("ebay",$sqlconnect);

$sqlquery="SELECT * FROM categorie;";

$results= mssql_query($sqlquery);

?>

» Report offensive content

8

raul.martinez - 18/11/06

thats why you don't have mssql module loaded in your php.ini you need to register it in php.ini and restart server.

» Report offensive content

9

hery - 12/12/06

great tutorial, help me a lot...thx

» Report offensive content

10

perfume117 - 20/12/06

hi
i tried to connect to sql server 2000 via php4 but when i wrote this code
<?
$server="localhost,1433";
$username="sa";
$password="";
$sqlconnect=mssql_connect($server, $username, $password);
$sqldb=mssql_select_db("Northwind",$sqlconnect);
$sqlquery="SELECT companyName FROM Customers;";
$results= mssql_query($sqlquery);
while ($row=mssql_fetch_array($results)){
echo $row['companyName']."<br>\n";}
mssql_close($sqlconnect);
?>
an error message occured. "Call to undefined function mssql_connect()"
please help me

» Report offensive content

11

perfume117 - 20/12/06

i edited php.ini too

» Report offensive content

12

samith - 14/01/07

thanks

» Report offensive content

13

mahesh - 02/02/07

how i can connect ms sql server2000 without ODBC in php4

» Report offensive content

14

dimply - 06/06/07

how can i connect the sql server with windows NT authentication using the network login ID?

» Report offensive content

15

manish - 06/07/07

hi,
I am much tired to connect the php and sql server.
error is:login failed:Mnaish dubey\dhermendra
pls help me.
thanks
manish

<?php



echo $myServer = "192.168.1.36";echo "<br>";

echo $myUser = "sa";echo "<br>";

echo $myPass = "root";echo "<br>";

echo $myDB = "webchat";echo "<br>";



//connection to the database

echo $dbhandle = mssql_connect($myServer, $myUser, $myPass)

  or die("Couldn't connect to SQL Server on $myServer");echo "<br>";



//select a database to work with

echo $selected = mssql_select_db($myDB, $dbhandle)

  or die("Couldn't open database $myDB");echo "<br>";



//declare the SQL statement that will query the database

echo $query = "SELECT uid, name, pass ";

echo $query .= "FROM user";

echo $query .= "WHERE name='manish',pass='manish'";

//echo $query = "select * from [user] where uid=1 and name='$username' and pass='$password'";



//execute the SQL query and return records

echo $result = mssql_query($query);



echo $numRows = mssql_num_rows($result);//die;

echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";



//display the results

while($row = mssql_fetch_array($result))

{

  echo "<li>" . $row["uid"] . $row["name"] . $row["pass"] . "</li>";

}

//close the connection

mssql_close($dbhandle);

?>

» Report offensive content

16

aminvm - 13/07/07

I telnet the MS SQL server with 1433 and I got the blank screen. I used standard codes of php as above mentioned but it still not working. Does anyone have any Idea on this

» Report offensive content

17

aminvm - 16/07/07

is it the same thing for Microsoft SQL Client Tools with Client Network Utility

» Report offensive content

18

ronie - 05/10/07

thanks...!

» Report offensive content

19

Martin - 14/11/07

Actually this also works well on the local host, try it out !

      <?php

      $this->dbname='[database name]';

        $this->server='192.168.0.131';   // your IP address

        $this->user='sa'; // username

        $this->password='password';

?>

» Report offensive content

20

LauraP - 30/11/07

Hi, when I try to connect with php on my sql server 2005 I had this error " Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in C:\Inetpub\wwwroot... " but with phpinfo() I see that mssql module is ok. When I use the newest ntwdblib.dll php can't use mssql and I don't find the mssql module in the messages of phpinfo()... and the is the error is " Fatal error: Call to undefined function mssql_connect()... ".
Can you help me?

» Report offensive content

21

KellyE - 05/01/08

I'm having the exact same issue as LauraP when I replace the ntwdblib.dll file. Windows Server 2003, Running PHP 5.2.1, IIS 6.0 connecting to SQL server 8.0.239

Anyone find any solutions to this?

» Report offensive content

22

Manjunath Pai - 17/01/08

I want to fetch the data from some other machine, SQL SERVER is installed.I am using WAMP package in my sytem.
Things i did
1)Enabled php.ms_sql in php.ini and also through the frontend provided.
2)Pasted the file ntwdblib.dll in PHP EXT and SYSTEM32(I am using VIESTA)
3)Used below script
<?
$server="localhost,1433";
$username="sa";
$password="";
$sqlconnect=mssql_connect($server, $username, $password);
$sqldb=mssql_select_db("Northwind",$sqlconnect);
$sqlquery="SELECT companyName FROM Customers;";
$results= mssql_query($sqlquery);
while ($row=mssql_fetch_array($results)){
echo $row['companyName']."<br>\n";}
mssql_close($sqlconnect);
?>

ERROR i GOT:Unable to connect to the server

» Report offensive content

23

ismail - 31/01/08

i gained a lot of knowledge. thanks to all

» Report offensive content

24

BHABANI - 10/05/08

HOW TO CONFIGURE PHP AND MY SQL IN XP OPERATING SYSTEM

» Report offensive content

25

Ejiro D. A - 30/05/08

Hey! its just same thing i have been trying for a long yime now , i just couldn't connect to my mssql, i have tried and tried but it's just not working!

» Report offensive content

26

Ksypolit - 11/06/08

Hello

I am using PHP 5 and MS SQL Server 200 and working fine in many applications using the mssql functions.

I have a problem when i try to use the odbc functions to connect with the SQL Server.
I have made the dsn connections as described in the article and test it (Connected with success) but when i try to use
it in the php code i get the following error.

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified,

Any ideas???

Here is the simple php code i am using to connect through my dsn connection 

$dsn = "TestSqlServer";

$user = "sa";

$pass = "";



odbc_connect($dsn, $user, $pass);

» Report offensive content

27

Ksypolit - 11/06/08

I found my mistake...

I have set a File DSN and not a System DSN... When i set a system DSN all works fine

Thanks!!!

» Report offensive content

28

david chao - 07/07/08

Dear Sir:

I am learning the php&sql server 2000 now ; I am very interesting

in your examples, they help me a lot. now I have a question:

Before I use the php to access the sql server 2000, do I have to

set up apache server or IIS server? Thanks!!

» Report offensive content

29

VLakshmi - 10/07/08

Thanks a lot, it worked well for me

» Report offensive content

30

Amokachi - 06/08/08

My Database is other Pc in a LAN, and my app is my PC and I tried to connect to SQL 2000, but it was failure (sorry my english is so bad):

Application show this message when mssql_secure (I can't remember) is ON

Warning: mssql_connect() [function.mssql-connect]: message: Error de inicio de sesión del usuario 'pchavez'. Motivo: no está asociado a una conexión de SQL Server de confianza. (severity 14) in C:\wamp\www\phonoguia\index.php on line 11

But when I tried same code but mssq.secure = OFF

Application Show this message

Warning: mssql_connect() [function.mssql-connect]: message: Error de inicio de sesión del usuario 'pchavez'. Motivo: no está asociado a una conexión de SQL Server de confianza. (severity 14) in C:\wamp\www\phonoguia\index.php on line 11

But I try in a local DB with my local app it's really works!!!

Plz anybody help me!!

» Report offensive content

31

alcitme - 12/08/08

can you pls. help me in our web-based programming appl. using php program in which we are required to make an online voting system in a php programming language as well as to connect in database.
Pls...........give me your code as soon as possible.......
Thank you so much...........
God bless you & may God give you more knowledge in programming.

» Report offensive content

32

mithun - 27/02/09

Thank u very muchhh....its very helpfull...........

» Report offensive content

33

alamelu - 06/10/09

hi
i tried to connect to sql server 2000 via php4 but when i wrote this
please help me 

code

<?

$server="localhost,1433";

$username="sa";

$password="";

$sqlconnect=mssql_connect($server, $username, $password);

$sqldb=mssql_select_db("Northwind",$sqlconnect);

$sqlquery="SELECT companyName FROM Customers;";

$results= mssql_query($sqlquery);

while ($row=mssql_fetch_array($results)){

echo $row['companyName']."<br>\n";}

mssql_close($sqlconnect);

?>

an error message occured. "Call to undefined function mssql_connect()"

» Report offensive content

34

mohammad awwad - 10/10/09

i have php 5.3.0 i am trying to connect with mssql db but i had some issues that php_mssql.dll & php_pdo_mssql.dll is missing can you send it to me please ASAP

thanks

» Report offensive content

Leave a comment

You must read and type the 6 chars within 0..9 and A..F

* indicates mandatory fields.

Latest comments

34

mohammad awwad - 10/10/09

i have php 5.3.0 i am trying to connect with mssql db but i had some issues that php_mssql.dll & php_pdo_mssql.dll ... more

33

alamelu - 10/06/09

hi i tried to connect to sql server 2000 via php4 but when i wrote this please help me code <? $server="localhost,1433"; $username="sa"; $password=""; $sqlconnect=mssql_connect($server, $username, $password); $sqldb=mssql_select_db("Northwind",$sqlconnect); $sqlquery="SELECT ... more

32

mithun - 27/02/09

Thank u very muchhh....its very helpfull........... ... more

Log in


Sign up | Forgot your password?

Blogs

RSS feed

  • Staff Microsoft shows off IE9 preview

    This week, highlights from Microsoft's MIX10 conference and more in the Roundup. Read more »

    -- posted by Staff

  • Chris Duckett IE9's H.264 vote killed Ogg

    In a split decision by the judges, the winner of the W3C/WHATWG video codec consensus is H.264, taking home the future of video playback on the internet while loser Ogg goes home with nothing but thoughts of what might have been. Read more »

    -- posted by Chris Duckett

  • Staff Google launches Apps Marketplace

    Google launches and app store, while Mozilla plans to re-write its open-source license. More of this week's news in the Roundup. Read more »

    -- posted by Staff

Most popular tags

What's on?

  • Optus Deal

    Broadband + home phone + PlayStation®3 in a single package price!