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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:walterc@xxxxxxxxxxx] 
Sent: Tuesday, June 09, 2009 4:35 PM
To: php-db@xxxxxxxxxxxxx
Subject:  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



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux