Executing SQL Server Stored Procedures With PHP - Running the mssql_xxx functions
(Page 3 of 6 )
As I mentioned earlier, PHP supports the mssql_xxx set of functions that offer the same kind of functionality and syntax that we've come to enjoy for MySQL. Because SQL Server has features that MySQL does not (such as stored procedures and triggers), the mssql_xxx set of functions includes a couple of unique functions. A list of these functions is shown below:
- mssql_get_last_message: Returns the last message that was generated by the server.
- mssql_min_error_severity: Sets the lower error severity at which an error will be raised.
- mssql_min_message_severity: Sets the lower error message severity at which an error will be raised.
- mssql_init: Used to initialize a stored procedure.
- mssql_execute: Executes a stored procedure against an SQL Server database.
- mssql_bind: Adds a parameter to a stored procedure.
- mssql_fetch_batch: Returns subsequent batches of records from SQL Server (if any).
- mssql_rows_affected: Returns the number of rows affected by the last query against the database.
If you've come from an ASP background like I have, then I'm sure you’ll agree that the way in which PHP supports SQL Server is excellent.
Let's start with a basic query that returns a list of employees from the Northwind table. Create a new PHP script called employee.php and enter the following code into it, substituting database connection variables where necessary:
<?php
$myServer = "localhost";
$myUser = "sa";
$myPass = "";
$myDB = "Northwind";
$s = @mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
$d = @mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");
$query = "SELECT TitleOfCourtesy+' '+FirstName+' '+LastName AS Employee ";
$query .= "FROM Employees ";
$query .= "WHERE Country='USA' AND Left(HomePhone, 5) = '(206)'";
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["Employee"] . "</li>";
}
?>When I ran the code above in my web browser, here's what the output looked like:
If you've worked with PHP and MySQL before, then much of the code shown above won't be anything new. However, for those who haven't, let's just quickly run through the code from our example:
$s = @mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
$d = @mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");Firstly we use the mssql_connect and mssql_select_db functions to connect to our SQL Server and select a database. If either of these actions fails, then the die function terminates our script, outputting the appropriate message to the browser.
$query = "SELECT TitleOfCourtesy+' '+FirstName+' '+LastName AS Employee ";
$query .= "FROM Employees ";
$query .= "WHERE Country='USA' AND Left(HomePhone, 5) = '(206)'";Next, we build a basic SQL query that uses field merging and the where clause to return all employees who live in USA or whose phone number starts with (206).
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";We then execute our query using mssql_query, capturing the result into the $result variable. We pass $result to mssql_num_rows to check how many rows were returned from the query and output that number to the browser.
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["Employee"] . "</li>";
}Lastly, we loop through the recordset using mssql_fetch_array so that we can refer to the employee field by its name.
Next: Executing stored procedures >>
More PHP Articles
More By Joe O'Donnell
