Page 1 of 1

storing student module records

Posted: Mon Oct 20, 2008 1:30 pm
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?

Re: storing student module records

Posted: Mon Oct 20, 2008 3:12 pm
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
    ...

Re: storing student module records

Posted: Tue Oct 21, 2008 5:30 am
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...

Re: storing student module records

Posted: Tue Oct 21, 2008 6:53 pm
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.