Mysql transaction handle

I have an user registration procdedure, with this steps:

If user send the registraion form then:

 1. step: run a QUERY
 2. step: run another QUERY
 3. make a directory1 on the server
 4. make a sub directory2 into the directory1
 5. make another sub directory3 into the directory1
 6. upload an image into the sub directory2

So this is work well. But im afraid if something happen while this procedure is runnin, so if the procedure interrupted on 4. step, then i have some unnecessary row in my table bacause of step 1.; 2.

So i think i have to use mysql transaction handle, but i don't know how. I think somethink like this:

     START TRANSACTION (what is the exact format?)
     1. st开发者_StackOverflow社区ep: run a QUERY
     2. step: run another QUERY
     3. make a directory1 on the server
     4. make a sub directory2 into the directory1
     5. make another sub directory3 into the directory1
     6. upload an image into the sub directory2
     COMMIT (if all step ok. but how can i check this?)
     ROLLBACK (if something wrong; but how can i check this?)

 I this transaction handle not handle the FILE transactions, so i ROLLBACK function is called, then i have to delete manually the directorys if created already?!

Sorry, two more question:

• If the procedure interrupted, for example in step 2, and ROLLBACK is called, then the sciprt will stop or start from the end? Or i need to write a callback funciton something like this: (is this how i have to do?)

  function begin() { @mysql_query("BEGIN");}
  function commit(){ @mysql_query("COMMIT");}
  function rollback()
  {
      @mysql_query("ROLLBACK");
      echo "oops";
      exit();
  }
  $inster_query="insert into....
  begin();
  $result_insert = @mysql_query($inster_query);
  if(!$result_insert)
  {
    rollback(); 
  }
  $update_query="update....
  $result_up = @mysql_query($update_query);
  if(!$result_up)
  {
    rollback(); 
  }
  .
  .
  commit(); 
  

• how can i test this if its work or not? Thank you.

START TRANSACTION - see manual

1. step: run a QUERY. If it fails - rollback.
2. step: run another QUERY. If it fails - rollback.
3. make a directory1 on the server. If it fails - rollback.
4. make a sub directory2 into the directory1. If it fails, delete directory1 and rollback.
5. make another sub directory3 into the directory1. If it fails, delete subdirectory2 and directory 1 and rollback.
6. upload an image into the sub directory2. If it fails, delete three previous directories and rollback. COMMIT

From development perspective, you need to create Undo stack and push there objects, that are capable of undoing the work. In case something fails, you need to ->Execute() the entire undo stack. I guess you know how to check if directory creation/file upload failed?

the steps that you want to do are:

1. step: run a QUERY
2. step: run another QUERY
3. make a directory1 on the server
4. make a sub directory2 into the directory1
5. make another sub directory3 into the directory1
6. upload an image into the sub directory2

Here first I want to discuss about the queries... means how you can send multiple queries and after everything goes right save them...
suppose your database name is 'database', table names are 'table_1', 'table_2','table_3'... and you want to do something like below:

step 1: insert a row in table_1
step 2: update a column in table_2 where id is 1
step 3: insert another row in table_3

but you need to complete all these three steps or none of them... In this case your can use this php code...

// first connect and select to your mysql database...
// before sending any queries (INSERT, UPDATE etc.) we have send this command else it will not work...
mysql_query("START TRANSACTION;");
/* this command will say to the mysql that it only have to calculate the queries that we are
sending is right or have problems... if everything is right, it will remember the query and
after sending the confirmation it will trigger the queries... besides if the query has any
problem then it will return FALSE only... moreover for right query mysql return TRUE...*/
// now I will send the queries and save the return values to different variables...
$query1 = "INSERT INTO `database`.`table_1` (col1, col2) VALUES ('value1', 'value2');";
$query1 = mysql_query($query1);
$query2 = "UPDATE `database`.`table_2` SET col1='value1', col1='value1' WHERE id='1';";
$query2 = mysql_query($query2);
$query3 = "INSERT INTO `database`.`table_3` (col1, col2) VALUES ('value1', 'value2');";
$query3 = mysql_query($query3);
// now if anything is wrong, I will get FALSE as return value for each wrong query...
// if all of them are TRUE, we will send the confirmation for save them...
// else we will say to forget whatever we send before...
if($query1 && $query2 && $query3){
    // send confirmation
    mysql_query('COMMIT;');
    /* NOTE: after you send COMMIT you can't ROLLBACK any query or data...*/
} else {
    // order to forget whatever we send before
    mysql_query('ROLLBACK');
    /* NOTE: after you send ROLLBACK you will lose all the queries that you send
    after you send START TRANSACTION to mysql...*/
}

NOTE: always keep in your mind that you can't ROLLBACK all mysql_query... here is the list that you can't ROLLBACK...

CREATE DATABASE
ALTER DATABASE
DROP DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
CREATE INDEX
DROP INDEX
CREATE EVENT
DROP EVENT
CREATE FUNCTION
DROP FUNCTION
CREATE PROCEDURE
DROP PROCEDURE

I think you get your answer that you want to do... However here is the php code for your project...

// connect and select your database...
mysql_query("START TRANSACTION;");
/* step 1 */
$query1 = "your query";
$query1 = mysql_query($query1);
/* step 2 */
/* you can also use multiple line to build your query  */
$query2 = "your query part 1";
$query2 .= "your query part 2";
$query2 .= "your query part 3";
$query2 = mysql_query($query2);
/* step 3 */
mkdir(' the path you want to create ', 0700);
/* here '0700' is the mode (permission or chmod)...
if you don't know about it or want to know about it check the link...
www.php.net/manual/en/function.chmod.php */
/* step 4 */
mkdir('<the path you write in step 3>/<the folder name that you want to create in step 4>', 0700);
/* step 5 */
mkdir('<the path you write in step 3>/<the folder name that you want to create in step 5>', 0700);
/* step 6 */
/* I am really sorry to say that I have on knowledge about
the code that need to write for uploading a image or any file...
so I am unable to help you for this step */
/* now we need to check is everything is right or not...
we will create a variable '$rollback' and
set its value as FALSE... if we get any problem we will set it to TRUE*/
$rollback = FALSE;
// first we will check the directory !important
/* use array and foreach to do that... it will make it easy and
if you got more directory later, you just have to add it to the array... nothing more */
$dir = array();
$dir[] = 'your directory 1';
$dir[] = 'your directory 2';
$dir[] = 'your directory 3';
foreach ($dir as & $single_dir) {
    if(file_exists($single_dir) == FALSE){
        $rollback = TRUE;
    }
}
// now if the $rollback is false, we unable to create any directory...
if($rollback==FALSE){
    foreach ($dir as & $single_dir){
        if(file_exists($single_dir)){
            rmdir($single_dir)
            /* friend though I use rmdir to remove the directory but
            it has many limitation... watch this link...
            www.php.net/manual/en/function.rmdir.php */
        }
    }
}
// lets check the image that we upload
if(file_exists('image directory') == FALSE){
    $rollback = TRUE;
} else {
    if($rollback == TRUE){
        unlink('image directory'); /* don't use rmdir for deleting file */
    }
}

// now time to check the queries...
if($query1 && $query2 $$ $rollback){
    mysql_query('COMMIT;');
} else {
    mysql_query('ROLLBACK;');
}

Friends, I hope my answer will be very helpful for you and for them who have the same problem... If you find any mistake in my answer, please be kind enough to forgive me for that and if you can please correct it for me... Moreover if you think my answer is helpful for others please don't forget to vote this answer up... thanks you... friends...