Transaction in MySQL with PHP

amu_roja

I created three tables in mysql which are inter linked
the tables are

User(User_ID)

Login_Info(User_ID)

Status(User_ID)

here I gave only the primary key field.

when I insert a user's info, I need to do a atomic transaction in the database

i.e, If a user is added, the values of the user is inserted into the three tables, otherwise no values should be inserted.

Please anyone know this give me the things.

By
Amudhan

mrhappiness

Why do you need transactions to do this?

INSERT INTO usertable
  if successful
    INSERT INTO logintable
      if successful
        INSERT INTO statustable
      else
        DELETE FROM logintable
        DELETE FROM usertable
  else
    DELETE FROM usertable

What did I get wrong?

If you worry about the user-id have a look at [man]mysql_insert_id[/man]

amu_roja

I agree the point,
anyway it is not a safe method, because sometimes any of other record may be destroyed by this query.

when we use transaction we set a save point and if any error occurs simply rollback to the position of the save point. It is a safe and actual method.

Please give me the details, whether mysql have this facility or not

regards
Amudhan

dopey

the innodb storage engine supports this. see http://dev.mysql.com/doc/mysql/en/commit.html

amu_roja

If i am using innodb type table, whether it is robust?
why i am asking is some one says that myisam is better than innodb regarding datacrashes.

Please help me regarding this.

by
amudhan

amu_roja

Nobody knows the answer? what a ....?

mrhappiness

i can't tell you anything about whether myisam or innodb is beeter regarding datacrashes (not that db expert) but as of mysql 4.0.4 you can update multiple tables using one statement: http://dev.mysql.com/doc/mysql/en/update.html

so perhaps you can store a unique id in all of the tables and if everything succeeds you update them and set all three entries to valid?

btw: what does mysql.com say about datacrashes and myisam/innodb?