pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Checking data inserted during a transaction
4+ messages / 4 participants
[nested] [flat]

* Checking data inserted during a transaction
@ 2004-05-12 22:45  Lynna Landstreet <lynna@gallery44.org>
  0 siblings, 3 replies; 4+ messages in thread

From: Lynna Landstreet @ 2004-05-12 22:45 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Checking data inserted during a transaction
@ 2004-05-12 23:36  Chris <chris@interspire.com>
  parent: Lynna Landstreet <lynna@gallery44.org>
  2 siblings, 0 replies; 4+ messages in thread

From: Chris @ 2004-05-12 23:36 UTC (permalink / raw)
  To: 'Lynna Landstreet' <lynna@gallery44.org>; pgsql-php

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@postgresql.org
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of Lynna Landstreet
Sent: Thursday, May 13, 2004 8:46 AM
To: pgsql-php@postgresql.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@postgresql.org




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Checking data inserted during a transaction
@ 2004-05-13 05:17  Joolz <joolz@xs4all.nl>
  parent: Lynna Landstreet <lynna@gallery44.org>
  2 siblings, 0 replies; 4+ messages in thread

From: Joolz @ 2004-05-13 05:17 UTC (permalink / raw)
  To: pgsql-php

> [Lynna Landstreet schreef op 12-05-2004 18:45 -0400]

...

> 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

...

Have you checked if the visibility is only for the current db session?

-- 
07:16-07:17
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Checking data inserted during a transaction
@ 2004-05-13 20:31  Keary Suska <hierophant@pcisys.net>
  parent: Lynna Landstreet <lynna@gallery44.org>
  2 siblings, 0 replies; 4+ messages in thread

From: Keary Suska @ 2004-05-13 20:31 UTC (permalink / raw)
  To: pgsql-php

on 5/12/04 4:45 PM, lynna@gallery44.org purportedly said:

> 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.

As was mentioned, only the connection/process that "owns" the transaction
can see information altered within the transaction. In short, the answer to
your question is, "you can't."

You have a bigger issue, however. Because of the stateless nature of HTTP
and how PHP/Apache implements database connections, your multi-page process
will likely fail, as there is no guarantee that on any subsequent HTTP
requests that you will be using an existing connection/process (see also
first paragraph).

To use a multi-stage process, you will need to preserve the data yourself
(i.e. not insert into DB) at each step until the process is confirmed by the
end user. If the data is not too large you can preserve it within session
variables. Otherwise you may want to create a "safe" temp file, a reference
to which is maintained by the session.

Best regards,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-05-13 20:31 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-12 22:45 Checking data inserted during a transaction Lynna Landstreet <lynna@gallery44.org>
2004-05-12 23:36 ` Chris <chris@interspire.com>
2004-05-13 05:17 ` Joolz <joolz@xs4all.nl>
2004-05-13 20:31 ` Keary Suska <hierophant@pcisys.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox