php - 更新两个 MySQL 数据库

Question

我在几个 SO 线程上读到可以使用 INNER JOIN 更新查询更新两个数据库，但我无法让它工作，它只是抛出错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'category' in field list is ambiguous' in C:\xampp\htdocs\update.php:79 Stack trace: #0 C:\xampp\htdocs\update.php(79): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\update.php on line 79

第 79 行: $result = $stmt->execute($prepare);

if (isset($_POST['update'])) {
    $category = isset($_POST['category']) ? $_POST['category'] : null;
    $manufactuer = isset($_POST['manufactuer']) ? $_POST['manufactuer'] : null;
    $model = isset($_POST['model']) ? $_POST['model'] : null;
    $serial = isset($_POST['serial']) ? $_POST['serial'] : null;
    $itemcondition = isset($_POST['itemcondition']) ? $_POST['itemcondition'] : null;
    $locationb = isset($_POST['locationb']) ? $_POST['locationb'] : null;
    $locationr = isset($_POST['locationr']) ? $_POST['locationr'] : null;
    $comments = isset($_POST['comments']) ? $_POST['comments'] : null;
    $purchased = isset($_POST['purchased']) ? $_POST['purchased'] : null;
    $retired = isset($_POST['retired']) ? $_POST['retired'] : null;
    $stolen = isset($_POST['stolen']) ? $_POST['stolen'] : null;

    $sql_part = array();
    $prepare = array();
    if ($category){
        $sql_part[] = 'category = :category';
        $prepare[':category'] = $category;
    }
    if($manufactuer){
        $sql_part[] = 'manufactuer = :manufactuer';
        $prepare[':manufactuer'] = $manufactuer;
    }
    if($model){
        $sql_part[] = 'model = :model';
        $prepare[':model'] = $model;
    }
    if($serial){
        $sql_part[] = 'serial = :serial';
        $prepare[':serial'] = $serial;
    }
    if($itemcondition){
        $sql_part[] = 'itemcondition = :itemcondition';
        $prepare[':itemcondition'] = $itemcondition;
    }
    if($locationb){
        $sql_part[] = 'locationb = :locationb';
        $prepare[':locationb'] = $locationb;
    }
    if($locationr){
        $sql_part[] = 'locationr = :locationr';
        $prepare[':locationr'] = $locationr;
    }
    if($comments){
        $sql_part[] = 'comments = :comments';
        $prepare[':comments'] = $comments;
    }
    if($purchased){
        $sql_part[] = 'purchased = :purchased';
        $prepare[':purchased'] = $purchased;
    }
    if($retired){
        $sql_part[] = 'retired = :retired';
        $prepare[':retired'] = $retired;
    }
    if($stolen){
        $sql_part[] = 'stolen = :stolen';
        $prepare[':stolen'] = $stolen;
    }
    $prepare[':barcode'] = $barcode;

    if(count($sql_part)){
        $sql = 'UPDATE assets a INNER JOIN assets_history b ON (a.barcode = b.barcode) SET ';
        $sql .= implode(', ', $sql_part);
        $sql .= ' WHERE a.barcode = :barcode AND b.barcode = :barcode';

        $stmt = $conn->prepare($sql);

        if($stmt){
            $result = $stmt->execute($prepare);
            $count = $stmt->rowCount();
            header('Location: ./usearch.php');
            exit;
        }
    }
}

这是数据库结构，以备不时之需:

  `barcode` int(6) UNSIGNED ZEROFILL NOT NULL
  `category` text NOT NULL
  `manufactuer` text NOT NULL
  `model` varchar(255) NOT NULL
  `serial` varchar(255) NOT NULL
  `itemcondition` text NOT NULL
  `locationb` text NOT NULL
  `locationr` text NOT NULL,
  `comments` varchar(255) NOT NULL
  `purchased` varchar(30) NOT NULL
  `retired` varchar(30) NOT NULL
  `stolen` varchar(30) NOT NULL

有没有更好的方法，还是我遗漏了一些愚蠢的东西。

我也刚刚在 PHPMyAdmin 中看到了关联两个表的列的可能性，但尚未尝试过。这两个表是相同的，一个将只保留对记录所做的所有更新。

这些文章我运气不好，其中一篇是我代码的基础。

MySQL UPDATE syntax with multiple tables using WHERE clause

MySql update two tables at once

How to update two tables in one statement in SQL Server 2005?

MySQL, update multiple tables with one query

Answer 1

使用 table name 列 category 它出现在两个表中

if ($category){
    $sql_part[] = 'assets.category = :category';
    $prepare[':category'] = $category;
}