2

I'm trying to wrap my head around many-to-many table connections. I have three tables: Modules, Phases, and Module_Phases (junction).

Both Modules and Phases have primary keys as auto_increment.

create table Modules
(
    MID int not null auto_increment primary key,
    modulesID int unique,
    title varchar(20),
    article Text,
    dateAdded datetime default null,
    UID int,
    pictureurl varchar(100),
    linkurl varchar(50),
    KID int
);

create table Phases
(
    PID int not null auto_increment primary key,
    phasesID int(1),
    phasename varchar(30),
    phaseage varchar(10),
    phasearea varchar (30),
    MID int
);

create table Phases_Modules
(
    PID int not null,
    MID int not null
);

When I insert into a table, I usually disregard the primary key and just use:

insert into Modules(modulesID,title,article,pictureurl,linkurl) values (1,"namex","textcontent","text2","text3");

On the web, I've seen people suggest using:

insert into Phases_Modules(PID,MID) values (x,x);

But since both of the tables have auto_increment on the primary key, how do I go about using the correct ID key? I've seen some examples where they use LAST_INSERT_ID. But can I, for example, use something like this:

insert into Phases_Modules(PID,MID) values (PID where Phases.phaseID=x, Modules.Last_Insert_ID);

I know this was kind of a bogus line, but it was just to try to explain the issue. :)

Aaron
  • 7,000
  • 2
  • 21
  • 37
svennand
  • 73
  • 1
  • 8
  • 2
    insert into M after get the LAST_INSERT_ID into a variable same for P put the LAST_INSERT_ID into another variable.THen insert these 2 variables into PM. – Mihai Apr 03 '16 at 10:10
  • 3
    What Mihai is trying to say is that you should execute seperate queries. – Boy Apr 03 '16 at 10:14
  • I found that for this one the table phases was premade so i could use this: insert into Phases_Modules (PID,MID) values (5,last_insert_id()); Will look into using variables for storing temporary later. Any suggestion as to an select query to get modules that is connected to phase x through that junction table? :) – svennand Apr 03 '16 at 12:34
  • Answer my own second question :P `select Modules.* from Modules inner join Phases_Modules on Modules.MID = Phases_Modules.MID inner join Phases on Phases.PID = Phases_Modules.PID and Phases.PID=1;` – svennand Apr 03 '16 at 13:10
  • @svennand I see in your last comment that you found an answer to your own question. Please post it as an answer with a brief explanation and mark it as accepted so others (including me :) ) can easily see how you solved it. Thanks! – Aaron Apr 18 '16 at 21:58
  • For this particular example/question i had static content on one of the tables. meaning only one table was getting auto incremented on its primary key. So i basiclly made the adding to the junction table for each time i inserted an new module This way the last id was allways the correct one for the module. And since Phases was static/precreated i just manually added correct number. 'insert into Phases_Modules (PID,MID) values (3,last_insert_id());' – svennand Apr 19 '16 at 07:54

0 Answers0