storing student module records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ma5ect
Forum Commoner
Posts: 35
Joined: Wed Jun 25, 2008 8:38 am

storing student module records

Post by ma5ect »

Hi all, i am setting up a database using sql to store details for students modules results, i have 3 tables

students
modules
results

the issue is i cant understand how to store data in the results table, as students have completed many modules, how would i store this?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: storing student module records

Post by califdon »

ma5ect wrote:Hi all, i am setting up a database using sql to store details for students modules results, i have 3 tables

students
modules
results

the issue is i cant understand how to store data in the results table, as students have completed many modules, how would i store this?
First of all, are you saying that you are using Microsoft SQL Server?

Specifically, since I don't know what you mean by "modules" and "results", it is impossible to answer your question. You will have to explain what these mean. Either define what each entity is, or show the list of fields in each table.

If you define each entity, it could be like this: "students entity is the collection of students, their names, addresses, birthdates, etc."

If you list the fields, the recommended format is like this:

Code: Select all

[b]students[/b]:
    sID       Int(10)
    sFname    varchar(20)
    sLname    varchar(20)
    sDOB      Date
    ...
ma5ect
Forum Commoner
Posts: 35
Joined: Wed Jun 25, 2008 8:38 am

Re: storing student module records

Post by ma5ect »

students table...
student ID
surname
forename
course
year

module table(subject)...
module ID
module name

results table..
student ID (FK)
module ID (FK)
completed
attempts


in the results table i want to store data of many student who has taken many modules and store there results...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: storing student module records

Post by califdon »

ma5ect wrote:students table...
student ID
surname
forename
course
year

module table(subject)...
module ID
module name

results table..
student ID (FK)
module ID (FK)
completed
attempts

in the results table i want to store data of many student who has taken many modules and store there results...
OK, that's a good start. I would suggest that you remove the Course and Year fields from the students table and put those in the results table, because they are attributes of a "result", not of a student. If the same student takes 2 courses or takes courses in different years, your structure won't allow it.

So then your question, I think, is how to populate the results table, right? Basically, you need a script that displays 2 dropdown selection boxes, one to select a student and one to select a course, and whatever other input controls you may need, such as year, completed and attempts. When the form is submitted, the script that processes it (either the same script or another one, depending on how you want to program it) will obtain the values of the student ID and module ID and whatever other data you need, and insert a record into the results table. So the trick is, how do you get those ID's? That's done in the first part, when you generate the form and its dropdown controls. In general, this is the kind of syntax you would use:

Code: Select all

...
while($row=mysql_fetch_assoc($result)) {
    ...
    echo "<option value=".$row['studentID'].">".$row['surname'].">";
    ...
}
Of course, before that code, you would have to have echoed the earlier HTML code for the selection control, and afterwards you would have to finish the </select> and all the rest of the form. The above just illustrates the way you can create a dropdown box of names, that returns the associated ID value when the form is submitted.
Post Reply