Assigning the current value of a sequence to a variable- Postgres 8.3.6
in [Databases]
|
Prev: Assigning the current value of a sequence to a variable - Postgres 8.3.6
Next: Using a PostgreSQL refcursor.
From: danaketh on 18 May 2009 15:45 Hello, you're not fetching the query results. $query = "SELECT currval('\"tblPeople_peopleId_seq\"')"; $result = pg_query($query) or die("Can't execute 4th query"); if ($result != false) { $row = pg_fetch_row($result); echo "person id is " . $row[0] . "<br />"; } else { // no curval() found or something bad happened... } This should work for you. If you run SELECT query, you have to fetch the results before you can access the data. If you expect more than one row, you can use while($row = pg_fetch_row($result)) { // code to process data from database } Hope this will help you :) Carol Walter napsal(a): > Hello, > > I have a program that looks for a name in a database. If the name is > found the id of the record is assigned to a variable. Later in the > program that value is used to insert records associated with the name > into the database using a variable called person_id. If the name is > not found, then a new record is build from data entered into a form. > There is a name record and a contact information record and a bridge > table record to create the relationship between them. After that, the > program may create additional records related to the new person record > in the same way it creates records related to the person records that > might already exist in the database. It would make this process lots > easier if I can assign currval of the new person record to the same > variable that I use when I already have a record in the database. Can > I assign currval to a variable? > > Below is the code that I'm using. The value that displays for person > id which I'm trying to capture at the bottom of this code is "person > id is Resource id #6". > > if (($submit_db_name == "Submit") && ($submit_new_name == "Submit")) > { > echo"<p> Contact Locator: $cont_loc</p>"; > echo"<p> Contact Type Rank: $cont_rank</p>"; > echo"<p> Contact Info Type: $contact_type</p>"; > echo"<p> New name string: $f_name_new</p>"; > echo"<p> New name string: $m_name_new</p>"; > echo"<p> New name string: $l_name_new</p>"; > echo"<p> New ivl web string: $ivl_web_peop</p>"; > echo"<p> New cns_web string: $cns_web_peop</p>"; > echo"<p> New contact rank string: > $cont_rank</p>"; > echo"<p> New contact locator string: > $cont_loc</p>"; > echo"<p> New contact item string: > $contact_info1</p>"; > echo"<p> New contact type string: > $contact_type</p>"; > 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 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 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 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 />"; > commit; > > This is PostgreSQL 8.3.6, PHP 5, on Solaris 10. > > Thanks for your time. > > Carol > > -- S pozdravem Daniel Tlach Freelance webdeveloper Email: mail(a)danaketh.com ICQ: 160914875 MSN: danaketh(a)hotmail.com Jabber: danaketh(a)jabbim.cz |