Re: Checking data inserted during a transaction

Hi Lynna,

You won't be able to see it in PHPPgAdmin until after it's committed.

The new data is only available to the current transaction. Once that
transaction is committed it becomes 'live'.

With PHP, when a script finishes, it will commit any unfinished
transactions (as far as I know) so a single transaction across multiple
pages won't work.

If you have shell access, you can see the transaction issue this way:

fire up 2 shell logins then in window 1

psql -d db
create table a(a int);

begin;
insert into a(a) values(1);
select * from a;

jump to shell 2
psql -d db
select * from a;
(will be empty)

jump back to shell 1
commit;

jump to shell 2 again
you will see 1 row.

You could serialize up the data in sessions and then use that data
before adding it to the database.
See http://www.php.net/serialize and http://www.php.net/unserialize and
http://www.php.net/session

Hope that helps a bit :)

Chris.

-----Original Message-----
From: pgsql-php-owner(at)postgresql(dot)org
[mailto:pgsql-php-owner(at)postgresql(dot)org] On Behalf Of Lynna Landstreet
Sent: Thursday, May 13, 2004 8:46 AM
To: pgsql-php(at)postgresql(dot)org
Subject: [PHP] Checking data inserted during a transaction

Hello all,

I'm working on an administrative interface in PHP for the art gallery
database I created last year, and running into a few issues.

Because information has to be entered into several different tables for
each exhibition, I've broken down the process of adding new exhibitions
to the databases into several pages, each of which processes the form
data sent from the previous page and then guides users through the next
step (artists featured in the exhibition, etc.).

I'm using transactions to handle this, so that at the end of the process
the user can (hopefully) review the data they've entered and either
commit or rollback the changes. But I'm having a bit of trouble figuring
out whether the data I'm trying to insert during the transaction is
actually being inserted.

From what I've read about transactions thus far, it looks to me as
though the data inserted should be visible in the database after the
insert is done but before the commit or rollback happens, and then
disappear if rollback is chosen. But that doesn't seem to be working.

I've loaded the data from the first form into an associative array,
added the name of the user who created and last modified the record (at
this stage, the same user) and the date created and last modified
(again, the same date, since this is the form for adding a new record),
and printed the array to make sure all the data is correct. Then I've
used the following statement to try and load it into the exhibitions
table of the database:

$exh_query = pg_insert($db, 'exhibitions', $exh_info)
or die ("Unable to insert data.");

$db is the database connection, and $exh_info is the array, which I've
already tested and made sure is working. Everything appears to go
smoothly, it doesn't die, no error message is generated - but the new
row does not show up in phpPgAdmin when I check there.

Have I done something wrong? Or am I confused and does data entered
during a transaction not actually show up until the commit? If that's
the case, is there any way I can display all the data for the user to
review at the end of the process before committing the changes? I was
planning to pull it from the database for that, but if it's not *in* the
database, I can't do that.

And if I can't, is there perhaps some way to pass an array from one page
to the next for several pages, adding to it along the way? I know you
can do that with normal variables through either hidden form fields or
putting them in the URL, but I don't know about arrays... And at least
one of the fields
(exhibition_description) is potentially several paragraphs long.

Any advice would be appreciated...

Thanks,

Lynna

P.S. My web host is running PHP 4.3.4 and PostgreSQL 7.4 if that's a
factor.

--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org