How can I create a many to many relationship in mysql?

A many-to-many relationship does not require a third table.


Kent

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???

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

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

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. . .

ok only the 3 tables makes any sense to me. .. so can someone give me an example of how that would look?

nope that didnt work.

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 />";
		}
	}
}

Select allOpen in new window

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

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

Easy enough.

Necklace 1   Rubies
Necklace 1   Diamonds
Necklace 1   Sapphires
Necklace 1   Quartz
Necklace 2   Rubies
Necklace 3   Rubies
Necklace 3   Diamonds

Select allOpen in new window

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.

thanks everyone