Jump to content


Photo

Inserting Multiple Rows At Once


  • Please log in to reply
5 replies to this topic

#1 dr.wong

dr.wong
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 16 June 2006 - 01:39 PM

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?

#2 nogray

nogray
  • Members
  • PipPipPip
  • Advanced Member
  • 930 posts
  • LocationSan Francisco CA

Posted 16 June 2006 - 05:17 PM

You'll need to set the markfield and the id as arrays like this
Mike
<input type="text" name="markfield[]" />
<input type="hidden" name="students[]" value="111" />

In your php, you'll run through the markfield array and set the insert query
$markfield_arr = $_POST['markfield'];
$students_arr = $POST['students'];

$query = "insert into `Marks` (`Student_ID`, `Mark`) values ";

for ($i=0; $i<count($markfield_arr); $i++){
     $query .= "('".$students_arr[i]."', '".$markfield_arr[i]."'), ";
}

$query = trim($query, ", ");

$result = mysql_query($query);

NoGray.com


#3 aebstract

aebstract
  • Members
  • PipPipPip
  • Advanced Member
  • 1,105 posts

Posted 16 June 2006 - 05:20 PM

Is this for that teacher in the freelance section?

There is an area of the mind that could be called unsane, beyond sanity, and yet
not insane. Think of a circle with a fine split in it. At one end there's
insanity. You go around the circle to sanity, and on the other end of the
circle, close to insanity, but not insanity, is unsanity.


#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 16 June 2006 - 05:37 PM

[!--quoteo(post=384665:date=Jun 16 2006, 12:20 PM:name=aebstract)--][div class=\'quotetop\']QUOTE(aebstract @ Jun 16 2006, 12:20 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Is this for that teacher in the freelance section?
[/quote]
LMAO wouldn't that be funny.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 aebstract

aebstract
  • Members
  • PipPipPip
  • Advanced Member
  • 1,105 posts

Posted 16 June 2006 - 05:46 PM

Yeah cause I wanted that job :(

There is an area of the mind that could be called unsane, beyond sanity, and yet
not insane. Think of a circle with a fine split in it. At one end there's
insanity. You go around the circle to sanity, and on the other end of the
circle, close to insanity, but not insanity, is unsanity.


#6 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 16 June 2006 - 06:01 PM

well sorry.. better luck next time? i was thinking it was funny cuz he took on a job in the freelance section and is turning around asking for help on it. that is..if that's what the question is about...

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users