Solved

How do I retrieve the last inserted id in postgresql using "returning" in insert sql clause in Php?

Posted on 2013-12-24
4
3,118 Views
Last Modified: 2013-12-24
Hi everyone!

I have shifted from mysql to postgresql, and I'm using php. I need to use the replacement of  the mysql_last_insert() function for retrieving the last inserted id. I have explored the currval(), lastval() etc. functions and also the RETURNING restriction/option in the insert sql statement.

For the task at hand, I prefer the insert statement using the RETURNING clause. Its probably a very simple thing but after the select statement with the returning clause, HOW DO I CAPTURE AND USE THE RETURNED VALUE IN PHP? I have tried but am missing something very simple.

The pseudo code is as  follows.

the table name is products,
product_id SERIAL AUTO INCREMENT PRIMARY KEY,
product_name varchar (50) NOT NULL.

<?php
require_once('conn.php');

$query = INSERT INTO product (product_name) VALUES ('$_POST[product_name]') RETURNING product_id;
$result = pg_query($query);

/*HOW DO RETRIEVE AND USE THE RETURNED product_id THAT IS OUTPUT AS A RESULT OF THE QUERY? USUALLY WE RETRIEVE AN ASSOCIATIVE ARRAY BUT HERE THERE IS ONLY ONE NUMERIC DATA RETURNED.*/

I shall be grateful for the help and I give thanks in advance!
?>
0
Comment
Question by:786aslamkhan
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39737711
Add this:

$row = pg_fetch_row($result);
$new_id = $row['0'];

HTH,
Dan
0
 

Author Comment

by:786aslamkhan
ID: 39737723
Sorry experts!!! IT WAS TOO SIMPLE!!! ... Just an oversight! The answer is as follows.

$result = pg_query($query);
                  while ($row = pg_fetch_array($result)){
                        $id = $row['product_id'];
                        echo "Id: " . $id;
                        }
I apologize if anyone has spent even a minute on this in vain. Please do forgive this oversight and momentary lapse of focus!

However to keep the question alive could anyone please tell me how to get the last insert id using currval() or lastval() please?
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39737802
Here's a discussion on currval(): http://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id

The problem is that this is NOT a replacement for mysql_last_insert().

It's local to the transaction and will return an error if nextval was never called in that transaction (for ex. in an insert).

In other words, used by itself in a transaction, currval(table-name_field-name_seq) will always return an error:
http://www.postgresql.org/docs/8.4/static/functions-sequence.html
0
 

Author Comment

by:786aslamkhan
ID: 39737880
Thanks DanCraciun so very much for your time and links. I had the opportunity to re-visit the discussion on currval().
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now