PDO inserting last insert id into two tables

Question

I'm attempting to insert the same insertId into a couple of link tables, however I've discovered that apparently the insert id gets reset to zero after executing the first method. These methods are separate as they get reused in other places. They are in separate classes.

There are essentially three processes being called, but I'm mainly concerned with these two updates, one of which cancels the other out.

if($resource->createResource()){

    $author->resourceAuthor();
    $subject->resourceSubject();

}

The two methods are essentially identical to each other. Each one inserts data into a separate link table (many to many).

The first one is this:

function resourceAuthor()
{

    $query = "INSERT INTO authors_has_resource (resource_id, author_id) VALUES (?,?)";

    $last_id = $this->conn->lastInsertId();

    $stmt = $this->conn->prepare($query);


    if(!empty($this->id)) {
        foreach($_POST['author_id'] as $id) {


            $stmt->execute(array(
                $last_id,
                $id
            ));
        }
    }
}

and the second one is this:

function resourceSubject()
{

    $query = "INSERT INTO resource_has_subjects (resource_id, subject_id) VALUES (?,?)";

    $last_id = $this->conn->lastInsertId();

    $stmt = $this->conn->prepare($query);


    if(!empty($this->id)) {
        foreach($_POST['subject_id'] as $id) {


            $stmt->execute(array(
                $last_id,
                $id
            ));
        }
    }
}

Which ever one I run first, works fine, but the second one just enters the resource_id as 0 (the other rows, i.e, author_id or subject_id in their respective tables go in fine, but of course they end up with duplicate keys, as of course 0 is not the primary key, at least in this case)

Is there a simple way of getting around this? I've tried creating the insert id as a variable in the initial script, it does not seem to have an effect.

Edit

I have attempted something like this:

if($resource->createResource()){

    $last_id = $db->lastInsertId();
    print_r($last_id);
    $resource->resourceSubjectAuthor($last_id);


}

And then creating a new method

function resourceSubjectAuthor($last_id)
{

    $query = "INSERT INTO authors_has_resource (resource_id, author_id) VALUES (?,?)";


    $stmt = $this->conn->prepare($query);


    $query2 = "INSERT INTO resource_has_subjects (resource_id, subject_id) VALUES (?,?)";


    $stmt2 = $this->conn->prepare($query2);
    if(!empty($this->id)) {
        foreach($_POST['author_id'] as $id) {


            $stmt->execute(array(
                $last_id,
                $id
            ));
        }

        foreach($_POST['subject_id'] as $ids) {


            $stmt2->execute(array(
                $last_id,
                $ids
            ));
        }
    }       


}

The last id is being created correctly, but then nothing gets executed

[How to create a Minimal, Complete, and Verifiable example for a PDO related problem](https://phpdelusions.net/pdo/mcve) — Your Common Sense, Dec 15 '17 at 17:49
@YourCommonSense thank you for your attempt, however this does not really assist me much; I have a specific issue regarding how to retain an insert id across two methods. I have made a few attempts at debugging this and am trying to find an answer, however any assistance you can provide would be very helpful — nomistic, Dec 15 '17 at 17:57
Oh, no. It is no me trying to assist. It is what *you* have to do to assist yourself in order keep your question open. Otherwise it will be closed as off topic. — Your Common Sense, Dec 15 '17 at 18:00
Your first attempt fails for such a silly reason as getting the insert I'd from the other insert. Why your second attempt fails, nobody knows — Your Common Sense, Dec 15 '17 at 18:18
Thank you, I do understand why the first one fails. I'm just trying to figure out how to get the id to be passed across from the original. Currently attempting at using a transaction (this seemed to be suggested on the official site) to use the same id and to call it before the execution, but I'm unsure if this is the correct approach. If I solve it, I'll post the answer here. — nomistic, Dec 15 '17 at 18:21

score 1 · Accepted Answer · answered Dec 15 '17 at 19:09

Ok, I managed to solve this. The problem was directly related to the insert id being reset after one execution. I just needed to define the insert id prior to both methods being called, and then pass that variable to the methods prior to their executions.

if($resource->createResource()){

    $last_id = $db->lastInsertId();
    $author->resourceAuthor($last_id);
    $subject->resourceSubject($last_id);
}

and then in both methods, just pass the variable instead of declaring it like so:

function resourceSubject($last_id)
{

    $query = "INSERT INTO resource_has_subjects (resource_id, subject_id) VALUES (?,?)";


    $stmt = $this->conn->prepare($query);


    if(!empty($this->id)) {
        foreach($_POST['subject_id'] as $id) {


            $stmt->execute(array(
                $last_id,
                $id
            ));
        }
    }
}

and similarly:

function resourceAuthor($last_id)
{

    $query = "INSERT INTO authors_has_resource (resource_id, author_id) VALUES (?,?)";
    $stmt = $this->conn->prepare($query);


    if(!empty($this->id)) {
        foreach($_POST['author_id'] as $id) {


            $stmt->execute(array(
                $last_id,
                $id
            ));
        }
    }
}

PDO inserting last insert id into two tables

1 Answers1