Home>

I am trying to register data in MySQL using PDO with PHP and then upload the image file.
Transaction has started in advance.
In the second half of the source, if there is a problem with the image file, throw new Exception () ;, catch the error with catch (Exception $e), and roll back with $pdo->rollBack () ;.

Problem

Data will be registered without being rolled back.
I don't understand the cause.
In the first place, there may be a fundamental error in the source.
If i point out, it will be very helpful.

Applicable source code
<? php
ini_set ('display_errors', 1);
/ **
 * Library loading
 * /
include_once '../commons/ConnectToDatabase.php';
/ **
 * Get session information
 * /
$user_id = 'test_user';
/ **
 * Get tags
 * /
$tags = explode (',', htmlspecialchars ($_ POST ['tags'], ENT_QUOTES, "UTF-8"));
 / **
  * Get title
  * /
 $title = htmlspecialchars ($_ POST ['title'], ENT_QUOTES, "UTF-8");
 $pdo = connectToDatabase ();
 try {
  $pdo->beginTransaction ();
    // 1. Register basic image information (user ID and title) in picurres and get the ID
  $sql = 'INSERT INTO pictures (user_id, title) VALUES (: user_id,: title)';
  $stmt = $pdo->prepare ($sql);
  $stmt->bindValue (': user_id', $user_id, PDO :: PARAM_STR);
  $stmt->bindValue (': title', $title, PDO :: PARAM_STR);
  $executed1 = $stmt->execute ();
  $picture_id = $pdo->lastInsertId ('id');
  $pdo->commit ();
  foreach ($tags as $tag) {
    // 2. Register tags in tags. At that time, ID is acquired.
    $sql = 'INSERT INTO tags (name) VALUES (: tag)';
    $stmt = $pdo->prepare ($sql);
    $stmt->bindValue (': tag', $tag, PDO :: PARAM_STR);
    $executed2 = $stmt->execute ();
    $tag_id = $pdo->lastInsertId ('id');
    // Register to tag_map using ID of 3.1 and 2
    $sql = 'INSERT INTO tag_maps (user_id, picture_id, tag_id) VALUES (: user_id,: picture_id,: tag_id)';
    $stmt = $pdo->prepare ($sql);
    $stmt->bindValue (': user_id', $user_id, PDO :: PARAM_STR);
    $stmt->bindValue (': picture_id', $picture_id, PDO :: PARAM_STR);
    $stmt->bindValue (': tag_id', $tag_id, PDO :: PARAM_STR);
    $executed3 = $stmt->execute ();
    // Upload image if information registration is successful
    if ($executed1&&$executed2&&$executed3) {
      // specify the upload destination directory
      $dir = '../data/';
      // array to store errors
      $errors = array ();
      // check extension
      $upfile = $_FILES ["userfile"] ["name"];
      $ext = substr ($upfile, -3);
      if ($ext! = "jpg"&&$ext! = "jpeg"&&$ext! = "png") {
        $errors ['img_extension'] = "Files with extension". $ext. "are not available. \ r \ nOnly files with extension .jpg .jpeg .png are available.";
      }
      // Check file size
      if ($_FILES ["userfile"] ["error"] == 1 || $_FILES ["userfile"] ["error"] == 2) {
        $errors ['img_size'] = "Image size must be 4MB or less";
      }
      // Check if an image is specified
      if ($_FILES ["userfile"] ["error"] == 4) {
       $errors ['img_notset'] = "No image specified";
     }
     if (count ($errors) === 0) {
       $tmp_file = $_FILES ['userfile'] ['tmp_name'];
       $result = move_uploaded_file ($tmp_file, $dir. $picture_id. ".". $ext);
     } else {
      throw new Exception ();
    }
  }

}
} catch (Exception $e) {
  $pdo->rollBack ();
}
?>

Please describe your approach to the issue

Supplemental information (language/FW/tool version etc.)

PHP version 7.1
CentOS 7.2

  • Answer # 1

    Because you committed before checking for errors.

  • Answer # 2

      

    // 1. Register basic image information (user ID and title) in picurres and get the ID

    There is a$pdo->commit ();line in the

    block. Now that you are committing the transaction, at this point the transaction ends and there is no rollback.
    DB rollback "rewinds what happened in the transaction", so if the transaction ends with a commit, nothing happens when you callrollBack. Since PDO aftercommitreturns to the auto-commit state (commits as soon as processing such as INSERT is executed), all the processes in foreach are committed without being rolled back.
    A commit is "allwhen the update process is successful" and a rollback is "what to do instead of commit when something fails".