Kết nối PHP với SQL Server!

PHP and SQL Server

LAMP (Linux-Apache-MySQL-PHP) is a great combination for a dynamic web site. But sometimes, due to existing data structures, other data clients, or whatever reason, you don't want to use MySQL, but need Microsoft's SQL Server instead. For a Linux user, this may seem like an anethma, but real IT professionals will tell you, "You use the tool that fits the job."
Similar to Windows, you can connect to a MS SQL Server through an open database connection data source name (ODBC DSN). FreeTDS provides a driver you can use to make this DSN. TDS (tabular datastream) is a protocol used by Sybase and Microsoft. This enables your Linux server to connect to a Microsoft SQL Server. Once the driver is installed, you can configure your ODBC connection to use the driver.
Install And Configure FreeTDS

The first thing you need to do is to download and install the FreeTDS driver. You can get the source and compile it yourself from http://www.freetds.org/, but I prefer RPMs. Depending on your distribution of Linux, the version you want will vary. I'm running Red Hat Enterprise Linux 4 ES and CentOS 4, which are almost identical. I installed freetds-0.62.3-1 from http://rpmforge.net/user/packages/freetds/
After installing FreeTDS, you can check your driver by attempting to connect to the MSSQL Server. Of course, use the appropriate server name, username and password in your command line.


# /usr/bin/tsql -S [mssql.servername.or.ip] -U [ValidUser]
locale is "en-US.UTF-8"
locale charset is "UTF-8"
Password: [password]
1>

Enter "quit" to exit your successful connection. If the tsql command doesn't return the 1> prompt, verify that you can get to your MSSQL server with telnet [mssql.servername.or.ip] 1433 and that your username and password are valid.
Next, edit your /etc/freetds.conf configuration file and add the following at the end of the file, replacing [mssql.servername.or.ip] with the appropriate server name or IP address:


[SQLServer]
host = [mssql.servername.or.ip]
port = 1433
tds version = 8.0
client charset = UTF-8




Setup ODBC Data Source

Next, unixODBC needs to know about all ODBC drivers you intend to use. While you can use the GUI program that comes with unixODBC, you can also use the odbcinst command. First create a template file containing your FreeTDS setup information.
tdsdriver.template

[FreeTDS]
Description = v0.62 with protocol v8.0
Driver = /usr/lib/libtdsodbc.so.0
Run odbcinst, telling it to install a driver entry using the template we just created.

# odbcinst -i -d -f tdsdriver.template
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc

This will add an entry to the end of the file /etc/odbcinst.ini

[FreeTDS]
Description = v0.62 with protocol v8.0
Driver = /usr/lib/libtdsodbc.so.0
UsageCount = 1

Next you create a ODBC data source name, or DSN, so client applications can utilize the driver. Unlike the driver, which is used by all users on a server, the ODBC DSNs are specific to the user who installs them. If you want to install a DSN for a specific user, sign on as that user then follow these instructions:
Once again, create a template then use odbcinst to create the DSN.
tdsdatasource.template
[MSSQLServer]
Driver = FreeTDS
Description = Sample Database
Trace = No
Server = [mssql.servername.or.ip]
Port = 1433
Database = [database]


Run odbcinst, telling it to install the datasource entry using the template we just created.


$ odbcinst -i -s -f tdsdatasource.template

Test your ODBC connection using isql.
# isql -v MSSQLServer [username] [password]
+----------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+----------------------------------+
SQL> Use "quit" to exit the SQL command prompt.
Make it work with PHP and UTF-8

To have a DSN for use with PHP, it has to be installed for the apache user, typically named "apache." Since you can't su to apache and install the DSN as above, you can add an entry to the /etc/odbc.ini file for all users and the apache user can make use of it.

/etc/odbc.ini

[MSSQLdatabase]
Description = Sample Database
Driver = FreeTDS
Trace = No
Servername = SQLServer #refers to the SQLServer in /etc/freetds.conf
Port = 1433
Database = [database]

You must also have to have the php-odbc module installed. You can check using the following command:


# rpm -qa | grep php-odbc
php-odbc-4.3.9-3.22.9

With the php-odbc module installed, in /etc/php.ini there should also be an entry under [ODBC]:

extension = odbc.so

Connecting to your Database

The last thing you need to do is to create a page that utilizes your ODBC DSN to connect to your database. Here is some sample code:
index.php

<?php
// connect to DSN "MSSQLServer" with user "Kate" and password "freckles"
$connect = odbc_connect("MSSQLServer", "Kate", "freckles") or die ("Could not connect to server");
// query the users table for all fields
$query = "SELECT * FROM users";
// perform the query
$result = odbc_exec($connect, $query);
// fetch the data from the database
while(odbc_fetch_row($result)) {
$field1 = odbc_result($result, 1);
$field2 = odbc_result($result, "field2");
print("$field1 : $field2\n");
}
// close the connection
odbc_close($connect);
?>

End