Home arrow PHP arrow Page 3 - Executing SQL Server Stored Procedures With PHP
PHP

Executing SQL Server Stored Procedures With PHP


In this article Joe looks at how to connect to an SQL Server 2000 database using PHP's set of mssql_xxx functions, and also how to execute commands and stored procedures against that database.

Author Info:
By: Joe O'Donnell
Rating: 5 stars5 stars5 stars5 stars5 stars / 93
January 01, 2003
TABLE OF CONTENTS:
  1. · Executing SQL Server Stored Procedures With PHP
  2. · Configuring PHP
  3. · Running the mssql_xxx functions
  4. · Executing stored procedures
  5. · Capturing output parameters and return values
  6. · Conclusion

print this article
SEARCH DEVARTICLES

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:

Retrieving records from the employees table

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.
blog comments powered by Disqus
PHP ARTICLES

- Removing Singletons in PHP
- Singletons in PHP
- Implement Facebook Javascript SDK with PHP
- Making Usage Statistics in PHP
- Installing PHP under Windows: Further Config...
- File Version Management in PHP
- Statistical View of Data in a Clustered Bar ...
- Creating a Multi-File Upload Script in PHP
- Executing Microsoft SQL Server Stored Proced...
- Code 10x More Efficiently Using Data Access ...
- A Few Tips for Speeding Up PHP Code
- The Modular Web Page
- Quick E-Commerce with PHP and PayPal
- Regression Testing With JMeter
- Building an Iterator with PHP

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials