Thread: 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

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" <rhunter@venticon.com>
To: <pgsql-php@postgresql.org>
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)
>

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.