• 5
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

Many-to-many relationships examples

I haven't found any MYSQL many-to-many relationships examples here and in google. What I am looking is to see a very simple example with php+mysql showing database's results. Can anybody write a very simple example?

Example scenario: students and courses at a university. A given student might be on several courses, and naturally a course will usually have many students.

Example tables, simple design:

CREATE TABLE `Student` (
    `StudentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(25),
    `LastName` VARCHAR(25) NOT NULL,
    PRIMARY KEY (`StudentID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `Course` (
    `CourseID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Code` VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`CourseID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `CourseMembership` (
    `Student` INT UNSIGNED NOT NULL,
    `Course` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (`Student`, `Course`),
    CONSTRAINT `Constr_CourseMembership_Student_fk`
        FOREIGN KEY `Student_fk` (`Student`) REFERENCES `Student` (`StudentID`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_CourseMembership_Course_fk`
        FOREIGN KEY `Course_fk` (`Course`) REFERENCES `Course` (`CourseID`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

Find all students registered for a course:

SELECT
    `Student`.*
FROM
    `Student`
    JOIN `CourseMembership` ON `Student`.`StudentID` = `CourseMembership`.`Student`
WHERE
    `CourseMembership`.`Course` = 1234

Find all courses taken by a given student:

SELECT
    `Course`.*
FROM
    `Course`
    JOIN `CourseMembership` ON `Course`.`CourseID` = `CourseMembership`.`Course`
WHERE
    `CourseMembership`.`Student` = 5678
  • 74
Reply Report

Here's a quick and dirty example of the SQL involved. I don't see any need to muddy up the concept with php. Just retrieve the set like you would any other.

In this example, there are many names, and many colors. People are allowed to have more than one favorite color, and many people can have the same favorite color. Hence many to many.


***** Tables **********

person
--------
id - int 
name - varchar

favColor
-------------
id - int 
color - varchar

person_color
------------
person_id - int (matches an id from person)
color_id - int (matches an id from favColor)



****** Sample Query ******

SELECT name, color 
FROM person 
    LEFT JOIN person_color ON (person.id=person_id)
    LEFT JOIN favColor ON (favColor.id=color_id)


****** Results From Sample Query *******

Name - Color
---------------
John - Blue
John - Red
Mary - Yellow
Timmy - Yellow
Suzie - Green
Suzie - Blue
etc...

Does that help?

  • 11
Reply Report
mysql> SELECT * FROm products;
+----+-----------+------------+
| id | name      | company_id |
+----+-----------+------------+
|  1 | grechka   |          1 |
|  2 | rus       |          1 |
|  3 | makaronu  |          2 |
|  4 | yachna    |          3 |
|  5 | svuniacha |          3 |
|  6 | manka     |          4 |
+----+-----------+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROm company;
+----+----------+
| id | name     |
+----+----------+
|  1 | LVIV     |
|  2 | KIEV     |
|  3 | KHarkiv  |
|  4 | MADRID   |
|  5 | MILAN   |
|  6 | KOR |
+----+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROm many_many;
+------------+---------+
| product_id | city_id |
+------------+---------+
|          1 |       1 |
|          1 |       3 |
|          2 |       3 |
|          1 |       2 |
|          1 |       4 |
|          2 |       4 |
|          2 |       1 |
|          3 |       1 |
+------------+---------+
8 rows in set (0.00 sec)

mysql> SELECT products.name,company.name FROM products JOIN many_many ON many_
ny.product_id =products.id JOIN company ON company.id= many_many.city_id;
+----------+---------+
| name     | name    |
+----------+---------+
| grechka  | LVIV    |
| grechka  | KHarkiv |
| grechka  | KIEV    |
| grechka  | MADRID  |
| rus      | KHarkiv |
| rus      | MADRID  |
| rus      | LVIV    |
| makaronu | LVIV    |
+----------+---------+
8 rows in set (0.00 sec)
  • 7
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions