• Skip to main content
  • Skip to primary sidebar

CarlosOnWeb.com

Code, Console, Content

  • Home
  • Services
  • Contact

Connecting PHP to MS SQL via ODBC

February 7, 2007 by carlosonweb Leave a Comment

The popularity of Java and .NET these days has relagated the lonely ODBC on the sidelines. To heck with ODBC? It is inherently slow and besides newer technologies for database connectivity are all over the place. In fact, they come bundled with the platform or framework or the SDK you are using.

Problem arises when you are not so free to use those fancy technologies. Now, for all its sake, read further as this one actually happens to a programmer I know and you might get a chance to experience the same as a working programmer.

Jeff was asked by his IT Department Head to develop an application to generate reports against an existing in-house developed HR Application with MS SQL Server at the backend. The reports will be used by Department Heads accross the office over their local area network. He is not to install anything on the already slow client machines.

Building an Intranet Solution around PHP is a natural approach for Jeff considering his experience of the language. He searched through the manual learned about the MS SQL Functions in PHP. This one is cool! The only drawback is he doesn’t want to install any third party library on the Server.

Using the pre-intalled PHP ODBC Functions seem to be a sensible alternative.
In this article, I will show you how to successfully connect PHP to an MS SQL Server using ODBC. We will use the undying Northwind Database that comes with the MS SQL Server installation.

If you have not done this before, the only really challenging task is to correctly an ODBC data source. Once this is done, the rest is easy. What follows is visual tour showing you how to establish your ODBC Connection to the Northwind Database.

STEP 1. Launch the Windows Admistrative Tool Panel

Here is a sample screen from Windows 2000 Professional.
Windows Administrative Tools Panel

Select and double click on the Data Sources Icon. You will then be taken to the next screen.

STEP 2. Select SQL Server
Create MS SQL ODBC Data Source

Really scroll down on the list until you see the SQL Server item. You have to select that one to be able to connect to MS SQL Server later. Click on the Finish Button, then you will be taken to the next screen.

STEP 3 . Create Northwind Data Source Name (DSN)

Northwind Data Source Name DSN

The parameters that you enter on the input boxes are very important. In the Name, put NorhtwindDSN. This name will be used later in the actual PHP Script. Next, specify the name of the Server where MS SQL is actually installed. In this case, I am using a local server, so I simply put (local) on the drop-down combo. The Description Field is actuall optional. When you are done, click on the Next Button.

STEP 4. Authentication

Server Authentication

For simplcity, let us use Windows NT Authentication. This means that when you are logged to the Windows Server, you can also access MS SQL Server. Click on the Next Button.

STEP 5. Select A Database

Select Northwind Database
Ensure that you are using the Northwind Database by selecting it from the drop-down list. Then, click on the Next Button.

STEP 6. Test The Connection.

Test ODBC Connection

From here, we can already test the ODBC Setup. Clicking on the Test Data Source Button will show you whether or not you have successfully setup the connection.

STEP 7. The Final Touch

ODBC Setup Done
We really have nothing to do here except click on the OK Button. But, before doing that, see to it that the name NorthwindDSN appers on the list. If it does, you are ready for the actual PHP Code.

The PHP Code


< ?php

$dsn = 'NorthwindDSN';
$dbuser = 'sa';
$dbpass = '';

$dbcon = odbc_pconnect($dsn, $dbuser, $dbpass);
if ($dbcon){
   $sql = ' SELECT ProductID, ProductName'
         . ' FROM Products '
         . ' ORDER BY ProductName';
   $rst = odbc_exec($dbcon, $sql);
 
   while ($row = odbc_fetch_object($rst)){
       print "$row->ProductID :: $row->ProductName";
   }
}
?> 

That’s it. Try and run it yourself. It should display on the browser the ProductID and ProductName values from the Products Table.

Please be warned that this is just a quick-and-dirty way of showing you how to connect PHP to MS SQL. For one, I am using the sa user account without any password. It works fine in my local box. But if you do this on a production server, be prepared to take some of those headache pills.

There are much better programming techniques to use other than this. You, for example, use a Database Wrapper Class to have all database manipulation operations collected under one class. I leave it all to you to search for those techniques.

Share this:

  • Facebook
  • Twitter
  • LinkedIn
  • Email
  • Print
  • More
  • Pinterest
  • Tumblr

Filed Under: Classics By Carlos

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar


Get In Touch

I love to hear from you. Don't be shy!

Use the Contact Form to send me a message.




Write-Ups

  • Installing Apache, PHP, and MariaDB (instead of MySQL) in Mac OS X
  • Using WordPress — The Good, The Bad, and The Overkill
  • Domain Name: The Beginning Of Your Online Empire
  • 3 Easy Ways To Make Money Online
  • Is Your Lack of Technical Know-How Preventing You From Running An Online Business?
  • Must-Have Tools For The Budding Online Entrepreneur
  • Seven Basic Jargons Every Newbie Should Know Before Building A Website
  • Making Money Without Trading Time For It
  • Time and Money Trap
  • Small Businesses Still Don’t Get It — Part 2
[footer_backtotop text="Top" href="#content"] Carlos On Web :: PHP Developer and WordPress Back-end Specialist based in the Philippines © 2005–2022
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.