RE: [PHP-DB] Transactions ....[was]: MySQL, Multiple tables............
| From: | Stuart Felenstein | Date: | Thu, 14 Oct 2004 10:43:39 +0000 |
| Subject: | RE: [PHP-DB] Transactions ....[was]: MySQL, Multiple tables............ | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-36794@lists.php.net to get a copy of this message | ||
How did you know I was using INNOdb tables ? :)
So here is one question, that I can't seem to find a
good answer on.
So the first table, where the first insert must go
looks like this:
[MainTable]
+--------------------------+----------+--------+
| Record_ID (auto inc int) | Member ID| .......|
+--------------------------+----------+--------+
Here in this table (above) the Record_ID is
auto inc, but then, before I write to the rest
of the tables I need to get that Record_ID. Each
"child" table has a int column PK that holds
the Record_ID.
I'm assuming from my understanding of Transctions,
that I would need to do a COMMITT prior to doing a:
select Record_ID from MainTalble where .....
and then start transaction with the remaining inserts.
That make sense or am I wrong? Nothing is written
till COMMITT.
Prior to issuing sql commands I would check if data /
user input exists. If it didn't then fail before
start. But my main concern is if after this first
committ and errors occur, is rollback to late on the
MainTable ?
Stuart
--- Bastien Koert <bastien_k@hotmail.com> wrote:
> If you are usiing the INNOdb table type/engine in
> the mysql db, it supports
> transactions. you can wrap the entire set of sql
> statement in a transaction
> and they would get executed as one block. If any
> fail, then the whole thing
> is rolled back. This DOES NOT work with the myISAM
> table types.
>
> $sql = "begin ";
> $sql .="insert into table 1...";
> $sql .="insert into table 2...";
> $sql .="insert into table N...";
> $sql .="end";
>
> $result = mysql_query($sql);
>
> hth
>
>
> >From: Stuart Felenstein <stuart4m@yahoo.com>
> >To: php-db@lists.php.net
> >Subject: [PHP-DB] MySQL, Multiple tables, and
> Multipage form using sessions
> >Date: Wed, 13 Oct 2004 13:41:46 -0700 (PDT)
> >
> >Can anyone, someone please point me in the right
> >direction.
> >I have a multi page form, so at the last page I
> want
> >to insert all data into
database................................