PHP, Microsoft SQL Server (MSSQL), and IIS: Connect and Query with ODBC
I was recently thrown a real curveball of a project. Instead of using a fresh MySQL database to pull information from, the customer required that we pull information from their new Microsoft SQL Server 2005 server. This isn't the most desired method of PHP->Database interactivity but that's what the project called for and that's what I needed to do. Here's how to get things going.
Connecting to Microsoft SQL Server 2005 from PHP
// the "driver" first
$virtual_dsn = 'DRIVER={SQL Server};SERVER=database.domain.org,45277;DATABASE=MyDatabase';
$connection = odbc_connect($virtual_dsn,'Username','Password') or die('ODBC Error:: '.odbc_error().' :: '.odbc_errormsg().' :: '.$virtual_dsn);
Choose Which Database
odbc_exec($connection,'USE MYDB');
Executing a Query
$result = odbc_exec($connection, 'SELECT FirstName FROM Names');
Using the Query Result
$first_name = odbc_result($result, 'FirstName')
Querying Tables and Columns with Spaces
// use brackets [] for tables and colums with spaces $result = odbc_exec($connection, 'SELECT [First Name] FROM [Names Table]');
Like I said, while this isn't the most desirable setup in the world, it will do. Big ups to Hostway for their help in configuring the server; they provided everything we needed.
To learn more about the ODBC functions, visit PHP.net
Why didn’t you use the mssql_* functions?
Connecting to databases other than mysql seems so awkward since I don’t do it much; however, just the other day I had to connect to an old dbase database (actually just flat files).
If anyone is interested in you can read more about it here.
It can be a real pain connecting to obscure databases.
Is there still a character limit imposed by PHP’s SQL Server driver (it was based on Sql Server 4 which had a much smaller limit than current varchar’s can take)? I remember that being an issue.
@Rob: I’m not sure about that. I didn’t run into any problems with what I used above.
Is there still a character limit imposed by PHP’s SQL Server driver (it was based on Sql Server 4 which had a much smaller limit than current varchar’s can take)? I remember that being an issue.
Also, have you looked at the
mssql_*functions for using stored procedures?$sp = mssql_init('AllOpenCalls_S1', $dbLink); mssql_bind($sp, '@SortByColumn', $sortBy, SQLVARCHAR, false, false); $proc = mssql_execute($sp); while ($result = mssql_fetch_assoc($proc)){ ... }I just ran a google search and it appears MS finally updated the driver PHP uses so that shouldn’t be an issue. It appears to support up to SQL Server 2005.
If ODBC gets you down, try the mssql_* functions. It makes it very easy to use parametrized stored procs:
mssql_init, mssql_bind, mssql_execute, and mssql_free_result.
It appears my data is outdated. MS released a new SQL driver for PHP in May which clears that and other Sql Driver related issues.
If you want to use parameterized stored procedures, see here:
PHP MSSQL Functions
@Jesus: I found that most hosts only had ODBC functions, not MSSQL functions.
sorry about the overposts I was having technical difficulties with post utility. Please disregard the extra garbage.
Listen up close yo. You really need to have a plan for timeouts bc those connections time out like a mug. I learned the hard way. The reason I say that is that there is prob a 100% chance that this is a remote connection unless your php is executing on a windows box and it is on the same server. Usually your php runs on apache and the sql server db is on another box, and so thus the timeouts.
For apps that make a lot of database hits where constant timing out can be a pain you might consider running a job that dumps it to a mysql database, I’ve done that alot. If you just have some widget that makes a query, you might could get away with caching, but you always need to handle timeouts.
I really prefer web services over direct db connections if its say a third party or a database and infrastructure that’s completely separate from your own but sometimes you don’t always have that option and web service calls time out too.
Anyways hope that helps.
If somebody is familiar with ADOdb he can use php almost with any database. This is why we are encouraged at school doing this. It’s pretty simple and easier to handle than the original mysql :)
You made my day!
Actually you more than made my day – it is just the problem I needed to kickstart me with php on IIS!
ADOdb, ODBC…… The Zend Framework has a nice database abstraction layer in pure php (requires PHP5 but if you’re starting PHP4 projects now then something is wrong with you) – it can talk to PDO and a bunch of other extensions, I assume that it can abstract away MSSQL.
Maybe, maybe not… Probably not helpful but worth looking at (and E_STRICT compliant).
OH! And, it’s loosely coupled with the rest of the “framework” – you can use it by itself and don’t need to touch the other stuff – a bit like a normal library such as PEAR.
we are trying to connect to a remote mssql database using php that runs on a completely separate linux box. looking at phpinfo(), it looks as though ODBC is listed as supported under dbx. the problem is that every time i try odbc_connect(), it says it is an undefined function. does anyone know how i would go about getting it to work in php?
Hey, thanks for this…I’ve been trying to get a connection to a remote machine going for two days and every mssql function was crapping out on me and all attempts at fixing turned up nothing.
cant i connect Microsoft SQL server in PHP application to access data from SQL server ?
First, David, your the man. All I can say.
I have been using odbc to mysql for years now. I work on projects in the public sector pulling data from various sources and manipulating it into various formats depending on the client needs. Other than the tablenames in [], which I did not know or use, my setup is similar.
I’m trying now to figure out how to pull data at intervals to prevent clients/users from hitting their server directly (mission critical stuff). Your XML back file got my attention there, I just need to figure out how to create mssql->mysql mappings and learn something about sending the xml to mysql using some kind of field mapping. Great stuff though.
Hello David
Can you share anymore information on making the connection? I need to make the same type of connection but don’t have a helpful host. Did you need special extensions compiled into PHP?
$virtual_dsn = ‘DRIVER={SQL Server};SERVER=database.domain.org,45277;DATABASE=MyDatabase’;
hopefully it’s help alottttt
http://discussion.accuwebhosting.com/php-scripts/698-how-connect-ms-sql-database-server-php.html
Please tell me how can i set character in this function (odbc)
my database result appear like this : ????????
please tell me if you know .. i really do want it ..