Using Adodb for php/postgresql, return row ID after insert

Question

When I insert a row, it increments the ID field using SERIAL.

How can I return the newly created ID number right after the insert?

(Database is Postgresql.)

score 18 · Accepted Answer · answered Feb 22 '10 at 04:44

18

$db->Insert_ID();

answered Feb 22 '10 at 04:44

Nirmal

9,391
11
57
81

score 7 · Answer 2 · edited Apr 08 '13 at 11:58

7

This code works nicely:

$insertId = $adodb->getOne("INSERT INTO test (str) values ('test') RETURNING id");

edited Apr 08 '13 at 11:58

answered Apr 08 '13 at 11:40

user2116547

71
1
2

score 3 · Answer 3 · answered Feb 22 '10 at 07:28

3

Treat your INSERT the same as a SELECT and use RETURNING in your INSERT-query:

INSERT INTO foo (bar) VALUES('Doe') RETURNING id;

Fetch the result and you're done. Works since version 8.2

answered Feb 22 '10 at 07:28

Frank Heikens

117,544
24
142
135

score 2 · Answer 4 · edited Oct 20 '12 at 03:44

Insert_ID() in adodb returns empty value or in some case it return 0.

The postgresql I worked on is in 9.1 version.

$_sql = 'INSERT INTO '.$_table.' (';
$_sql .= implode(', ',$arr_fld_names);
$_sql .= ") VALUES ('";
$_sql .= implode("', '",$arr_fld_values);
$_sql .= "')";

if ($echo){
    echo $_sql;
}

$_return = $this->Execute($_sql);
$this->mLastInsertedId = $this->mConn->Insert_ID(); // Keep track of the last inserted ID.

Edit: I have added a dirty way:

$_sql = 'INSERT INTO '.$_table.' (';
$_sql .= implode(', ',$arr_fld_names);
$_sql .= ") VALUES ('";
$_sql .= implode("', '",$arr_fld_values);
$_sql .= "') RETURNING id";

if ($echo){
    echo $_sql;
}

$_return = $this->Execute($_sql);
preg_match_all('/([\d]+)/', $_return, $ret_val);
$this->mLastInsertedId = $ret_val[0][0]; // Keep track of the last inserted ID.

score 0 · Answer 5 · answered Apr 15 '18 at 19:01

I like to use adodb to avoid writing SQL. I need to insert a record on table1 with text1 and at same time a record on table2 with other data and the created ID on table1 as a reference. I like the idea of using transaction and AutoExecute

$db->StartTrans();
{
    $array_table1 = array('text_field'=>'text1');
    $db->AutoExecute("table1", $array_table1, 'INSERT');

    if ($lastid=$db->Insert_ID()) {
        $array_table2=array(
            'text_field'=>'other text',
            'id_of_table1'=>$lastid);
        $db->AutoExecute('other_table', $array_table2, 'INSERT');
    } else {
        $db->RollbackTrans();
    }

}
$db->CompleteTrans();
$result=$db->_transOK;

Autoexecute still lacks of an option for returning the Insert_Id()

Using Adodb for php/postgresql, return row ID after insert

5 Answers5