How to get a tuple's values that is inserted into PostgreSql db using php?

Question

I am trying to insert a tuple into PostgreSql using PHP. After inserting the tuple I want to get the value of one of the columns of the inserted row. This column value is generated automatically by the db as it is defined as SERIAL in DDL.

    $query = "INSERT INTO posts VALUES('$title','$msg',$x,$y,'$me')";
    $result = pg_query($dbh,$query);
    if (!$result) {
            $status = 0;
    } else {
            $status = 1;
    }
    $row = pg_fetch_assoc($result);
    $pID = $row['postID'];
    $array = array(
            'status' => $status,
            'pID' => $pID
    );

    #Delete query is only for checking if the code is working.
    $query = "DELETE FROM posts WHERE postID='$pID'";
    $result = pg_query($dbh,$query);

The table 'posts' has following DDL:

CREATE  TABLE  posts
( title CHAR(20),
  content CHAR(42),
  x_coor INTEGER,
  y_coor INTEGER,
  userID CHAR(50),
  time_stamp TIMESTAMP default current_timestamp,
  postID SERIAL,
  PRIMARY KEY(postID),
  FOREIGN KEY (userID) REFERENCES users ON DELETE CASCADE);

I want to get the value of 'postID' column when I insert a row into the table 'posts' to perform additional functions based on postID. I have tried pg_fetch_assoc, pg_fetch_row, pg_fetch_object & pg_fetch_array. None of those seem to work. (I made appropriate modifications to the code when using each of those functions.)

Is there something incorrect in the code or perhaps I am missing something?

Thanks!

Answer 1

A good way is the returning clause:

INSERT INTO posts 
VALUES('$title','$msg',$x,$y,'$me')
RETURNING id;

My PHP is a bit rusty, but it'd look something like:

$query = "INSERT INTO posts VALUES('$title','$msg',$x,$y,'$me') RETURNING id";
$result = pg_query($dbh, $query);
if ($result) {
    $row = pg_fetch_row($result); 
    $inserted_id = $row[0];
}