CODEWITHSUNDEEP
mysql
MatthewEnderle
MatthewEnderle

Reputation: 119

Join two INSERT INTOs

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.

https://i.gyazo.com/1d8ad47a2bad919efe5a5e8f7f9e4e82.png

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 = "[email protected]"

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

Upvotes: 0

Views: 30

Answers (2)

MatthewEnderle
MatthewEnderle

Reputation: 119

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.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

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?

Upvotes: 2

Related Questions