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.html 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 -----Original Message----- From: Carol Walter [mailto:walt...@indiana.edu] 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 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 . "<br />"; $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 . "<br />"; 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_seq\"'), '$cont_rank', '$cont_loc')"; echo "Third Name query: " . $query . "<br />"; $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 . "<br />"; +++++++ (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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
