PDO MYSQL insert statement not working (no error given)

Question

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?

Philipp · Accepted Answer · 2017-01-12T10:36:27.573

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;

Thank you, I actually left off some the SQL, ID is set to auto_incremenet, please see the update — DevWithZachary, Jul 21 '16 at 14:10
Didn't you get an error like "[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key"? — Philipp, Jul 21 '16 at 14:25
Nope, because i missed off more sql, updated again and this time its in tact — DevWithZachary, Jul 21 '16 at 14:26
Haha, alright. So did you do the commands in the correct order? Can you add the result of `DESCRIBE Address;` to your question? Just to be sure — Philipp, Jul 21 '16 at 14:28
Can you dump the $_POST-array to check if you have all the data needed and are accessing them correctly? — Philipp, Jul 21 '16 at 14:43

PDO MYSQL insert statement not working (no error given)

1 Answers1