Inserting Multiple Rows at once

[dr.wong]

Hey everyone.

Let say I have two tables in a MySQL database. Once called 'Marks' and another called 'Students'

The table 'Students' has two columns being : "Name" and "Student_ID"
and
The table 'Marks' has three columns being "ID" (Auto Increment), "Student_ID" and "Mark"

Now lets say that we wanted to write a script that kept a record of the students marks.
The table 'Students' would be used to generate a form on the page, basically pulling all the names and Student ID's out of its rows and using a loop to show all of the students in the table.
So if we had three students, the form would look like this :
John |__markfield__| |__ID Field__|
Mike |__markfield__| |__ID Field__|
Tony|__markfield__| |__ID Field__|

The field 'ID FIELD' is hidden and has a value of the students unique ID. The 'markfield' is an input where you can put the students mark. When we press submit, we want all of the marks and their corresponding student ID's to be placed into the table 'Marks'.

Later, if we wanted to retrieve any students marks, we could just call 'Select from MARKS where Student_ID = 110115' (or what ever the students ID is) This would show ALL of his marks in a similar loop as we used to display the form.

My Question is.. How do I insert all of these new rows at once? Remember that I will not know how many students are in the table 'Students' as new ones would be added all the time.

Any suggestions?

[jvrothjr]

you are going to have to setup an array for each variable on the page.

//define table
echo "<tr>";
//define header row
echo "</tr><tr>";
loop x = 1 to 5
echo "<td>";
echo "Student Name:<INPUT TYPE=TEXT NAME=StudentName[x] value=STNM[x]>";
echo "</td><td>";
echo "Student Mark:<INPUT TYPE=TEXT NAME=Studentmark[x]>";
echo "</td><td>";
echo "<INPUT TYPE=hidden NAME=Studentmark[x] value=STID[x]>";
echo "<td></tr>";
Next x

and when you submit the form you will have to loop thru the values also

loop x = 1 to 5
$query = "insert into Marks (Student_ID,Mark) values ('$Studentid[x]','$StudentMark')";
Studentmark[x]
Next x

This would be the Idea (basics)

[fenway]

You could also use a multi-valued INSERT statement, provided you don't need to individual IDs back.