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,694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

627 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