Making a Junction Table in phpMyadmin for MySQL

in this video tutorial we're going to see how to create a junction table and PHP my admin we've already seen this in the tutorial before and where we did it in Access which was a lot more visual the only problem with PHP my admin we go through the same process as both it's just a little bit more tricky or a little bit less visual and you have to have a lot more of a handle on the different foreign keys and relationships that you've got to have so we're going to use our library database as always and at the moment we've got eight different tables there we want to create a new table called study groups and then we want to make a many to many relationship between study groups and the members table so let's start off by making our study groups table so over the side ok on create table and the table name is study groups and again I'm using my MySQL naming convention all capital letters and in this table we're just going to fields it's going to be pretty simple one of them is going to be a primary key field so the first primary key field is study group underscore ID and the length is 7 it's a primary key so we should allow PHP my admin to take care of the incrementing of the primary key so I'm going to check on the auto increment variable and that's slightly off our screen here so but let's check down and then the next field I'm gonna put on here is just study group name and again I'm using that a particular type of MySQL naming convention here and that's fine and that's it that's all I'm going to do in that table so I'm going to save that table and that's executed fine and study groups it's gone in there all right so we've got study groups table and we've got a members table as well now what I want to do is I want to create a many-to-many relationship between those two different things and so the whole idea is is that a member in a library can sign up to lots of different study groups so one member might decide I want to join the math study group and another and they also want to join the yeah the English study group at but also study groups can have many different members attach them and that makes sense as well because a study group isn't much fun unless you've got other people in the study group along with you so we're going to create this junction table and again I'm going to click down here to create table and the name that I'm going to put on my junction table is J and CT underscore study group is on one side and members is on the other so again it's using the naming convention that's listed underneath the course and I can just create how many different columns do I need well there are two ways to do Junction tables one way is with three columns of one way is with two columns we're just going to use a 3 column version at the moment because it's a little bit simpler so I'm just going to click on go there and a new table is created so the 3 different columns are going to be the primary key fields of the overall table and then the other two are the two foreign keys one to each of the other tables so I want to study groups and want to members so in the primary key fields what I'm going to write here is study group members study group group member underscore ID and the length here is seven again I'm going to put on auto increment and then the next field is a study group foreign key that's fine and then the next field is a member foreign key that's okay and these are both these are all numbers so int + int + int is they all are related primary keys so that's that okay they're foreign keys as well so I'm going to index them and again this is off the screens butter over the side here I'm just going to go to the index column of these and just switch them to index and that should be fine so I'm going to click save on that and that table is created so we can see a junction table very very easily here because of the prefix so they're quite obvious now that's all well and good but we want to make a relationship it and to make those relationships I'm just going to go into the junction Study Group's on its core memories the junction table basically that's where everything will happen go into relations you and because I've indexed the two foreign key fields I've got a drop-down menu if you don't see these drop-down menus it means that you haven't got the fields indexed so you have to go back to the structure of the table and change that and so the study group foreign key should relate to and I see all the different index views available to me so it should relate to study groups study group underscore ID which is a primary key of the study groups table and on that I'm going to go on the leach restrict on update cascade and then member underscore FK the foreign key should relate to the primary key of the members table which is their members dot member underscore ID and again on delete restrict on update cascade and I save that and that query has gone through no problem and so those are all my relationships set up my junction table is set up and now all I have to do is populate it with data

Channel: Q Visible

Views: 17,162

Rating: 4.5555553 out of 5

Keywords: MySQL, php, MAMP, web, development, Web Development (Industry)

Id: UvMZqwyErrs

Length: 5min 40sec (340 seconds)

Published: Thu May 02 2013