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,314 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

777 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