php - mysqli 执行生成错误查询

Question

这是一个简单且有效的 UPDATE 查询(嗯，带有 INNER JOIN)，并且 php 代码中没有错误。如果我在 php 之外执行查询，则该查询有效:

UPDATE address_book ab
INNER JOIN address_book_client abc ON abc.contact_id = ab.id
SET ab.name = 'name1', ab.surname = 'surname', ab.cc = '34', ab.phone = '123456789', ab.email = 'a@a.aa', ab.nif = '12345678A', ab.note = 'Blah blah blah...'
WHERE abc.contact_id = 1 AND abc.company_id = 1

执行更新的代码:

private $name;

// Populate AddressBook Object From User Input
public function PopulateFromUserInput($address_book) {

    $this->name         = $address_book['name'];

}

// New / Modify / Delete Contact
public function contact($option = '') {
    $mysqli = $this->aet->getAetSql();
    $exit   = FALSE;

    } else if ($option == 'modify') {

        if ($stmt = $mysqli->prepare('UPDATE address_book ab
                                      INNER JOIN address_book_client abc ON abc.contact_id = ab.id
                                      SET ab.name = ?, ab.surname = ?, ab.cc = ?, ab.phone = ?, ab.email = ?, ab.nif = ?, ab.note = ?
                                      WHERE abc.contact_id = ? AND abc.company_id = ?')) {

            $stmt->bind_param('ssiisssii', $this->name, $this->surname, $this->cc, $this->phone, $this->email, $this->nif, $this->note,
                                           $this->id, $this->uploader);

            $exit = 'User modified an existing contact.';

        } else return array(FALSE, $mysqli->error . '. ID: ' . $this->id);

    }

    if (!$stmt->execute()) {
        $exit = [FALSE, $stmt->error . '. ID: ' . $this->id];
    }

    return $exit;
}

我通过在 prepare() 之前执行 var_dump($this->name); 来确保新数据存在，并且没有问题。此外，$mysqli->error; 和 $stmt->error; 中都没有错误。我在日志中收到 $exit 字符串，这意味着 $stmt->execute() 返回了 TRUE。

现在，我启用了 mysql 日志并查看了一下:

1353 Connect   user@localhost as anonymous on table
1353 Query     SET NAMES utf8mb4
1353 Prepare   UPDATE address_book ab
               INNER JOIN address_book_client abc ON abc.contact_id = ab.id
               SET ab.name = ?, ab.surname = ?, ab.cc = ?, ab.phone = ?, ab.email = ?, ab.nif = ?, ab.note = ?
               WHERE abc.contact_id = ? AND abc.company_id = ?
1353 Execute   UPDATE address_book ab
               INNER JOIN address_book_client abc ON abc.contact_id = ab.id
               SET ab.name = 'name1', ab.surname = 'surname', ab.cc = 34, ab.phone = 123456789, ab.email = 'a@a.aa', ab.nif = '12345678A', ab.not$
               WHERE abc.contact_id = NULL AND abc.company_id = 1
1353 Quit

由于某种原因，生成的查询的最后一个字段确实有问题。

这是在 UPDATE 查询中发生的，而不是在 INSERT 中发生的:

1433 Connect   user@localhost as anonymous on table
1433 Query     SET NAMES utf8mb4
1433 Prepare   INSERT INTO address_book (name, surname, cc, phone, email, nif, note)
               VALUES (?, ?, ?, ?, ?, ?, ?)
1433 Execute   INSERT INTO address_book (name, surname, cc, phone, email, nif, note)
               VALUES ('name', 'surname', 34, 123456789, 'a@a.aa', '12345678A', 'Blah blah blah...')
1433 Close stmt
1433 Prepare   INSERT INTO address_book_client (contact_id, company_id)
               VALUES (?, ?)
1433 Execute   INSERT INTO address_book_client (contact_id, company_id)
               VALUES (3, 1)
1433 Quit

出于某种未知的原因，execute() 在 ab.not 之后以 $ 结束该行。

为了确保我所做的变量没有问题:

// inside the contact() function
var_dump($this->note);

我得到的是:

string(17) "Blah blah blah..."

知道问题出在哪里吗？

Answer 1

我认为这可能是 WHERE abc.contact_id = NULL

的问题

这应该是WHERE abc.contact_id IS NULL

有关更多信息，请参阅 discussion