CODEWITHSUNDEEP
phpmysqlsqlarrays
SilverSlug
SilverSlug

Reputation: 57

Needing help storing dynamic list in mySQL

I am working on a project where I need to store an unknown length list on names in an sql db. It will be a list of students in a class, which will be different for each class. I also need to be able to search for them (in PHP/SQL) by student name to see all the classes a student attended. I was thinking about storing the class as a row and the students as an array, but I can't figure out an sql to query the arrays in the db. Am I heading the right direction? Maybe creating a new db row for each student for each class? Maybe making the students rows and update an array of classes for each? I will probably using AJAX later to retrieve the info. Thanks!

Upvotes: 0

Views: 129

Answers (2)

Andreas Rayo Kniep
Andreas Rayo Kniep

Reputation: 6542

Why don't you create two tables to store the information about existing Classes and Students and a relation-table Participant that stores the information about which student goes to which class?

Something along the lines of: 

CREATE TABLE Classes (
  id          int NOT NULL,
  description varchar(200),
  PRIMARY KEY (id)
)

CREATE TABLE Studentes (
  id          int NOT NULL,
  name        varchar(200),
  PRIMARY KEY (id)
)

CREATE TABLE Participant (
  student_id  int NOT NULL,
  class_id    int NOT NULL,
  FOREIGN KEY (student_id) REFERENCES Students(id)
  FOREIGN KEY (class_id) REFERENCES Classes(id)
)

Then later, you can find out what classes a students visits with a SQL-query like:

SELECT c.description
     FROM Students s
LEFT JOIN Participant p  ON (s.id = p.student_id)
LEFT JOIN Classes     c  ON (p.class_id = c.id)
WHERE s.name = 'SilverSlug'
;

Upvotes: 1

Sari Rahal
Sari Rahal

Reputation: 1955

What you are looking for is a relations table. It will keep track of class and students. Example

Classes Table : table of all the classes

  ID = Primary key
|ID | Name |
| 1 | Math |

Students Table : table of all the students

  ID = Primary key
|ID | Name |
| 1 | Sam  |
| 2 | Tom  |

Relations Table : Used to keep track of an instance of a class

  ID = Primary key  Class_ID & Student_ID are Secondary keys
|ID | Class_ID | Student_ID | Time |
| 1 |      1   |     1      | 6am  |
| 2 |      1   |     2      | 6am  |

With these tables you can make simple queries to find out who's in what class, you can also find out what classes a student has.

Upvotes: 1

Related Questions