How to get return id after insert in php & pgsql

Question

I created two tables: employee and employeedetails.

In employee table: id (pkey) userid,pswd,lastlogindate.

In employeedetails table: id(pkey), sno(forienkey), name,age,designation,registration,salary.

I want employee id in sno(fkey) of employeedetails sequently, but I insert manually, I want it inserted automatically.

<?php
$host        = "host=127.0.0.1";
$port        = "port=5432";
$dbname      = "dbname=books";
$credentials = "user=postgres password=postgres";

$db = pg_connect( "$host $port $dbname $credentials"  );
if(!$db){
    echo "Error : Unable to open database\n";
} else {
    echo "Opened database successfully\n";
}

if(isset($_POST['register']))
{
    $userid=$_POST['userid'];
    $pswd=$_POST['pswd'];
    $name=$_POST['name'];
    $age=$_POST['age'];
    $designation=$_POST['designation'];
    $salary=$_POST['salary'];
    $registartiondate=date("Y-m-d",time());            
    $sql="insert into employedetails(sno,name,designation,registartiondate,age,salary) values('".$name."','".$designation."','".$registartiondate."','".$age."','".$salary."')";
    $query= " insert into employe (userid,pswd) values ('".$userid."','".$pswd."')";

    $exe=pg_query($db,$query);

    $q = pg_query($db,$sql);

    if($q)
    {
        echo "INSERTED SUCCESSFULLY";
    }
    else
    {
        echo pg_last_error($db);
    }
}
?>

<html>
<form method="post">
userid<input type="text" name="userid"/>
<br>
pswd<input type="text" name="pswd" /><br/>
name<input type="text" name="name" /><br/>
age<input type="text" name="age" /><br/>
designation<input type="text" name="designation" /><br/>
salary<input type="text" name="salary" /><br/>

<input type="submit" value="Submit" name="register"/>
</form>
</html>

[Check This](http://stackoverflow.com/questions/29035929/get-last-insert-id-of-postgresql) — VipindasKS, Apr 15 '16 at 07:04
Your queries are open for [SQL injection](https://en.wikipedia.org/wiki/SQL_injection)! — pozs, Apr 15 '16 at 10:26

score 1 · Answer 1 · answered Apr 15 '16 at 08:00

1

Postgresql supports the RETURNING clause that can project a set updated, inserted en even deleted exactly like in a SELECT statement:

INSERT INTO employee (name, password)
VALUES ($1::text, $2::text) 
RETURNING employee_id, name, '****' as password
;

employee_id | name | password
    1       | john | ****
(1 row)

It will return the asked fields of the inserted rows.

answered Apr 15 '16 at 08:00

greg

3,354
1
24
35

i am get result like this Resource id #8 – Harshan Morawaka Dec 18 '19 at 11:47
Exactly like a SELECT statement. – greg Dec 22 '19 at 11:34

How to get return id after insert in php & pgsql

1 Answers1