PDO MYSQL插入语句不起作用(没有给出错误） - PDO MYSQL insert statement not working (no error given)

PDO MYSQL insert statement not working (no error given)

我有下面的MySQL插入语句，代码运行，PDO不会报告任何错误消息，但是第一条语句不会将数据保存到数据库（地址表中没有新行），而第二条语句则很好。

$sql=$dbh->prepare("INSERT INTO `Address` (`ID`, `Street`, `Street2`, `Town`, `County`, `Postcode`) VALUES (NULL, ?,?,?,?,?)");
        $sql->execute(array($_POST['street'], $_POST['street2'], $_POST['town'], $_POST['county'], $_POST['postcode']));
        $addressID = $dbh->lastInsertId();
        $firmSQL = $dbh->prepare("INSERT INTO TaxiFirm (Name, LogoURL, AddressID, Phone, Email, LicenseExpiry, ContractDate, Active) VALUES (?, ?, ?, ?, ?, ?, ?, 1)");
        $firmSQL->execute(array($_POST['name'], $_POST['url'], $addressID, $_POST['phone'], $_POST['email'], $_POST['license'], $_POST['contract']));

地址表的SQL：

CREATE TABLE `Address` (
  `ID` int(11) NOT NULL,
  `Street` varchar(100) NOT NULL,
  `Street2` varchar(100) NOT NULL,
  `Town` varchar(100) NOT NULL,
  `County` varchar(100) NOT NULL,
  `Postcode` varchar(20) NOT NULL
) 
ALTER TABLE `Address`   MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `Address`
  ADD PRIMARY KEY (`ID`);

DESCRIBE Address; 的结果

ID  int(11) NO  PRI     auto_increment  
Street  varchar(100)    NO              
Street2 varchar(100)    NO              
Town    varchar(100)    NO              
County  varchar(100)    NO              
Postcode    varchar(20) NO

我看不出代码有什么问题？

您将NULL指定为ID的值，但它是NOT NULL列。ID没有auto_increment，因此该语句将真正尝试将null插入到字段中，但失败了。

评论后：

所以我们可以说，你的桌子设置正确。我在本地重复了你的步骤，得到了相同的结果：

mysql> DESCRIBE Address;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int(11)      | NO   | PRI | NULL    | auto_increment |
| Street   | varchar(100) | NO   |     | NULL    |                |
| Street2  | varchar(100) | NO   |     | NULL    |                |
| Town     | varchar(100) | NO   |     | NULL    |                |
| County   | varchar(100) | NO   |     | NULL    |                |
| Postcode | varchar(20)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

执行代码时：

$dbh = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', '', array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));
$sql = $dbh->prepare("INSERT INTO `Address` (`ID`, `Street`, `Street2`, `Town`, `County`, `Postcode`) VALUES (NULL, ?,?,?,?,?)");
$sql->execute(array('street', 'street2', "town", 'county', 'postcode'));
$addressID = $dbh->lastInsertId();
var_dump($addressID); // string(1) "3"

通过检查数据库进行确认：

mysql> SELECT * FROM Address;
+----+--------+---------+------+--------+----------+
| ID | Street | Street2 | Town | County | Postcode |
+----+--------+---------+------+--------+----------+
|  3 | street | street2 | town | county | postcode |
+----+--------+---------+------+--------+----------+
1 row in set (0.00 sec)

最后猜测：$_POST中缺少参数-阵列/访问阵列的错误索引

$dbh = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', '', array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));
$sql = $dbh->prepare("INSERT INTO `Address` (`ID`, `Street`, `Street2`, `Town`, `County`, `Postcode`) VALUES (NULL, ?,?,?,?,?)");
$sql->execute(array('street', 'street2', null, 'county', 'postcode'));
$addressID = $dbh->lastInsertId();
var_dump($addressID); // string(1) "0" 
exit;