currval of an index in Postgres to pass on?

I have several modules in a PHP program that access a postgres
database. The program needs to create a new "Name" record in a table
is it has a new name or use a name chosen from the database if it
doesn't have a new name. I want to load the chosen id in person_id if
the name is already there, but it needs to have the current value of
the name record index if it is not. I'm having trouble figuring our
how to load currval(person_id_seq) into person_id. My code snippet
appears below. I know that this code is wrong and I know that I need
to process the resulting query as an array, but I can't figure out how.

begin;

$query = "INSERT INTO \"tblPeople\"(\"fName\",\"mName\",\"lName
\", ivlweb, cnsweb)
VALUES ('$f_name_new',
'$m_name_new','$l_name_new', '$ivl_web_peop', '$cns_web_peop')";

echo "First Name query: " . $query . "
";
$pg_peop_ins = pg_query($query) or die("Can't execute first
query");

$query = "INSERT INTO \"tblContactInformation\"(\"contactItem
\",\"contactType\") VALUES ('$contact_info1','$contact_type')";
echo "Second Name query: " . $query . "
";
pg_contact_ins = pg_query($query) or die("Can't execute
2nd query");
$query = "INSERT INTO \"brdgPeopleContactInformation
\" (\"peopleId\",\"contactInformationId\",rank, type) VALUES
(currval('\"tblPeople_peopleId_seq
\"'),currval('\"tblContactInformation_contactInformationId_s eq\"'),
'$cont_rank', '$cont_loc')";
echo "Third Name query: " . $query . "
";
$pg_peop_cont_ins = pg_query($query) or die("Can't execute 3rd
query");
$query = "SELECT currval('\"tblPeople_peopleId_seq\"')";
$person_id = pg_query($query) or die("Can't execute 4th query");
echo "person id is " . $person_id . "
"; +++++++ (This gives
me a Resource # - I know this is because I didn't process it as an
array.) ++++++++
commit;

The other thing that puzzles me is that I thought the statements
between the begin and the commit should be treated as one
transaction. In this case, some of the tables get written and not
others between the begin and the commit.

Thanks for your time.

Carol


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: currval of an index in Postgres to pass on?

Check this link.

http://ca3.php.net/function.pg-query

The second example shows one way to perform a transaction, or the first
commit shows another potential way (sending a "BEGIN;" as it's own
query").

An easier way than using CURRVAL is to use the "RETURNING" clause on
your first INSERT, and then pass the value as a parameter.

http://www.postgresql.org/docs/8.3/interactive/sql-insert.ht ml

By the way, for obvious reasons, you should be using parameterized
queries to prevent SQL injection.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294=20
=20

-----Original Message-----
From: Carol Walter [mailto:walterc@indiana.edu]=20
Sent: Tuesday, June 09, 2009 4:35 PM
To: php-db@lists.php.net
Subject: [PHP-DB] currval of an index in Postgres to pass on?

I have several modules in a PHP program that access a postgres =20
database. The program needs to create a new "Name" record in a table =20
is it has a new name or use a name chosen from the database if it =20
doesn't have a new name. I want to load the chosen id in person_id if =20
the name is already there, but it needs to have the current value of =20
the name record index if it is not. I'm having trouble figuring our =20
how to load currval(person_id_seq) into person_id. My code snippet =20
appears below. I know that this code is wrong and I know that I need =20
to process the resulting query as an array, but I can't figure out how.

begin;

$query =3D "INSERT INTO
\"tblPeople\"(\"fName\",\"mName\",\"lName=20
\", ivlweb, cnsweb)
VALUES ('$f_name_new', =20
'$m_name_new','$l_name_new', '$ivl_web_peop', '$cns_web_peop')";
=09
echo "First Name query: " . $query . "
";
$pg_peop_ins =3D pg_query($query) or die("Can't execute
first =20
query");

$query =3D "INSERT INTO
\"tblContactInformation\"(\"contactItem=20
\",\"contactType\") VALUES ('$contact_info1','$contact_type')";
echo "Second Name query: " . $query . " />";
pg_contact_ins =3D pg_query($query) or die("Can't execute

2nd query");
$query =3D "INSERT INTO \"brdgPeopleContactInformation=20
\" (\"peopleId\",\"contactInformationId\",rank, type) VALUES =20
=09
(currval('\"tblPeople_peopleId_seq=20
\"'),currval('\"tblContactInformation_contactInformationId_s eq\"'), =20
'$cont_rank', '$cont_loc')";
echo "Third Name query: " . $query . "
";
$pg_peop_cont_ins =3D pg_query($query) or die("Can't
execute 3rd =20
query");
$query =3D "SELECT currval('\"tblPeople_peopleId_seq\"')";
$person_id =3D pg_query($query) or die("Can't execute 4th
query");
echo "person id is " . $person_id . "
"; +++++++
(This gives =20
me a Resource # - I know this is because I didn't process it as an =20
array.) ++++++++
commit;

The other thing that puzzles me is that I thought the statements =20
between the begin and the commit should be treated as one =20
transaction. In this case, some of the tables get written and not =20
others between the begin and the commit.

Thanks for your time.

Carol


--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php