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,473 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 35

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 35

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ahax pagination 9 47
Google Dork query 7 56
PHP Curl Problem 10 31
Set a time limit on Wordpress Cookie function. 3 9
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

680 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