Mysql_Insert_Id Alternative for Postgresql

mysql_insert_id alternative for postgresql

From the PostgreSQL point of view, in pseudo-code:

 * $insert_id = INSERT...RETURNING foo_id;-- only works for PostgreSQL >= 8.2. 

 * INSERT...; $insert_id = SELECT lastval(); -- works for PostgreSQL >= 8.1

 * $insert_id = SELECT nextval('foo_seq'); INSERT INTO table (foo...) values ($insert_id...) for older PostgreSQL (and newer PostgreSQL)

pg_last_oid() only works where you have OIDs. OIDs have been off by default since PostgreSQL 8.1.

So, depending on which PostgreSQL version you have, you should pick one of the above method. Ideally, of course, use a database abstraction library which abstracts away the above. Otherwise, in low level code, it looks like:

Method one: INSERT... RETURNING

// yes, we're not using pg_insert()
$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_row($result);
$insert_id = $insert_row[0];

Method two: INSERT; lastval()

$result = pg_execute($db, "INSERT INTO foo (bar) values (123);");
$insert_query = pg_query("SELECT lastval();");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];

Method three: nextval(); INSERT

$insert_query = pg_query($db, "SELECT nextval('foo_seq');");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];
$result = pg_execute($db, "INSERT INTO foo (foo_id, bar) VALUES ($insert_id, 123);");

The safest bet would be the third method, but it's unwieldy. The cleanest is the first, but you'd need to run a recent PostgreSQL. Most db abstraction libraries don't yet use the first method though.

convert mysql_insert_id and mysqli_multi_query to Postgresql

Switch to PDO

Run like mad away from that tutorial you are following. Either it was written many years ago or it was written by someone who is not knowledgeable on the subject. This is a horrible tutorial. This is why:

           $order_details .= "
            INSERT INTO tbl_order_details(order_id, product_name, product_price, product_quantity)
            VALUES('".$order_id."', '".$values["product_name"]."', '".$values["product_price"]."', '".$values["product_quantity"]."');
            ";

The query is generated using string concatenation, which is a very unsafe practice. The preferred and the safe way is to use prepared statements. Secondly you are better off leanring PDO rather than mysqli because PDO works with both mysql and PostgreSQL. Thirdly mysqli_mutli_query is hardly ever used.

        $stmt = $db->prepare("INSERT INTO tbl_order_details(order_id,product_name, product_price, price_quantity) VALUES(:order_id, :product_name, :product_price, :price_quantity)";

        foreach($_SESSION["shopping_cart"] as $keys => $values)
        {
            $stmt->bindParam('order_id'], $order_id);
            $stmt->bindParam('product_name', $values['product_name']);
            $stmt->bindParam('product_price',$values['product_price']);
            $stmt->bindParam('product_quantity'], $values['product_quantity']);
            $stmt->execute()
        }

The above does the same thing much more safely and more importantly works on both postgresql and mysql

Last insert id

Getting the last insert id with PDO is easy.

$db->lastInsertId('sequence_name');

Where sequence_name is the name of the sequence for your serial column in postgresql. (Serial is the equivalent for auto_increment)

How can i get the inserted seq without using the seq_name?

According to the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html
there is a function lastval() that

Return the value most recently returned by nextval in the current
session. This function is identical to currval, except that instead of
taking the sequence name as an argument it fetches the value of the
last sequence that nextval was used on in the current session. It is
an error to call lastval if nextval has not yet been called in the
current session.

Fetching one single row from database using Perl

You might want to set column alias for query,

$query = "SELECT MAX(id) AS id FROM people";

as postgres is putting his own alias for you, and that is max.

If all you want is last inserted id, you can

my $query = "INSERT INTO people (firstname, lastname, age) 
         VALUES (?,?,?)
         returning id
";

and fetch query as you would do with select. (check pg docs)

Get last insert id of Postgresql

PostgreSQL will (by default) create a sequence called 'user_id_seq'. for example if your table name is user. then it is user_id_seq

You can then do something like:

$strTable = "user";
$last_insert_id = $objPDO->lastInsertId("$strTable_id_seq");

See for multiuser-safe methods http://www.postgresql.org/docs/9.0/interactive/functions-sequence.html

See other ways
mysql_insert_id alternative for postgresql

EDIT: as per comment of Eli

//if your table name in model is **user**
$strTable = "user";
$model = new User(); 

// do your stuff
$model->save();

$last_insert_id = $model->lastInsertId("$strTable_id_seq");

OR

If your model is called Model, and has a property called id (aka, the PK of the table), then you can acces this way:

//...
$model = new Model();
// do your stuff....
$model->save();

$the_id = $model->id;

Alternative to PDO::lastInsertId / mysql_insert_id

If you go the route of ADOdb (http://adodb.sourceforge.net/), then you can create the insert ID before hand and explicitly specific the ID when inserting. This can be implemented portably (ADOdb supports a ton of different databases...) and guarantees you're using the correct insert ID.

The PostgreSQL SERIAL data type is similar except that it's per-table/per-sequence, you specify the table/sequence you want the last insert ID for when you request it.

PHP Postgres: Get Last Insert ID

INSERT, UPDATE and DELETE in PostgreSQL have a RETURNING clause which means you can do:

INSERT INTO ....
RETURNING id;

Then the query will return the value it inserted for id for each row inserted. Saves a roundtrip to the server.

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

---

Related Topics