douwei3280
2016-07-21 13:05
采纳率: 0%
浏览 59
已采纳

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

This question already has an answer here:

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>

图片转代码服务由CSDN问答提供 功能建议

此问题已经存在 这里有一个答案:

  • 我的PDO语句不起作用 1回答

    我有以下MySQL插入语句,代码运行,PDO不报告任何错误消息,但是 第一个语句不会将数据保存到数据库(地址表中没有新行),而第二个语句会正常。

      $ sql = $ dbh-&gt; prepare(“  INSERT INTO`地址`(`ID`,`Street`,`Street2`,`Town`,`County`,`Postcode`)VALUES(NULL,?,?,?,?,?)“); 
     \  n $ sql-&gt;执行(数组($ _ POST ['street'],$ _POST ['street2'],$ _POST ['town'],$ _POST ['county'],$ _POST ['postcode'])  ); 
     
      $ addressID = $ dbh-&gt; lastInsertId(); 
     
     $ firmSQL = $ dbh-&gt; prepare(“INSERT INTO TaxiFirm(Name,LogoURL,AddressID,Phone,Email,LicenseExpiry,ContractDate,Active)VALUES(?  ,?,?,?,?,?,?,1)“); 
     $ firmSQL-&gt;执行(数组($ _ POST ['name'],$ _POST ['url'],$ addressID,$ _POST  ['phone'],$ _POST ['email'],$ _POST ['license'],$ _POST ['contract'])); 
       
     
     

    地址表的SQL:

      CREATE TABLE`Address`(
    `ID`int(11)NOT NULL,
    `Stown`varchar(100)NOT NULL,  
    `Street2` varchar(100)NOT NULL,
    `镇`varchar(100)NOT NULL,
    `County`varchar(100)NOT NULL,
    `邮政编码`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地址的结果;

      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 
       
     
     

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

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douxie0824 2016-07-21 13:18
    已采纳

    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;
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题