PHP Syntax question re. @@Identity with SQL Server

Hi,
I am from a classic ASP background attempting to transpose some code into PHP.

I am attempting to insert a record into a SQL Server database and then grab the last generated primary key for use as a file name for an image.

My code goes a little like this:

	$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) VALUES (?,?) SELECT @@IDENTITY AS [NewsItemId] FROM NewsItem;";
	$params = array($_POST['headline'],$_POST['item']);
	$RS_NewsItem01 = sqlsrv_query($conn,$SQLStmt,$params);

if( $SQLStmt === false ) {
		$status = "0045";
		header('Location: error.asp?1='.$status);
		exit();
}

	$SQLStmt = "UPDATE NewsItem SET Image = ? WHERE NewsItemId = ?;";
	$params = array($RS_NewsItem01['NewsItemId'].'.jpg',$RS_NewsItem01['NewsItemId']);
	$RS_NewsItem02 = sqlsrv_query($conn,$SQLStmt,$params);

if( $SQLStmt === false ) {
		$status = "0045";
		header('Location: error.asp?1='.$status);
		exit();
}

Open in new window


The first bit is fine - it inserts Ok, but I have come unstuck with the syntax for the UPDATE.

Kind regards,
LVL 2
splantonAsked:
Who is Participating?
 
splantonAuthor Commented:
Cracked it...

	$serverName = "<myservername>";
	$uid = "<myuid>";
	$pwd = "<mypwd>";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"<mydatabase>");
	$conn = sqlsrv_connect($serverName, $connectionInfo);
	
	if( $conn === false )
		{
			$status = "0044";
			header('Location: error.asp?1='.$status);
			exit();
		}


// Insert with Select last generated Id.

	$SQLStmt = "SET NOCOUNT ON;INSERT INTO NewsItem (Headline,Item) VALUES (".$_POST['headline'].",".$_POST['item']."); SELECT SCOPE_IDENTITY() AS IDENTITY_COLUMN_NAME;";
	$RS_NewsItem02=sqlsrv_query($conn, $SQLStmt); 

	if( $RS_NewsItem02 === false )
		{
			$status = "0044";
			header('Location: error.asp?1='.$status);
			exit();
		}

// Make row available for reading.

	if (sqlsrv_fetch($RS_NewsItem02) === false)
		{
			$status = "0044";
			header('Location: error.asp?1='.$status);
			exit();
		}

// Get the row field

	$NewsItemId = sqlsrv_get_field( $RS_NewsItem02, 0);

	$SQLStmt = "UPDATE NewsItem SET Image = '".$NewsItemId.".jpg' WHERE (NewsItemId = ".$NewsItemId.");";
	echo($SQLStmt);
	$RS_NewsItem03 = sqlsrv_query($conn,$SQLStmt);

Open in new window


Looks like I was missing the right combination of query, fetch and get field.

Coming from an ASP environment I had no idea that the sqlsrv_query () doesn't actually bring any data back.
In ASP you issue an excecute.

For example:
Example: Set RS_Newsitem01 = Connection01.Execute(SQLStmt)

Open in new window


This executes the statement and brings back the first row, ready for use.

In PHP the sqlsrv_query() doesn't return anything. You have to issue a sqlsrv_fetch() statement, followed by a sqlsrv_get_field() to get any data back.

There was something of a red herring in the sqlsrv_next_result() - that was not helping things. I think I must have been close on several occasions but a lack of understanding of the relationship with sqlsrv_query(), sqlsrv_fetch(), sqlsrv_next_result() and sqlsrv_get_field() got me in a right mess. I think that the ID had been returned OK, but I had not been preparing the row and field correctly before trying to echo() it.

From reading the documentation on sqlsrv_query() it simply says the statement "Prepares and executes a query. " - not very specific when coming from an environment where 'excecute' has a much more complete functionality.

The solution above works with the native Microsoft PHP sqlsvr driver and has been tested with SCOPE_IDENTITY and @@IDENTITY

Anyway I have now got to the bottom of it and posted a complete working solution. If anyone would like further clarification on this please feel free to contact me.

Regards,
0
 
Ray PaseurCommented:
In the code snippet the script sets a string variable on line 1, then tests to see if it is FALSE on line 4.  I think you might want to test $RS_NewsItem01 on line 4.
0
 
Ray PaseurCommented:
Same idea on line 13 and 15.  Man page here:
http://php.net/manual/en/function.sqlsrv-query.php
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
splantonAuthor Commented:
OK, I get what you mean about this bit of code:

if( $SQLStmt === false ) {
		$status = "0045";
		header('Location: error.asp?1='.$status);
		exit();
}

Open in new window


So ignore that for the time being. I'm just concentrating on getting the insert and update working :

	$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) VALUES (?,?) SELECT @@IDENTITY AS [NewsItemId] FROM NewsItem;";
	$params = array($_POST['headline'],$_POST['item']);
	$RS_NewsItem01 = sqlsrv_query($conn,$SQLStmt,$params);

	$SQLStmt = "UPDATE NewsItem SET Image = ? WHERE NewsItemId = ?;";
	$params = array($RS_NewsItem01['NewsItemId'].'.jpg',$RS_NewsItem01['NewsItemId']);
	$RS_NewsItem02 = sqlsrv_query($conn,$SQLStmt,$params);

 

Open in new window


and how to get the generated SQL Identity Primary key into the UPDATE.

I'll sort out my error checking later :)
0
 
Ray PaseurCommented:
...sort out my error checking later
Might want to do that first.  Here is a code fragment annotated with comments to show what I was talking about.

// SET A QUERY STRING VARIABLE
$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) VALUES (?,?) SELECT @@IDENTITY AS [NewsItemId] FROM NewsItem;";

// SET AN ARRAY VARIABLE USING EXTERNAL REQUEST DATA
$params = array($_POST['headline'],$_POST['item']);

// RUN A QUERY USING THE QUERY STRING AND ARRAY
$RS_NewsItem01 = sqlsrv_query($conn,$SQLStmt,$params);

// TEST TO SEE IF THE STRING VARIABLE === FALSE (HINT: IT NEVER EQUALS FALSE)
if( $SQLStmt === false ) {

    // IF THE STRING EQUALS FALSE, REDIRECT THE CLIENT BROWSER
    $status = "0045";
    header('Location: error.asp?1='.$status);
    exit();
}
// ETC.  
// BECAUSE THE TEST FOR FALSE WILL ALWAYS FAIL, 
// THE SCRIPT WILL COME HERE EVERY TIME
// EVEN IF THE QUERY FAILED

Open in new window

But that aside, please see the example in the PHP man page here:
http://www.php.net/manual/en/function.sqlsrv-next-result.php

These pages seem to have helpful information, too.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_24112956.html
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3eeb9539-4cb6-473c-8f53-562b7054f94a/how-to-get-the-last-insert-id-using-phpsqlsrv?forum=sqldriverforphp

I've seen a few comments that suggest not using the @@ notation because it can be unreliable from one release to the next.

My recommendation would be to use var_dump() to print out any intermediate variables so you can verify their values before using them in other parts of the script.  In PHP, var_dump() is often your best friend!
http://php.net/manual/en/function.var-dump.php
0
 
Dave BaldwinFixer of ProblemsCommented:
I don't see that NOCOUNT is doing you any good.  And I would not try to use the ID to create the filename.  Even MySQL which has a statement to get the last id warns against using it because it is possible for the insert statement and the request for the last id to become separated and invalid.  Use some other method to create the filename and this "problem" disappears.
0
 
Ray PaseurCommented:
Regarding MySQL's last inserted id, here is one worthwhile note:
http://php.net/manual/en/mysqli.insert-id.php#74923

While this is not directly applicable to the question, it's worth noting that there are many ways to use SQL just a little bit wrong -- with surprising results!
0
 
Anthony PerkinsCommented:
I am attempting to insert a record into a SQL Server database and then grab the last generated primary key for use as a file name for an image.
The way you can do this is by using SCOPE_IDENTITY() or alternatively you can use the OUTPUT clause in the INSERT statement to return all the inserted rows.

Do not use @@IDENTITY as depending if there is an INSERT TRIGGER on the table it may not return the correct value.
0
 
splantonAuthor Commented:
Hi Guys,

Not really getting much further on this. I have taken on board the comments and am now using SCOPE_IDENTITY()

My current code looks like this:

<?php
	$serverName = "<servername>";
	$uid = "<myuid>";
	$pwd = "<mypwd>";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"DirectSpeakers_test");
	$conn = sqlsrv_connect($serverName, $connectionInfo);

	$SQLStmt = "INSERT INTO NewsItem (Headline,Item) VALUES ('1','1'); SELECT SCOPE_IDENTITY();";

	$recordset=sqlsrv_query($conn, $SQLStmt); 
	sqlsrv_next_result($recordset); 
	sqlsrv_fetch($recordset); 
	echo sqlsrv_get_field($recordset, 0); 
?> 

Open in new window


I am still not getting the last inserted Id and I am tearing my hair out over this. Unfortunately I am required to get the last ID and do it in PHP - no choice!
0
 
Anthony PerkinsCommented:
Why did you remove the SET NOCOUNT ON?  That will force you to get the next result set, in order to skip over the information regarding the number of rows affected.  From SQL Server BOL:
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
0
 
splantonAuthor Commented:
I removed it simply because I am fishing around in the dark trying to get this working. I am not an expert in SQL Server, nor am I a PHP programmer, however I have ten years classic ASP and am stuck with trying to get a piece of code to return an ID so that I can use it.

The code now looks like this:

<?php
	$serverName = "<servername>";
	$uid = "<myuid>";
	$pwd = "<mypwd>";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"DirectSpeakers_test");
	$conn = sqlsrv_connect($serverName, $connectionInfo);

	$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) VALUES ('1','1'); SELECT SCOPE_IDENTITY();";

	$recordset=sqlsrv_query($conn, $SQLStmt); 
	sqlsrv_next_result($recordset); 
	sqlsrv_fetch($recordset); 
	echo sqlsrv_get_field($recordset, 0); 
?>

Open in new window


Needless to say - it does not return anything and I have no idea why?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
How about this?
<?php
	$serverName = "<servername>";
	$uid = "<myuid>";
	$pwd = "<mypwd>";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"DirectSpeakers_test");
	$conn = sqlsrv_connect($serverName, $connectionInfo);

	$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) OUTPUT NewsItemID VALUES ('1','1');";

	$recordset=sqlsrv_query($conn, $SQLStmt); 
	sqlsrv_next_result($recordset); 
	sqlsrv_fetch($recordset); 
	echo sqlsrv_get_field($recordset, 0); 
?>

Open in new window

That should retrieve the newly inserted ID.
0
 
Anthony PerkinsCommented:
First of all, can you run the T-SQL code in SSMS and return the correct value?

Now that you have added SET NOCOUNT ON I would think that the following line is redundant:
sqlsrv_next_result($recordset);
And maybe even:
sqlsrv_fetch($recordset);
But then my knowledge of PHP could probably fit on a postage stamp.
0
 
splantonAuthor Commented:
T-SQL - checks out just fine - Tested it again to be sure, but the SQL is from a classic ASP page that needs replacing with a PHP equivelant. It works in ASP and in SSMS.

Tried removing first the

sqlsrv_next_result($recordset);

line and then the

sqlsrv_fetch($recordset);

Still nothing returned...
0
 
splantonAuthor Commented:
Qlemo,
tried this:

<?php
	$serverName = "<servername>";
	$uid = "<myuid>";
	$pwd = "<mypwd>";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"DirectSpeakers_test");
	$conn = sqlsrv_connect($serverName, $connectionInfo);

	$SQLStmt = "SET NOCOUNT ON; INSERT INTO NewsItem (Headline,Item) OUTPUT NewsItemID VALUES ('1','1');";

	$recordset=sqlsrv_query($conn, $SQLStmt); 
	sqlsrv_next_result($recordset); 
	sqlsrv_fetch($recordset); 
	echo sqlsrv_get_field($recordset, 0); 
?> 

Open in new window



Gave me the following:


PHP Warning:  sqlsrv_next_result() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\directspeakers (subdomain test)\www\test2.php on line 11
PHP Warning:  sqlsrv_fetch() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\directspeakers (subdomain test)\www\test2.php on line 12
PHP Warning:  sqlsrv_get_field() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\directspeakers (subdomain test)\www\test2.php on line 13


:(
0
 
Anthony PerkinsCommented:
T-SQL - checks out just fine
Good.  So we know the T-SQL is correct.  Now all we need is someone who knows PHP well...
0
 
Dave BaldwinFixer of ProblemsCommented:
Using a working database/table and PHP code, I can not get this code to produce ANY results at all.  sqlsrv_next_result() always returns nothing.
	$recordset=sqlsrv_query($conn, $SQLStmt); 
	sqlsrv_next_result($recordset); 
	sqlsrv_fetch($recordset); 
	echo sqlsrv_get_field($recordset, 0); 

Open in new window

0
 
splantonAuthor Commented:
I found this solution on this link
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_24112956.html

Tested it with code like this:

<?php
	$serverName = "ISERV53\SQLEXPRESS";
	$uid = "directspeakers_test";
	$pwd = "secure1";
	$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>"DirectSpeakers_test");
	$conn = sqlsrv_connect($serverName, $connectionInfo);

	$tsql = "INSERT INTO NewsItem (Headline,Item) VALUES ('1','1');";

	/* Set up the parameters array. Parameters correspond, in order, to
	question marks in $tsql. */
	
	/* Create the statement. */
	$stmt = sqlsrv_prepare( $conn, $tsql);
	if( $stmt )
	{
	echo "Statement prepared.\n";
	}
	else
	{
	echo "Error in preparing statement.\n";
	die( print_r( sqlsrv_errors(), true));
	}
	
	/* Execute the statement. Display any errors that occur. */
	if( sqlsrv_execute( $stmt))
	{
	echo "Statement executed.\n";
	}
	else
	{
	echo "Error in executing statement.\n";
	die( print_r( sqlsrv_errors(), true));
	}

	/* Execute the statement. Display any errors that occur. */
	if( sqlsrv_execute( $stmt))
	{
	echo "Statement executed.\n";
	}
	//Get Last Insert ID
	sqlsrv_next_result($stmt);
	sqlsrv_fetch($stmt);
	$insert_id = sqlsrv_get_field($stmt, 0);
	
	var_dump($insert_id);
	echo($insert_id);
?> 

Open in new window


I put the vardump in there to see what was happening and all I am getting is a 'bool(false) ' returned.
0
 
splantonAuthor Commented:
I finally cracked it with some guidance from other users, but predominantly had to come up with a working solution myself as there was no clear working solution provided by anyone else.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If found your solution helpful for something very similar.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
For mssql on windows, I found this to be the easiest solution for me using pdo/paramterized insert   http://msdn.microsoft.com/en-us/library/ff628155(SQL.90).aspx

After being used to stored proc or ";newid=scope_identity(); in the query, this was super easy.
$lastRow = $conn->lastInsertId('named_table_just_inserted');
   echo $lastRow . "\n";

   // defaults to the last table you inserted
   $lastRow = $conn->lastInsertId();

Open in new window

0
 
splantonAuthor Commented:
Scott,
Glad you found it helpful. I really came unstuck because I am moving from ten years plus of classic ASP  into PHP development. It all seems so similar until it isn't. :)
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I'm in the same boat!  Some things are amazingly easy and others easy to get in trouble.   I'm still sticking with mssql 2012 for now.    

Best of luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.