PHP/MySQL - 如果查询失败如何删除插入？

Question

我有这个代码

<?php
class Objekt{
// database connection and table name
private $conn;
private $table_name = "objects";
// object properties
public $id;
public $id_group;
public $title;
public $description;
public $lat;
public $lng;
public $icon;
public $tagsraw;
// constructor with $db as database connection
public function __construct($db){
    $this->conn = $db;
}

function create(){
    // query to insert record
    $query = "INSERT INTO
                " . $this->table_name . "
            SET
                id_group=:id_group, title=:title, description=:description, lat=:lat, lng=:lng, icon=:icon;
            SELECT max(id) AS id FROM objects;";
    // prepare query
    $stmt = $this->conn->prepare($query);
    // sanitize
    $this->id_group=htmlspecialchars(strip_tags($this->id_group));
    $this->title=htmlspecialchars(strip_tags($this->title));
    $this->description=htmlspecialchars(strip_tags($this->description));
    $this->lat=htmlspecialchars(strip_tags($this->lat));
    $this->lng=htmlspecialchars(strip_tags($this->lng));
    $this->icon=htmlspecialchars(strip_tags($this->icon));     
    // bind values
    $stmt->bindParam(":id_group", $this->id_group);
    $stmt->bindParam(":title", $this->title);
    $stmt->bindParam(":description", $this->description);
    $stmt->bindParam(":lat", $this->lat);
    $stmt->bindParam(":lng", $this->lng);
    $stmt->bindParam(":icon", $this->icon);

    // execute query
    if($stmt->execute()){
               
        //$stmt->execute();
    
/* 
      ERROR below here
      Uncaught PDOException: SQLSTATE[HY000]: General error
      stack trace:
      #0 PDOStatement -&gt;fetch(2)
      #1 Objekt-&gt;create()
*/

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $this->id = $result['id'];
        echo "$this->id";
                    
        //get tags matches
        $tags = [];
        $this->tagsraw = preg_replace('/\s+/', '', $this->tagsraw);
        $tags = explode(',', $this->tagsraw);
        
        $id_tags = [];
        for ($i = 0; $i < sizeof($tags); $i++) {
            $query = "SELECT id FROM category WHERE title=" . tags[i];
            
            $stmt = $this->conn->prepare($query);
            
            $stmt->execute();
            
            $result = $stmt->fetch(PDO::FETCH_OBJ);
            $id_tags[i] = $result->id;
        }
        
        for ($i = 0; $i < sizeof($id_tags); $i++) {            
            $query = "INSERT INTO category_object SET id_object=" . $this->id . ", id_category=" . $id_tags[i];
            
            $stmt = $this->conn->prepare($query);
            
            $stmt->execute();
            
        }
        
        return true;
    }
    return false;
}

我想做的事:

Insert Object and create a relation to a category_object table over foreign keys id_object / id_category

我想改进的地方:

What do I have to do to delete an insert if it fails (where should I put a try/catch or something else)

What is wrong with my fetch? I Select the value from max(id) to get the value of my inserted object, why is it throwing an Exception

我有另一个警告，为什么它告诉我这个

for ($i = 0; $i < sizeof($id_tags); $i++) {            
        $query = "INSERT INTO category_object SET id_object=" . $this->id . ", id_category=" . $id_tags[i];

        $stmt = $this->conn->prepare($query);

        $stmt->execute();

    }

将在未来的 php 版本中弃用..

Answer 1

使用transactions .

在调用 commit() 之前，事务中完成的任何插入/更新/删除操作都不会实际对数据库执行，并且您始终可以回滚更改，在失败时放弃它们.

在您的代码中，您应该可以将 if($stmt->execute()){ 替换为

try {
    $this->conn->beginTransaction();
    $stmt->execute();

    // ...everything else from inside the if statement here...

    $this->conn->commit();
    return true;
} catch (\Exception $e) {
    $this->conn->rollback();
    return false;
}