PDO MYSQL插入语句不起作用（没有给出错误）[重复]

Question

This question already has an answer here:

• My PDO Statement doesn't work 1 answer

I have the below MySQL insert statements, the code runs, and PDO does not report any error messages, however the first statement does not save the data to the database (no new row in the address table) while the second fires fine.

$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']));

The SQL for the Address table:

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`);

The result of 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              

I can't see anything wrong with the code?

</div>

Answer 1

You are specifying NULL as value for the ID, but it is a NOT NULL column. ID doesn't have auto_increment, so the statement will really try to insert null into the field, which fails.

After the comments:

So we can say, your table is set up correctly. I repeated your steps locally and got the same result:

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)

When executing your code:

$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" 

Confirmation by checking the database:

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

Last guess: Parameters missing in your $_POST-Array / wrong index for accessing the array

$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;