Having SQL Server storing data for your PHP web application isn’t a common combination but is a far better option than having MySQL when it comes to larger database. I wont brag about why choose SQL Server over MySQL instead will quickly demonstrate in few steps how to execute stored proc in PHP using mssql database extension library.
We will be using mssql_query to execute the stored procedure and return records added using SCOPE_IDENTITY(). Please accept my appologies for coding indentation and lack colouring! @@
Let’s take a task list scenario where we manage our to-do tasks in a table having an ID, name, date added, priority task, and active fields.
First step, create your table in SQL server like the following:
Step 2, Create a stored procedure to insert records:
CREATE proc [dbo].[usp_InsertTask]
insert into tblTask(TaskName, TaskDateAdded, TaskPriority, TaskActive)
values (@TaskName, @TaskDateAdded, @TaskPriority, @TaskActive)
SELECT SCOPE_IDENTITY() as id
Note “SELECT SCOPE_IDENTITY() as id” will return the inserted record TaskID set as auto increment.
Step3, back to PHP to create your mssql_connect connection and execute stored procedure
$myServer = “10.64.0.7”;
$myUser = “mydevuser”;
$myPass = “mydevpassword”;
$myDB = “devdatabase”;
// connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die(“Couldn’t connect to SQL Server on $myServer”);
// select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die(“Couldn’t open database $myDB”);
//setup variable data
$TaskName = ‘Configure SQL Server’;
$TaskDateAdded = ’12/22/2008′;
$TaskPriority = 2;
$TaskActive = true;
$result = mssql_query(“usp_InsertTask ‘$TaskName’, ‘$TaskDateAdded’, ‘$TaskPriority’, ‘$TaskActive'”, $dbhandle);
$result = mssql_query(“EXEC dbo.usp_InsertTask @TaskPriority = ‘$TaskPriority’, @TaskName = ‘$TaskName’, @TaskDateAdded = ‘$TaskDateAdded’, @TaskActive = ‘$TaskActive'”, $dbhandle);
$result = mssql_fetch_object($result);
echo “Record ID = “.$result->id;
// close the connection
Step 4, DONE!
Most blog posts found when google-ing php and stored procedures SQL Server will go on about using mssql_execute function instead of mssql_query directly. But you will find much more posts about how it actually doesn’t work as php.net/mssql manual suggested. Probably because of new version of SQL server? PHP? or wrong mssql driver or configuration?
The steps i have provided is working under PHP5 on a Debian dev server and SQL Server 2008 CTP.
I’be been working on php for 2 months now and developing on my laptop with WAMP installed before uploading to work’s dev server. Now working with SQL Server instead of MySQL, I’ve installed SQL SERVER 2008 CTP for test and suddently Apache went down as port 80 was used by Microsoft HTTPAPI/2.0.
I then found SSRS (SQL Server Reporting Services) was still running after uninstalling SQL Server 2008 as it features a web service even though IIS is not installed according to wiki.
I couldn’t find any info on google relating to this small issue that puzzled me for a short while. i hope this helps finding ppl stupid like me sometimes to solve their problem
Peter Ward and Charles Sturling have driven excitement to about 25 QUT students during the SQL Server Campus Event. We have seen SQL Server Business Intelligence Technologies, especially SQL Server 2005 Reporting Services
Students warming up of excitements for a friday afternoon 🙂
Students that wish to further their knowledge in SQL Server, the next QSSUG is on Thurs, 24 May 2007
Venue: Microsoft Brisbane Office, Level 9, Waterfront Place, 1 Eagle Street
Duration : Catering from 5:30PM for a 6:00PM Start
It is with great pleasure to have Peter Ward presenting SQL Server 2005 at the Queensland University of Technology on May the 4th.
“Peter Ward is WARDY IT Solutions Chief Technical Architect. Peter is an active member in the Australian SQL Server community and President of the Queensland SQL Server User Group. Peter is a highly regarded speaker at SQL Server events throughout Australia and is a sought after SQL Server consultant and trainer, providing solutions for some of the largest SQL Server sites in Australia. Peter is a regular author for several SQL Server websites and has published numerous articles in the monthly SQL Server newsletter that he produces along with the highly acclaimed WARDY IT Solutions SQL Server Blog. Peter has been selected as a Spotlight speaker for the 2007 SQL Pass Community Summit, the largest SQL Server event in the world and has recently been awarded as a Microsoft Most Valuable Professional for his technical excellence and commitment to the SQL Server Community” – http://wardyit.com/about.aspx
Students will have the privilege to understand the importance of SQL Server in the industry and tackle the best features of SQL Server 2005. They will also be introduced the SQL Server community based in Brisbane, the Queensland SQL Server User Group.
I find this event a great opportunity for QUT students to hear from a professional user of SQL Server sharing his valuable knowledge. It is also encouraging and motivating students starving of technology.
The Faculty of Information Technology invites you to the Microsoft SQL Server 2005 campus event.
In this event, you will be presented with key SQL Server 2005 features and understand the importance of SQL Server in the industry. Free Pizzas and drinks will be provided and Visual Studio Express Editions including SQL Server 2005 Express.
- Speaker: Peter Ward (Solution Chief Technical Architect and MVP Windows Server System – SQL Server)
- Date: 04-May-2007
- Time: 15:00 – 17:00
- Venue: S405a, S block, GP
- Admission: FREE!
- 15:00 – 15:30 – Pizza and soft drinks
- 15:30 – 15:40 – Introduction
- 15:40 – 16:00 – Importance of SQL Server in the industry
- 16:00 – 16:50 – SQL Server 2005 Features
- 16:50 – 17:00 – Locknote and QSSUG info
To help organize with seats and pizzas & drinks, please RSVP to firstname.lastname@example.org
Hope to see you there!