LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 12-04-2005, 03:09 AM   #1
vharishankar
Senior Member
 
Registered: Dec 2003
Posts: 3,142
Blog Entries: 4

Rep: Reputation: 121Reputation: 121
SQL - getting the primary key autogenerated of an INSERT statement


I want to use the autogenerated primary key of a INSERT statement to use in another insert statement for another table.

I am using SQLite in PHP.

Here is the code example:
PHP Code:
     $result sqlite_exec ($db_resource'INSERT INTO mytable (field1, field2, field3)
                            VALUES (\'something\', \'something\', \'something\');'
); 
I want the autoincremented primary key of this insertion for use in another query, but I don't want to use SELECT, because it might yield a wrong answer. Supposing another insertion has taken place before I call select, I might get the wrong row.

Any help will be appreciated.

Last edited by vharishankar; 12-04-2005 at 03:12 AM.
 
Old 12-04-2005, 06:52 AM   #2
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
You would need to create the table with a "PRIMARY KEY" that has the "AUTOINCREMENT" constraint:
http://www.sqlite.org/lang_createtable.html
 
Old 12-04-2005, 07:19 AM   #3
keefaz
Senior Member
 
Registered: Mar 2004
Distribution: Slackware
Posts: 4,614

Rep: Reputation: 136Reputation: 136
Just after yout insert statement, use
PHP Code:
$id sqlite_last_insert_rowid $db_resource ); 
 
Old 12-05-2005, 02:47 AM   #4
vharishankar
Senior Member
 
Registered: Dec 2003
Posts: 3,142
Blog Entries: 4

Original Poster
Rep: Reputation: 121Reputation: 121
david ross, I know it's a primary key and I set it to autoincrement. I just need to retrieve that value. That's all.

Keefaz, thanks. I'll try that today. However is it sure to work in a multiuser environment like a forum where there is a chance that insertions can happen into the database simultaneously by so many users? Suppose there is another insertion immediately after that one which is inserted before I can get the key which is the one I want. What happens then? Or is this scenario likely at all?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL statement to get the last row in a table oulevon Programming 11 04-03-2009 08:30 PM
help with simple sql statement sekelsenmat Programming 6 08-06-2005 12:01 PM
mysql sql statement help mrtwice Programming 4 12-02-2003 10:04 AM
Get the date on PHP and insert it into SQL poeta_boy Programming 4 11-26-2003 03:25 AM
SQL statement glj Programming 1 10-12-2001 09:29 AM


All times are GMT -5. The time now is 12:46 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration