PHP - pg_query getting INSERTED row

Zombrex

Hi guys

Working a program that inserts data into a PostgreSQL database from PHP using the pg_query.

once the data has been inserted I need to see what new row has been inserted, because the database has a auto-updating sequence number.

So say I insert a new row and it is given a new number 46 I need to see that from the result of the insert, as this will be used further down.

So my question is this, is there any way using pg_query to view the full row that resulted from the query

Gosh

Not 100% sure but I think the following may work

[PHP]
$query = "SELECT currval('" .$tablename .'_' .$pkey ."_seq')";
$result = pg_query($link, $query) or trigger_error($this, E_USER_ERROR);
$id = pg_fetch_result($result, 0, 0);
[/PHP]

replace tablename and pkey with your names

Zombrex

Gosh wrote:

Not 100% sure but I think the following may work

[PHP]
$query = "SELECT currval('" .$tablename .'_' .$pkey ."_seq')";
$result = pg_query($link, $query) or trigger_error($this, E_USER_ERROR);
$id = pg_fetch_result($result, 0, 0);
[/PHP]

replace tablename and pkey with your names

Yeah I had thought of that, the problem is that this is for a web based system with many users. To use the above one would have to guarrentee that nothing has been inserted after my row was inserted, otherwise I could get back the sequence number for a different row. It is very unlikely that that would happen, but because of the sensitive nature of the system (it has to work perfectly, and will have to for a few years), my bosses wouldn't be happy unless I am certain that I can get back the row I inserted.

seamus

Closest thing I could find online was something which attempt to emulate the mysql_insert_id for PGSQL

function pg_insert_id($pg_result, $serial_column, $table)
{
	$oid = pg_last_oid($pg_result);
	$query = "SELECT $serial_column FROM $table WHERE oid = $oid";
	$result = pg_exec($query);
	$row = pg_fetch_row($result, 0);
	return($row[0]);
}

Zombrex

Gosh wrote:

Not 100% sure but I think the following may work

[PHP]
$query = "SELECT currval('" .$tablename .'_' .$pkey ."_seq')";
$result = pg_query($link, $query) or trigger_error($this, E_USER_ERROR);
$id = pg_fetch_result($result, 0, 0);
[/PHP]

replace tablename and pkey with your names

I stand corrected Gosh, I've just read on a PHP site that currval is session safe, so it would only be a problem if my session was still updating, which I can be sure it isn't

Thanks guys

Also, in case anyone comes across this looking for help another way of doing it (if you don't have sequences) is to us pg_last_oid ($result) .. this will give you the OID of the inserted row, and you can then get it using a normal select statement