Page 1 of 1

Selecting the ID of a row you just inserted in the Database

Posted: Tue Apr 05, 2005 10:09 pm
by tdelobe
First...I am using php 4x with a SQL Server DB.

I am trying to access the ID column of a row I just inserted into the database. I have done this in .NET before using @@Identity but can't figure out how to do it in PHP.

Basically for the row I insert into the DB I want to select that ID column so I can take that variable ($ID) and add it as a varible to display in the email I send out that has all the values I just inserted into the database.

See code below for what I am currently doing. Can anyone give me some code to accomplish this?

Code: Select all

global $link;
	
	$host="";
	$user="";
	$password="";
	$database = "";
	
	$first_name=$_POST['first_name'];
	$last_name=$_POST['last_name'];
	$title=$_POST['title'];
	$company=$_POST['company'];
	$phone=$_POST['phone'];
	$email=$_POST['email'];
	$headline=$_POST['headline'];
	$url=$_POST['url'];
	
	$link = mssql_connect($host,$user,$password)
	or die ("couldn't connect to server");
	$db = mssql_select_db($database,$link)
	or die ("Couldn't select database");
	
	$query = "INSERT INTO OutsideNewsRelease (FirstName,LastName,Title,Company,Phone,Email,Headline,URL,SubmissionDate) VALUES ('$first_name','$last_name','$title','$company','$phone','$email','$headline','$url','$SubmissionDate')";
	mssql_query($query);
	mssql_close();
Phenom | Please use

Code: Select all

tags when posting code.[/size][/color]

Posted: Tue Apr 05, 2005 11:07 pm
by feyd
user comment on [url=http://php.net/mssql_get_last_message]mssql_get_last_message()[/url] wrote: php-contrib at i-ps dot nospam dot net
27-Jan-2002 11:24
With ref to last_insert_id;

you can also do "SELECT ident_current('table_name')" with msSQL, which is the same thing.
Don't forget that this counts as a seperate SQL query, so you will have to fetch the results as well, which isn't as neat as MySQL_insert_id().

Posted: Tue Apr 05, 2005 11:36 pm
by tdelobe
I added this code, but I can't get the variable to show up.

Code: Select all

$query = "INSERT INTO OutsideNewsRelease (FirstName,LastName,Title,Company,Phone,Email,Headline,URL,SubmissionDate) VALUES ('$first_name','$last_name','$title','$company','$phone','$email','$headline','$url','$SubmissionDate')";
	mssql_query($query);
$query2 = "SELECT OutsideReleaseID FROM OutsideNewsRelease WHERE OutsideReleaseID = @@IDENTITY";
	mssql_query($query2);
	mssql_close();
I tried referencing $OutsideReleaseID later in my code but it comes up blank so the select is not working I guess. Any idea why?


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Wed Apr 06, 2005 12:07 am
by feyd
read the posting code guidelines. :?

did you read my previous post?

Posted: Wed Apr 06, 2005 9:55 am
by tdelobe
sorry, here is the code with the proper tag around it. I did read your post, but doesn't your select statement do the same thing that mind does? That select statement works fine in .NET, just can't reference that OutsideReleaseID variable with PHP, I am missing something in my code.

Code: Select all

$query = "INSERT INTO OutsideNewsRelease (FirstName,LastName,Title,Company,Phone,Email,Headline,URL,SubmissionDate) VALUES ('$first_name','$last_name','$title','$company','$phone','$email','$headline','$url','$SubmissionDate')";    mssql_query($query);$query2 = "SELECT OutsideReleaseID FROM OutsideNewsRelease WHERE OutsideReleaseID = @@IDENTITY";    mssql_query($query2);    mssql_close();

Posted: Wed Apr 06, 2005 11:04 am
by tdelobe
I found this code which does it for MySQL, but how can I do this with MSSQL?

Code: Select all

<?
$query = "INSERT INTO artist (name) VALUES ('$artist')";
mysql_query($query);
//now to retreive the id of the newly inserted record...
$artist_id= mysql_insert_id();
//this gets the last inserted id

$query = "INSERT INTO song (title, lyrics, id) VALUES ('$title','$lyrics','$artist_id()')";
mysql_query($query);

?>

Posted: Wed Apr 06, 2005 11:08 am
by feyd
did you even try what I posted?

Posted: Wed Apr 06, 2005 11:11 am
by tdelobe
i tried, but it did not work, however it might be because of how i placed it into my code. can you put a more complete code so I can check to see if i have a syntax issue or something?

Posted: Wed Apr 06, 2005 11:15 am
by tdelobe
This is what I tried using your code...

Code: Select all

<?
global $link;
	
	$host="";
	$user="";
	$password="";
	$database = "";
	
	$first_name=$_POST['first_name'];
	$last_name=$_POST['last_name'];
	$title=$_POST['title'];
	$company=$_POST['company'];
	$phone=$_POST['phone'];
	$email=$_POST['email'];
	$headline=$_POST['headline'];
	$url=$_POST['url'];
	
	$link = mssql_connect($host,$user,$password)
	or die ("couldn't connect to server");
	$db = mssql_select_db($database,$link)
	or die ("Couldn't select database");
	
	$query = "INSERT INTO OutsideNewsRelease (FirstName,LastName,Title,Company,Phone,Email,Headline,URL,SubmissionDate) VALUES ('$first_name','$last_name','$title','$company','$phone','$email','$headline','$url','$SubmissionDate')";
	mssql_query($query);
	
	$query = "SELECT ident_current('OutsideNewsRelease')";
	mssql_query($query);
	mssql_close();
?>

I got the following error:

Warning: mssql_query(): message: 'ident_current' is not a recognized function name. (severity 15) in D:\SiteRefresh\www\usta_web\forms\OutsideSubmit\index.php on line 31

Warning: mssql_query(): Query failed in D:\SiteRefresh\www\usta_web\forms\OutsideSubmit\index.php on line 31

Posted: Wed Apr 06, 2005 11:31 am
by feyd
as I said originally, it's a copy of a user comment post from php.net. I don't work will SQL Server, so I can't help much more than that.

Posted: Thu Apr 07, 2005 7:56 am
by Jean-Yves
In SQL Server, just do:

Code: Select all

SELECT @@identity
After your INSERT statement