Using PHP with MS SQL Server - An Example
(Page 4 of 5 )
Knowing what's necessary to get SQL Server working is a lot less interesting than actually using it. To that end I have provided an example below. I've chosen to illustrate the use of SQL Server with the DB package. It provides a slightly cleaner example, and those who have used DB for other database engines will notice the similarity.
<?php
require_once("DB.php");
$db =& DB::connect("mssql://user:password@host:1433/membership");
if (DB::isError($db)) {
die($db->getMessage());
}
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$res = db->query("EXECUTE member_list");
if (DB::isError($res)) {
die($res->getMessage());
}
echo "<table>n<tr>n<th>Name</th><th>Address</th><th>Email</th>" .
"<th>Expiration</th>n</tr>n";
while($row =& $res->fetchRow()) {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>n",
$row["name"], $row["address"], $row["email"],
$row["expiration"]);
}
echo "</table>n";
?>
There's nothing terribly complicated going on here. It's the sort of nine to five script that junior programmers use to put bread on the table, and that's the point. Using SQL Server is just like using MySQL, PostgreSQL, or even SQLite. The only real difference is the connection string and some of the SQL syntax.
If you're not familiar with the DB extension, I'll give you a quick overview of what's going on. We start with the DB::connect method. This method returns an instance of a DB descended class. Which class will depend on the database engine that you use, but the selection is transparent to you, since all descendant classes support the same core set of methods.
The setFetchMode() method is used to tell the class what format I want my result sets in. In this case I want associative arrays. The default is numerically indexed arrays. I almost always prefer an associative array when it's available, but it's mostly a matter of personal preference in a script this simple.
The query() method takes a query and returns a result set. Having my result set in hand, I use the fetchRow() method until there's nothing more to fetch, processing each row in turn.
One clever little bit of the DB classes is that they return an error object if there are any problems. This saves the sometimes problematic matter of checking return values which have different meanings depending on their context. The universal getMessage() method saves having to look up error codes and generate human readable results.
Next: Why SQL-Server? >>
More MS SQL Server Articles
More By Clay Dowling
/ 39 
