serial type question

Поиск
Список
Период
Сортировка
Искать

serial type question

От:
Ray Hunter <rhunter@venticon.com>
Дата:
I have a php script that will be inserting from a form into two tables.

Table1 has a serial data type.
Table2 references Table1's serial data type (PK).

Here are my questions:
1. Can i run a transaction process from php? If yes, how?
2. How can i get the serial value for inserting into the 2nd table?


-- 
Thank you,

Ray Hunter


Re: serial type question

От:
"Phil Geer" <philg@gearcc.com>
Дата:
For Question #2 this is how I handle it.

$query = pg_Exec( $dbconnect,
                               "select nextval(table_name_seq);");

Store that value in a variable then you can insert it into your table1 with
the serial (PK) and into the table2 as a reference.

$query = pg_Exec( $dbconnect,
                                "insert into table1 (serial, col1, col2)
                                                values
(variable_set_by_nextval, data1, data2);
                                insert into table2 (table2_PK,  table2_col1,
table2_col_that_references_table1)
                                                values(table2_PK_data ,
table2_col1_data, variable_set_by_nextval);");



You may also want to take a look at this section of the postgresql manual
it'll be of help.

http://www.postgresql.org/idocs/index.php?functions-sequence.html

As for Question #1 it should work just fine if you do a begin and commit


$query = pg_Exec( $dbconnect,
            "BEGIN WORK;
                insert into table1 (serial, col1, col2)
                                values (variable_set_by_nextval, data1,
data2);
                insert into table2 (table2_PK,  table2_col1,
table2_col_that_references_table1)
                                values(table2_PK_data , table2_col1_data,
variable_set_by_nextval);
             COMMIT WORK;");

Good Luck
Phil

----- Original Message -----
From: "Ray Hunter" 
To: 
Sent: Wednesday, November 13, 2002 2:56 PM
Subject: [PHP] serial type question


> I have a php script that will be inserting from a form into two tables.
>
> Table1 has a serial data type.
> Table2 references Table1's serial data type (PK).
>
> Here are my questions:
> 1. Can i run a transaction process from php? If yes, how?
> 2. How can i get the serial value for inserting into the 2nd table?
>
>
> --
> Thank you,
>
> Ray Hunter
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: serial type question

От:
"scott.marlowe" <scott.marlowe@ihs.com>
Дата:
On 13 Nov 2002, Ray Hunter wrote:

> I have a php script that will be inserting from a form into two tables.
> 
> Table1 has a serial data type.
> Table2 references Table1's serial data type (PK).
> 
> Here are my questions:
> 1. Can i run a transaction process from php? If yes, how?

Yes.  And no.

Yes, you can run a transaction.  Only requirement is that it cannot span 
multiple PHP pages.  I.e. you can't start a transaction on one page and 
finish it on another.  pg_connect drops the connection, aborting the 
transaction, and pg_pconnect cannot GUARANTEE that you will get the same 
connection the next time you run a page, as a different child process may 
answer your request than the one you had on the first page.  Making 
matters worse, on a low load test box, multiple page transactions will 
probably work fine, but on a heavily loaded box you may get the problem 
that your child process doesn't stay the same from one page to the next.

If you run the transaction on a single PHP page it should work fine.  All 
you need to do is:

pg_exec($connection,"begin");
LOTSA PHP CODE TO DO THE WORK GOES HERE
pg_exec($connection,"commit");

You might wanna add some error checking to the above...

> 2. How can i get the serial value for inserting into the 2nd table?

You can use currval('seqname') AFTER the insert to the parent table to 
find out what the inserted value was.  currval is transaction aware, and 
will not give you the REAL current value if some other transaction just 
bumped it up right after you did.  

Use it something like this:

begin;
insert into parent (field1, field2) values (value1,value2);
select currval('sequsedbyparent');
insert into child (fieldx, fieldy, fk2parent) values 
(valuex,valuey,'valfromcurrval');
repeat above for each child table
commit;

That should do it.  Again, add some error checking to this example for 
production use.

FAQ