return id from database after insert

Question

i have a php that responds to an ajax and inserts a row into database, this is the code

if($action == "insertCalendarEvents") {
        $calendar_group = $_GET["calendar_group"];
        $event_name = "'" . $_GET["event_name"] . "'";
        $event_datestart = "'" . $_GET["event_datestart"] . "'";
        $event_datestop = "'" . $_GET["event_datestop"] . "'";
        $event_timestart = $_GET["event_timestart"] != "" ? "'" . $_GET["event_timestart"] . "'" : "null";
        $event_timestop = $_GET["event_timestop"] != "" ? "'" . $_GET["event_timestop"] . "'" : "null";
        $event_info = "'" . $_GET["event_info"] . "'";

        require_once("connect.php");
        $query = "INSERT INTO calendar_events (calendar_group, event_name, event_datestart, event_datestop, event_timestart, event_timestop, event_info) VALUES (" . $calendar_group . ", " . $event_name . ", " . $event_datestart . ", " . $event_datestop . ", " . $event_timestart . ", " . $event_timestop . ", " . $event_info . ")";
        $result = pg_query($connect, $query);
        if(!$result)
            die("error 1"); // query error
    }

what i want to achieve it to make this code to return the id that the database auto-incremented and echo it back to the ajax function, the problem is that i cant get the last id because someone else might insert data after me and get the wrong id.. i would be rly happy to know if there is a workaround on this issue Thanks in advance, Daniel!

EDIT: i want to add that i am using PostgreSQL as database, maybe it has a module to do that

Seems security is a futile endeavour. – Anand Shah Mar 23 '12 at 12:28 — Anand Shah, Mar 23 '12 at 12:28

score 2 · Accepted Answer · answered Mar 23 '12 at 12:39

2

Checking http://wiki.postgresql.org/wiki/FAQ#How_do_I_get_the_value_of_a_SERIAL_insert.3F, it seems you can do queries like:

INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id

I'm assuming you call pg_fetch_assoc() on the query resource and treat it like a SELECT?

answered Mar 23 '12 at 12:39

Michael

11,912
6
49
64

Thank you very much, MichaelRushton! this is a huge step forward in my programming experience because everytime i code something related to database i encouter this problem :) – Pacuraru Daniel Mar 23 '12 at 22:39

score -1 · Answer 2 · answered Mar 23 '12 at 12:27

-1

use @@identity or scope_identity to get the the last index you inserted.

after your insert statement use select @@identity

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

answered Mar 23 '12 at 12:27

Timmerz

6,090
5
36
49

can you be more specific pls.. i have the autoincremented field named event_id in the table calendar-events .. do i need to use SELECT @@event_id ? – Pacuraru Daniel Mar 23 '12 at 12:42
in that case I think what you are looking for is currval() – Timmerz Mar 23 '12 at 12:51
http://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id – Timmerz Mar 23 '12 at 12:52
yes i have tries currval and it works perfectly, the only thing im not 100% sure is what happens if more ppl add events in the same time in database, can currval return a wrong value than ? (and by wrong i mean not that value you should get, but the value of someone else) – Pacuraru Daniel Mar 23 '12 at 12:55
if you followed that link I posted, you will see he says: "No you are mistaken. currval() is "local" to your current connection. So there is no problem using it in a multi-user environment. That's the whole purpose of a sequence." – Timmerz Mar 23 '12 at 12:57
http://www.postgresql.org/docs/7.4/static/functions-sequence.html says, "Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer even if other sessions are executing nextval meanwhile." – Timmerz Mar 23 '12 at 13:00
also I don't use postegres, so you might want to use MichaelRushton's answer....RETURNING id – Timmerz Mar 23 '12 at 13:02
Thank you very much Timmerz. I have tried currval function too and it works as i expected. I see you said you dont use postgresql, is there a reason or you use a different one ? thank you again, i appreciate your help on this issue – Pacuraru Daniel Mar 23 '12 at 22:43
I use sql server. that was my original answer. I'm not familiar with any other databases...glad you got it figured out. – Timmerz Mar 24 '12 at 00:30

return id from database after insert

2 Answers2