Thread: 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
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
> [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
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"
Hmmm, so my entire idea for how to do this appears to have been unsound. Well, better to find that out sooner than later! Thanks to everyone who replied. But I have a few questions: on 5/12/04 7:36 PM, Chris at chris@interspire.com wrote: > 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 OK, so if I understand this correctly, the serialize function can take something like a big associative array containing all the data I want to be able to write into the database and condense it into a single object that can be passed via a hidden form field or something to that effect, correct? Or the data can be put into the session array using $_SESSION, and then passed by using a $session_id in the page URL? But according to one of the user comments in the manual I have to use addslashes() and stripslashes() if I want to be able to put the data into the database after unserializing it - is that right? Also, what about character encoding? My database and my web pages are utf-8 because of the presence of special characters in some of the artists and names and image and exhibition titles (it's a Canadian gallery, so there are a lot of French artists). Will serializing the array containing the data affect that in any way? Sorry if I'm being a pest - the whole session thing is new to me and I want to make sure I understand it correctly. Thanks, Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Hi Lynna, on 5/12/04 7:36 PM, Chris at chris@interspire.com wrote: > 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 > OK, so if I understand this correctly, the serialize function can take something like a big associative array containing all the data I want to be able to write into the database and condense it into a single object that can be passed via a hidden form field or something to that effect, correct? Or the data can be put into the session array using $_SESSION, and then passed by using a $session_id in the page URL? You won't need to use unserialize and serialize per page if you're going to use sessions - you only need those if you're going to pass the values in a URL or in a hidden form field. For sessions, you can simply $my_array = array('1','2','3','4','5'); $_SESSION['blah'] = $my_array; sort of thing.. > But according to one of the user comments in the manual I have to use > addslashes() and stripslashes() if I want to be able to put the data into the database after unserializing it - is that right? Yep. So you'll need to do $value = addslashes(serialize($real_value)); and use $value in your query... Then when you fetch out $real_value = unserialize(stripslashes($value)); Done =) > Also, what about character encoding? My database and my web pages are utf-8 because of the presence of special characters in some of the artists and names and image and exhibition titles (it's a Canadian gallery, so there are a lot of French artists). Will serializing the array containing the data affect that in any way? Not as far as I know, it should handle that fine. > Sorry if I'm being a pest - the whole session thing is new to me and I want to make sure I understand it correctly. Ask as many questions as you need to, we've all been at the start before =)
on 5/19/04 10:46 PM, Chris at chris@interspire.com wrote:
> You won't need to use unserialize and serialize per page if you're going
> to use sessions - you only need those if you're going to pass the values
> in a URL or in a hidden form field.
>
> For sessions, you can simply
> $my_array = array('1','2','3','4','5');
> $_SESSION['blah'] = $my_array;
>
> sort of thing..
OK. So if I'm just saving things into the $_SESSION array, I don't need to
use serialize?
I've been reading through a whole bunch of session tutorials on DevShed,
PHPBuilder, etc. (although rather annoyingly nearly all of them are based on
PHP 4.0 and a lot of things seem to have changed with 4.2), and I think I'm
*starting* to get a grip on this, although some aspects are still a bit
confusing.
Most of the tutorials say that you can either pass session ids through the
URL or through cookies, and that using cookies is better for security
reasons. I'm OK with cookies because only a limited number of people will be
using the admin system I'm setting up and I can tell them they have to have
cookies enabled. It's not a part of the site that will be accessible to the
general public. And from the sounds of it the cookie is set automatically
without my having to do it with setcookie - right?
But the thing that's confusing me now is that apparently in 4.2,
--enable-trans-id is on by default, and that makes it automatically put the
session ID in the URLs of relative links, unless I've misunderstood what it
does? If passing the SID via URL is supposed to be bad from a security
standpoint, is there some way I can make it not do this? Bearing in mind
that I'm on a shared host so I can't mess with their overall PHP
configuration? Or am I worrying about this too much?
>> But according to one of the user comments in the manual I have to use
>> addslashes() and stripslashes() if I want to be able to put the data
> into the database after unserializing it - is that right?
>
> Yep. So you'll need to do
> $value = addslashes(serialize($real_value));
>
> and use $value in your query...
>
> Then when you fetch out
>
> $real_value = unserialize(stripslashes($value));
>
> Done =)
And that's only if I'm putting the session itself into a form field or
database, right?
If I extract the values from the $_SESSION array at the end of the update
process and insert them into the database then, having just used cookies to
store it in the meantime, then it doesn't need this? Or does it?
> Ask as many questions as you need to, we've all been at the start before
> =)
Thanks. Hopefully sooner or later I'll actually be at the point where I can
start answering other people's questions instead. :-)
Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
Oh, and one more question I forgot. If session_start and/or adding variables to $_SESSION does result in setting a cookie automatically, does that mean it has to be done at the beginning of the script, before any output, like with setcookie? Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Hey Lynna, > Oh, and one more question I forgot. If session_start and/or adding variables to $_SESSION does result in setting a cookie automatically, does that mean it has to be done at the beginning of the script, before any output, like with setcookie? Yes - you need to do a session_start() before any output gets sent. See http://www.php.net/session_start for a bit more info about it.. >> You won't need to use unserialize and serialize per page if you're >> going to use sessions - you only need those if you're going to pass >> the values in a URL or in a hidden form field. >> >> For sessions, you can simply >> $my_array = array('1','2','3','4','5'); >> $_SESSION['blah'] = $my_array; >> >> sort of thing.. >OK. So if I'm just saving things into the $_SESSION array, I don't need to use serialize? Correct. >Most of the tutorials say that you can either pass session ids through the URL or through cookies, and that using cookies is better for security reasons. I'm OK with cookies because only a limited number of people will be using the admin system I'm setting up and I can tell them they have to have cookies enabled. It's not a part of the site that will be accessible to the general public. And from the sounds of it the cookie is set automatically without my having to do it with setcookie - right? Yep. Everything gets stored on the server in the session_save_path directory (you can set this yourself in your scripts - see http://www.php.net/session_save_path :) >But the thing that's confusing me now is that apparently in 4.2, --enable-trans-id is on by default, and that makes it automatically put the session ID in the URLs of relative links, unless I've misunderstood what it does? If passing the SID via URL is supposed to be bad from a security standpoint, is there some way I can make it not do this? Bearing in mind that I'm on a shared host so I can't mess with their overall PHP configuration? Or am I worrying about this too much? If it's a worry you can turn it off - http://www.php.net/ini_set - look for session.use_trans_sid . Since it's a PHP_INI_SYSTEM|PHP_PER_DIR you can change it in a .htaccess file ... >>> But according to one of the user comments in the manual I have to use >>> addslashes() and stripslashes() if I want to be able to put the data >> into the database after unserializing it - is that right? >> Yep. So you'll need to do >> $value = addslashes(serialize($real_value)); >> and use $value in your query... >> Then when you fetch out >> $real_value = unserialize(stripslashes($value)); >And that's only if I'm putting the session itself into a form field or database, right? Yep. >If I extract the values from the $_SESSION array at the end of the update process and insert them into the database then, having just used cookies to store it in the meantime, then it doesn't need this? Or does it? It depends :) eg dob could be stored as an array - $dob = array('dd' => '01', 'mm' => '01', 'yy' => '1970'); then you'll need to serialize it before saving it.. If you want the values saved separately, then no you won't (eg q1 = 'y', q2 = 'blah' etc etc). HTH. Chris.