Connecting to Microsoft SQL Server from PHP in Ubuntu using mssql_connect()

As part of ongoing testing to find the best way of storing large amounts of data we are considering Microsoft SQL Server, which needs to be accessible from our PHP5 application. The site is hosted on Ubuntu Server 10.10, which makes things a little more interesting. We currently use adodb5 to talk to a MySQL server, which works fine, apart from MySQL being far too slow. It is possible to create an ODBC connection in Ubuntu to talk to SQL Server but for testing the speed of our queries I just used mssql_connect(). In the near future I will move the database connection entirely to ODBC/adodb so we don’t have to rewrite any of our existing code.

Ubuntu doesn’t come with the packages needed for mssql_connect() by default so you need to install them:

sudo apt-get install php5-sybase

Then restart the apache server to apply the changes:

sudo /etc/init.d/apache2 restart

Now you can use mssql_connect and its associated functions in your PHP to connect to your Microsoft SQL Server, as in this example:

ini_set(‘display_errors’, 1);
$server = ‘my.server.ip:1433DATABASEINSTANCE’;
$link = mssql_connect($server, ‘username’, ‘password’);

if (!$link) {
die(‘<br/><br/>Something went wrong while connecting to MSSQL’);
}
else {
$selected = mssql_select_db(“databasename”, $link)
or die(“Couldn’t open database databasename”);
echo “connected to databasename<br/>”;

$result = mssql_query(“select name from table”);

while($row = mssql_fetch_array($result))
echo $row["name"] . “<br/>”;
}

18 thoughts on “Connecting to Microsoft SQL Server from PHP in Ubuntu using mssql_connect()”

Pingback: Connecting to Microsoft SQL Server using ODBC from Ubuntu Server « James Rossiter

Pingback: Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP « James Rossiter

Thank you so much for this! I was running into walls left and right trying to connect Linux based PHP to MSSQL.
php5-sybase did the trick!

Reply ↓

You save my life James.

Reply ↓

mine too

Reply ↓

Not sure why you call MySQL slow. In my particular case I’ve found connections to MSSQL slower than to MySQL, even on small amount of data transfers. If you were having slow responses from MySQL here are some tips to speed it up:
1. Use mysqli adapter instead of mysql, it is way faster
2. Pagination
3. Change the the storage engine from MyISAM to InnoDB. MyISAM is designed for speed in writing and InnoDB for speed in reading.

Also, if you want portability in your code, I suggest using Zend Framework, and use it with Zend_Db_Select objects. These are managed entirely as objects independent of the DB adapter. This way you only have to change the Zend_Db_Adapter and that’s it.

Reply ↓

Hi,

Thank you very much for this quick solution, it helped me a lot.
I’ve added this page URL for my personal reference into http://www.webnapps.co.uk/helpful-links
I hope that is OK with you.

Regards

Reply ↓

i try ur solution with PHP Version 5.3.5.. seem that msssql_connect can’t execute and prompt d error msg Warning: mssql_connect(): Unable to connect to server: CMSDB

the php script run perfectly under PHP version 5.3.2..

Reply ↓

This was a HUGE help. I’d been banging my head against the wall for a couple days trying to figure this out. Thanks so much!

Reply ↓

Hi James,
It didnt worked for me i followed exactly the same steps as you have mentioned here, i am workingon localhost and trying to connect to live DB server… What could be wrong here? please help

Reply ↓

Pranav on May 31, 2012 at 11:34 am said:

I found the resolution, I was executing wrong code…sorry for bothering

Reply ↓

Hi, thx for your help, I’m having a little problem: for some reason with specif tables when I do a simple select (select * from table) an error occurs in the webpage. How can I find the problem? Thx again.

Reply ↓