Home > PHP, SQL Server > PHP and SQL Server Stored Procedures, how to insert a new record under 4 steps

PHP and SQL Server Stored Procedures, how to insert a new record under 4 steps


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:

tbltask

Step 2, Create a stored procedure to insert records:

CREATE proc [dbo].[usp_InsertTask]
(
@TaskName varchar(255),
@TaskDateAdded datetime,
@TaskPriority int,
@TaskActive bit
)
as
insert into tblTask(TaskName, TaskDateAdded, TaskPriority, TaskActive)
values (@TaskName, @TaskDateAdded, @TaskPriority, @TaskActive)
SELECT SCOPE_IDENTITY() as id
RETURN

Note “SELECT SCOPE_IDENTITY() as id” will return the inserted record TaskID set as auto increment.

Check out Pinal Dave, SQL Server MVP, blog on the differenced between SCOPE_IDENTITY() vs @@IDENTITY vs IDENT_CURRENT to retrieve the last inserted record.

Step3, back to PHP to create your mssql_connect connection and execute stored procedure

<?php
$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);
OR
$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
mssql_close($dbhandle);
?>

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.

  1. toby
    April 22, 2011 at 2:49 am

    Hi, Thanks for the tutorial, it works great, I can’t seem to display the result using these 2 lines: $result = mssql_fetch_object($result);
    echo “Record ID = “.$result->id;

    Is there any other way to display my result back ?

  2. April 17, 2013 at 1:33 am

    Excellent blog! Do you have any tips for aspiring writers?

    I’m planning to start my own site soon but I’m a little lost on everything.
    Would you propose starting with a free platform
    like WordPress or go for a paid option? There are
    so many options out there that I’m totally overwhelmed .. Any recommendations? Thanks!

  3. December 12, 2014 at 3:57 pm

    worked for me

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: