Solved

PHP Syntax question re. @@Identity with SQL Server

Posted on 2014-04-22
23
578 Views
Last Modified: 2014-05-21
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,
0
Comment
Question by:splanton
  • 9
  • 4
  • 4
  • +3
23 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40015266
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40015271
Same idea on line 13 and 15.  Man page here:
http://php.net/manual/en/function.sqlsrv-query.php
0
 
LVL 2

Author Comment

by:splanton
ID: 40015531
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40015657
...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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40015845
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40016047
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40016526
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
 
LVL 2

Author Comment

by:splanton
ID: 40017476
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40017736
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
 
LVL 2

Author Comment

by:splanton
ID: 40017892
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 40017989
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 40018010
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
 
LVL 2

Author Comment

by:splanton
ID: 40018060
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
 
LVL 2

Author Comment

by:splanton
ID: 40018065
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40018204
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40018298
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
 
LVL 2

Author Comment

by:splanton
ID: 40019557
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
 
LVL 2

Accepted Solution

by:
splanton earned 0 total points
ID: 40019734
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
 
LVL 2

Author Closing Comment

by:splanton
ID: 40029085
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40079049
If found your solution helpful for something very similar.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40079129
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
 
LVL 2

Author Comment

by:splanton
ID: 40080174
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40080299
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses four methods for overlaying images in a container on a web page
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now