Join two INSERT INTOs

Question

I'm not sure how to articulate this correctly so here it goes.

I'm designing a small webapp and in my database I have a table for users, commits, and userCommits. it's pretty minimal as in users has an ID that is auto number as well as commitshas a similar ID column. Now userCommits is what links them together as you can see in the following figure.

What I've done is INSERT INTO commits (...) VALUES (...) but the problem with that is how do I create the link in userCommits so that userCommits.commitID will equal the correct commit. I feel like I shouldn't just query the table to find the latest one and use its ID. Is there a way to join the ID's somehow?

I know that I can do a query like this to list all the commits the user via email.

SELECT
commits.id,
commits.cName,
users.email
FROM
commits
INNER JOIN userCommits ON commits.id = userCommits.commitID
INNER JOIN users ON userCommits.userID = users.id
WHERE users.email = "someonecool@hotmail.com"

But what I'm now trying to do is to insert into the database when the user creates a commit.

score 2 · Accepted Answer · answered Dec 30 '17 at 16:50

2

You perform the insert and select the inserted id as part of the same command for one table, then do the same for the other table

Once your app has knowledge of both ids, it can insert into the usercommits table

See Get the new record primary key ID from mysql insert query?

answered Dec 30 '17 at 16:50

Caius Jard

72,509
5
49
80

That is exactly what I wanted. I don't know how I didn't find this while I was searching for an hour. – MatthewEnderle Dec 30 '17 at 16:53
Every DB doesn't it differently, for future ref if you end up working with sqlserver it's quite a lot like MySQL, except the command is `SELECT scope_identity()` whereas oracle has a specific `RETURNING` clause on the insert statement itself that can be used to return any value calculated during insert of a row. Sometimes googling is easier if you know what does it in one DB - I don't use MySQL a lot, but searching "MySQL equivalent select scope identity" got me there pretty quickly – Caius Jard Dec 30 '17 at 18:21

score 0 · Answer 2 · answered Dec 31 '17 at 17:27

So I didn't mention I was using CodeIgniter. My bad. However the solutions provided would work. I just want to post what actually is used for other people that may encounter this.

As I learned for mySQL each client gets a session of it's own. This means that when you call $this->db->insert_id(), or SELECT LAST_INSERT_ID(); it will return YOUR last entered ID, not the server's last entered ID.

$commitData = array(
            'cName'        => $this->input->post('commitName'),
            'cLocation'        => $this->input->post('location'),
            'cStartDate'        => $this->input->post('startDate'),
            'cEndDate'        => $this->input->post('endDate')
        );

$this->db->insert('commits', $commitData);

$userData = array(
            'commitID'        => $this->db->insert_id(),
            'userID'        => $user->id
        );

$this->db->insert('userCommits', $userData);

There is obviously going to be security built around this but this is just the basic nuts and bolts.

Join two INSERT INTOs

2 Answers2