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

Suggested Solutions

Title # Comments Views Activity
PHP curl issue VERBOSE output 18 83
Redirect 301 from one address  to another 5 25
Can SQL do Pythagoras? 8 20
Postgresql 9.6 10 10
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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.

808 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