Microsoft SQL Server and PHP

From DreamHost
Jump to: navigation, search
The instructions provided in this article or section are considered advanced.

You are expected to be knowledgeable in the UNIX shell.
Support for these instructions is not available from DreamHost tech support.
Server changes may cause this to break. Be prepared to troubleshoot this yourself if this happens.
We seriously aren't kidding about this.

Connecting to Microsoft SQL Server with PHP

Standard Connections

The following script creates a Microsoft SQL Server resource, $link, that can be used to make queries:

<?php

$hostname = "";
$username = "";
$password = "";
$database = "";

$link = mssql_connect($hostname,$username,$password);
mssql_select_db($database) or die("Unable to select database");

?>

It is a good idea to put this script into a separate file, and then use PHP's include() function to append it to the beginning of any web page that requires database access. There is normally no reason to have more than one database for a website (particularly because there is no theoretical limit to the number of tables per database), so there is rarely a need for more than one connection resource. The connection will close once execution of the script ends, or by invoking the mssql_close() function.

Persistent Connections

An alternative to the normal connection is a persistent connection. mssql_pconnect() first searches for an existing connection that has the same hostname, username, and password. If the function finds one, it returns its identifier instead of trying to open a new connection. The persistent connection will not end with execution of the script - nor will it respect the mssql_close() function.

Executing Queries

Retrieving a Resultset

Given this example table (test_table):

+----+---------------+
| id | test_data     |
+----+---------------+
|  1 | A value       |
|  2 | Another value |
|  3 | Still another |
+----+---------------+

This example script will extract all the data in the table and display it in an HTML table, assuming the connection resource is stored in the $link variable:

<?php

$sql = "SELECT * FROM test_table";
$result = mssql_query($sql,$link) or die("Unable to select: ".mssql_get_last_message());
print "<table>\n";
while($row = mssql_fetch_row($result)) {
    print "<tr>\n";
    foreach($row as $field) {
        print "<td>$field</td>\n";
    }
    print "</tr>\n";
}
print "</table>\n";
mssql_close($link);

?>
WARNING
You will need to install a custom PHP installation in order to have MSSQL support with PHP on Dreamhost. The default PHP binaries to do not have access to these functions.

See Also

External link