FairyBusiness
asked on
How can I create a many to many relationship in mysql?
Hi, my professor this last semester showed us how to do a many to many relationship in MySQL, but he only used 1 day to go over it and I did not fully understand it at the time. So now I need to have a many to many relationship, but I am not sure how to create this. (trying to avoid listed fields) Not even sure where to start. (I have only ever done one to many relationships)
If someone could please describe a SIMPLE way of doing this, or knows of a SIMPLE tutorial that would be great!
If someone could please describe a SIMPLE way of doing this, or knows of a SIMPLE tutorial that would be great!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I dont get how you would do a many to many relationship without a third table?? which table are the rows that relate to multiple rows on both sides of the two tables???
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Taking this a step further, you could actually put all of the data in the books table. The many-to-many relationship occurs because you can have the same author occur several times and you can have the same title occur several times.
Kent
Kent
ASKER
but if you have more than one item on the other side needing the title how do you specify where the title goes to which at what time? i'm just not getting it. .. I know how to create tables and give things the same name but that doesnt help me create a many to many relationship bc i'm not looking at it any new way than before
Ok. Back to the books table.
INSERT INTO books (isbn, author, title) VALUES ('00001', "Alan", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00002', "Barb", "Latin for Beginners");
INSERT INTO books (isbn, author, title) VALUES ('00003', "Chad", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00004', "Dave", "What to Wear");
INSERT INTO books (isbn, author, title) VALUES ('00005', "Earl", "Beginning Math");
INSERT INTO books (isbn, author, title) VALUES ('00006', "Dave", "Peyton Place");
A many-to-many relationship exists between the author and title columns. Dave has written two books ("What to wear" and "Peyton Place"). A book titled "English Dictionary" was written by both Alan and Chad.
If the Authors and Title are moved into separate tables the many-to-many relationship exists across tables.
Kent
INSERT INTO books (isbn, author, title) VALUES ('00001', "Alan", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00002', "Barb", "Latin for Beginners");
INSERT INTO books (isbn, author, title) VALUES ('00003', "Chad", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00004', "Dave", "What to Wear");
INSERT INTO books (isbn, author, title) VALUES ('00005', "Earl", "Beginning Math");
INSERT INTO books (isbn, author, title) VALUES ('00006', "Dave", "Peyton Place");
A many-to-many relationship exists between the author and title columns. Dave has written two books ("What to wear" and "Peyton Place"). A book titled "English Dictionary" was written by both Alan and Chad.
If the Authors and Title are moved into separate tables the many-to-many relationship exists across tables.
Kent
ASKER
It still looks like a one to many relationship in my mind. . .
you have one book english dictionary, two authors
or you have one author dave, and two books
maybe I am missing something. . .
you have one book english dictionary, two authors
or you have one author dave, and two books
maybe I am missing something. . .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok only the 3 tables makes any sense to me. .. so can someone give me an example of how that would look?
ASKER
nope that didnt work.
here is my entire function if it helps:
here is my entire function if it helps:
function woods() {
global $conn;
if(isset($_GET['id'])) {
$id = $_GET['id'];
$query = "SELECT name FROM submenus WHERE id=" . $id;
$result = mysql_query($query, $conn);
confirm_query($result);
while($rows = mysql_fetch_assoc($result)) {
$name = $rows['name'];
}
$path = "flipbook/" . $name;
$dirs = scandir($path);
foreach($dirs as $dir) {
echo $dir . "<br />";
$data = $path . "/" . $dir;
echo $data . "<br />";
$style = scandir($data);
//print_r($style[4]);
if(!is_dir($style)) {
foreach($style as $img) {
if (!is_dir($img)){
echo "<img src=\"" . $data . "/" . $img . "\" />";
}
}
}
echo "<br />";
}
}
}
ASKER
ignore that last comment, was for another question.
Hi Fairy,
Don't think of the database structure, think of the data. The relationship is between data objects. The container is immaterial.
Kent
Don't think of the database structure, think of the data. The relationship is between data objects. The container is immaterial.
Kent
ASKER
ok, I have 3 necklaces
they each contain many gemstones:
rubies
diamonds
sapphires
quartz
so all 3 necklaces contain rubies, so rubies would to three different ones with 3 coming at it
how would that work specifically? so me a row that demonstrates the many to many relationship
they each contain many gemstones:
rubies
diamonds
sapphires
quartz
so all 3 necklaces contain rubies, so rubies would to three different ones with 3 coming at it
how would that work specifically? so me a row that demonstrates the many to many relationship
Easy enough.
Necklace 1 Rubies
Necklace 1 Diamonds
Necklace 1 Sapphires
Necklace 1 Quartz
Necklace 2 Rubies
Necklace 3 Rubies
Necklace 3 Diamonds
ASKER
what if I have like 50 gemstones per necklaces? and like 50 necklaces?
The volume doesn't matter, as long as each side of the relationship supports a quantity greater than 1.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks everyone
A many-to-many relationship does not require a third table.
Kent