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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

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

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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().
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read the posting code guidelines. :?

did you read my previous post?
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

Post 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();
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

Post 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);

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

did you even try what I posted?
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

Post 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?
tdelobe
Forum Commoner
Posts: 41
Joined: Thu Aug 07, 2003 2:28 pm
Location: washington, dc

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

In SQL Server, just do:

Code: Select all

SELECT @@identity
After your INSERT statement
Post Reply