php - 使用 last id PDO PHP MySQL 将数据插入到两个表中

Question

我正在尝试使用第一个表的 ID 将数据插入到两个表中。我尝试了几种可能性，但似乎都不起作用。请参阅下面我正在使用的当前方法。它正在插入到第一个表而不是第二个表。另外，没有任何错误告诉我我做错了什么。

如果有人能告诉我哪里出了问题，那就太好了。

public function addContact($cnt_fname,$cnt_lname,$cnt_email,$cnt_phone,$cnt_type,$cnt_company,$cnt_web,$cnt_add1,$cnt_add2,$cnt_city,$cnt_state,$cnt_post,$cnt_country,$cnt_status) {
    try
    {

    $stmt = $this->conn->prepare("

    START TRANSACTION;

    INSERT INTO LeadContact(lead_fname,lead_lname,lead_email,lead_phone,lead_type,lead_company,lead_add1,lead_add2,lead_city,lead_state,lead_post,lead_country,lead_status) 
    VALUES(:cnt_fname,:cnt_lname,:cnt_email,:cnt_phone,:cnt_type,:cnt_company,:cnt_add1,:cnt_add2,:cnt_city,:cnt_state,:cnt_post,:cnt_country,:cnt_status);

    INSERT INTO LeadCompany(company_phone,company_type,company_name,company_website,company_add1,company_add2,company_city,company_state,company_post,company_country,company_status,company_contact) 
    VALUES(:cnt_phone,cnt_type,:cnt_company,:cnt_web,:cnt_add1,:cnt_add2,:cnt_city,:cnt_state,:cnt_post,:cnt_country,:cnt_status,last_insert_id());

    COMMIT;
    ");

    $stmt->bindparam(":cnt_fname", $cnt_fname);
    $stmt->bindparam(":cnt_lname", $cnt_lname);
    $stmt->bindparam(":cnt_email", $cnt_email);                                       
    $stmt->bindparam(":cnt_phone", $cnt_phone);                                       
    $stmt->bindparam(":cnt_type", $cnt_type);                                         
    $stmt->bindparam(":cnt_company", $cnt_company);                                       
    $stmt->bindparam(":cnt_add1", $cnt_add1);                                         
    $stmt->bindparam(":cnt_add2", $cnt_add2);                                         
    $stmt->bindparam(":cnt_city", $cnt_city);                                         
    $stmt->bindparam(":cnt_state", $cnt_state);                                       
    $stmt->bindparam(":cnt_post", $cnt_post);                                         
    $stmt->bindparam(":cnt_country", $cnt_country);                                       
    $stmt->bindparam(":cnt_status", $cnt_status);
    $stmt->bindparam(":cnt_web", $cnt_web);

    $stmt->execute();

    return $stmt;

}
catch(PDOException $e)
{
    echo $e->getMessage();
}               
}

建议的重复问题与我尝试使用 last_insert_id() 函数的问题不同。

Answer 1

使用Transactions ，先执行第一条命令，得到Last Insert Id , 在下一个插入中使用它。

public function addContact($cnt_fname,$cnt_lname,$cnt_email,$cnt_phone,$cnt_type,$cnt_company,$cnt_web,$cnt_add1,$cnt_add2,$cnt_city,$cnt_state,$cnt_post,$cnt_country,$cnt_status)
{

    try {
        $db->beginTransaction();

        $stmt = $db->prepare("INSERT INTO LeadContact(lead_fname,lead_lname,lead_email,lead_phone,lead_type,lead_company,lead_add1,lead_add2,lead_city,lead_state,lead_post,lead_country,lead_status) 
  VALUES(:cnt_fname,:cnt_lname,:cnt_email,:cnt_phone,:cnt_type,:cnt_company,:cnt_add1,:cnt_add2,:cnt_city,:cnt_state,:cnt_post,:cnt_country,:cnt_status)");
        $stmt->bindparam(":cnt_fname", $cnt_fname);
        $stmt->bindparam(":cnt_lname", $cnt_lname);
        $stmt->bindparam(":cnt_email", $cnt_email);
        $stmt->bindparam(":cnt_phone", $cnt_phone);
        $stmt->bindparam(":cnt_type", $cnt_type);
        $stmt->bindparam(":cnt_company", $cnt_company);
        $stmt->bindparam(":cnt_add1", $cnt_add1);
        $stmt->bindparam(":cnt_add2", $cnt_add2);
        $stmt->bindparam(":cnt_city", $cnt_city);
        $stmt->bindparam(":cnt_state", $cnt_state);
        $stmt->bindparam(":cnt_post", $cnt_post);
        $stmt->bindparam(":cnt_country", $cnt_country);
        $stmt->bindparam(":cnt_status", $cnt_status);

        $insertId = $db->lastInsertId();

        $stmt = $db->prepare("INSERT INTO LeadCompany(company_phone,company_type,company_name,company_website,company_add1,company_add2,company_city,company_state,company_post,company_country,company_status,company_contact) 
  VALUES(:cnt_phone,:cnt_type,:cnt_company,:cnt_web,:cnt_add1,:cnt_add2,:cnt_city,:cnt_state,:cnt_post,:cnt_country,:cnt_status,:id)");
        $stmt->bindparam(":cnt_phone", $cnt_phone);
        $stmt->bindparam(":cnt_type", $cnt_type);
        $stmt->bindparam(":cnt_company", $cnt_company);
        $stmt->bindparam(":cnt_web", $cnt_web);
        $stmt->bindparam(":cnt_add1", $cnt_add1);
        $stmt->bindparam(":cnt_add2", $cnt_add2);
        $stmt->bindparam(":cnt_city", $cnt_city);
        $stmt->bindparam(":cnt_state", $cnt_state);
        $stmt->bindparam(":cnt_post", $cnt_post);
        $stmt->bindparam(":cnt_country", $cnt_country);
        $stmt->bindparam(":cnt_status", $cnt_status);
        $stmt->bindparam(":id", $insertId);
        $stmt->execute();

        $db->commit();
    } catch (PDOException $ex) {
        //Something went wrong rollback!
        $db->rollBack();
        throw $ex;
    }
}