Help answer threads with 0 replies.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
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.


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

Rep: Reputation: 136Reputation: 136
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
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 66
You would need to create the table with a "PRIMARY KEY" that has the "AUTOINCREMENT" constraint:
Old 12-04-2005, 07:19 AM   #3
LQ Guru
Registered: Mar 2004
Distribution: Slackware
Posts: 5,388

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

Original Poster
Rep: Reputation: 136Reputation: 136
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?


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 > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 06:59 PM.

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