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.