3

I am trying to insert data to two tables using the id for the first table. I have tried several possibilities but none seem to work. Please see below for the current method I am using. It is inserting to the first table but not the second. Plus there isn't any error telling me what I did wrong.

If anyone can tell me where I am going wrong that would be great. 

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();
}               
}

The suggested duplicate is not the same question as I am trying to use the last_insert_id() function.

robolist
  • 159
  • 1
  • 14
  • Possible duplicate of [PDO Prepared Inserts multiple rows in single query](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) – Neodan Nov 24 '17 at 12:50
  • If I'm not totally wrong the problem lies within your prepared statement. You have two queries in which the params shall be bind to. I couldn't find anything on the fly to support my concern but neither could I find an example or code where it was done this way. There's always just one query, meaning that you'll probably have to separate the two insert statements. But I also believe that you could shorten your param-binding -> https://stackoverflow.com/questions/12344741/binding-multiple-values-in-pdo :) – Kathara Nov 24 '17 at 12:58
  • 1
    Possible duplicate of [ID from another table on insertion using PDO](https://stackoverflow.com/questions/35611809/id-from-another-table-on-insertion-using-pdo) – Masivuye Cokile Nov 24 '17 at 13:00
  • can you run both query separately ?? so you will get last inserted id for both of that query – krupal parsana Nov 24 '17 at 13:01
  • My understanding is that you cannot use bindparam on a multiquery - but I may be mistaken. – Strawberry Nov 24 '17 at 13:21

2 Answers2

3

Use Transactions, first execute the first command and get Last Insert Id, use it on the next insert.

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;
    }
}
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Junius L
  • 15,881
  • 6
  • 52
  • 96
  • This worked a charm. Just had to add the execute after the first insert and change the $db variable to the correct connection and it all worked. thanks. – robolist Nov 27 '17 at 09:49
2

Separating the queries might help, you need to first execute the first query then get the ID of that first insert then use the id to insert new table.Remember, if you use a transaction you should use lastInsertId BEFORE you commit otherwise it will return 0

<?php
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 {
        $this->conn->BeginTransaction();

        $stmt = $this->conn->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);

        if ($stmt->execute()) {

            //insert to table 2

            $inserted_id = $this->conn->lastInsertId(); //get last id


            $sql = $this->conn->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,:insertid)");
            $sql->bindparam(":cnt_fname", $cnt_fname);
            $sql->bindparam(":cnt_lname", $cnt_lname);
            $sql->bindparam(":cnt_email", $cnt_email);
            $sql->bindparam(":cnt_phone", $cnt_phone);
            $sql->bindparam(":cnt_type", $cnt_type);
            $sql->bindparam(":cnt_company", $cnt_company);
            $sql->bindparam(":cnt_add1", $cnt_add1);
            $sql->bindparam(":cnt_add2", $cnt_add2);
            $sql->bindparam(":cnt_city", $cnt_city);
            $sql->bindparam(":cnt_state", $cnt_state);
            $sql->bindparam(":cnt_post", $cnt_post);
            $sql->bindparam(":cnt_country", $cnt_country);
            $sql->bindparam(":cnt_status", $cnt_status);
            $sql->bindparam(":insertidr", $inserted_id);

            if ($sql->execute()) {
                return $sql;
            }

        } else {

            throw new Exception("Error inserting");

        }

        if ($this->conn->commit()) {
            $success = true;
        } else {
            throw new Exception('Transaction commit failed.');
        }

    }
    catch (Exception $e) {
        try {
            // something went wrong, we have to rollback
            $this->conn->rollback();
            // and display the error message
            echo $e->getMessage();
        }
        catch (Exception $f) {
            // and display the error message
            echo $f->getMessage();
        }
    }
}
?>
Masivuye Cokile
  • 4,754
  • 3
  • 19
  • 34
  • Many thanks for the reply. Unfortunately this throws the error 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'... I did notice that your response had too many binds in the second query. so I matched it to the correct amount but it still came up with that error. – robolist Nov 24 '17 at 13:28
  • Try to start with few params then add them one by one and see – Masivuye Cokile Nov 24 '17 at 13:30