How do I insert into a MySQL database using a loop for multiple rows?

I have data that will need to be added into a database. The data looks like this:

Jim Jones    2009-01-01     Red
Susan Hoo    2009-01-04     Red
...

I am writing a procedure for this but don't know how to loop it so that it inserts all of the rows. Is there a tutorial or something that someone can show me to get me started?

Thanks

This is a pro开发者_开发知识库cedure that will insert live data, the data is not in a file.

It really depends on what form you have the data in. If you have it as text or CSV, you can use LOAD DATA INFILE. But I don't think that will work if you have it all in a single line (hard to tell from your example).

Otherwise if you need to do it programmatically, it would help a lot if you told us which language you were using.

Edit - much better with formatting. Here is the reference page for LOAD DATA INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Edit 2 - in response to your comment, what is the interface through which users will insert new records?

Edit 3 - ok, if you have a GUI through which they will insert a record then you don't need to have a for loop, since presumably only one record gets inserted at a time. Ignoring the possibility of batching for the moment, you should just perform an INSERT whenever the user indicates through the GUI that they would like to submit their record.

You sound like a beginner so here is a simple answer. From within your development tool press F1 or select help from the menu and search for: "for", "while", "do", "until" or "loop". These are the common looping keywords.

You might want to have a look at multi dimensional arrays - suppose you have your input stored in a multi dimensional array similar to this:

$myform = array(

 0 => array(

 "name" => "Jim Jones",

 "date" => "2009-01-01",

 "colour" => "red"

 ),

 1 => array(

 "name" => "Susan Hoo",

 "date" => "2009-01-04",

 "colour" => "red"

 )

you could then do something like this:

for($x=0;$x<(sizeof($data_array));$x++) {

mysql_query("INSERT INTO your_db_table (

name,

date,

colour

) VALUES (

'$myform[$x][name]',

'$myform[$x][date]',

'$myform[$x][colour]'

)"

)

}

..please handle with care! Although I use something similar in various applications, the above coding is not tested and has probably the one or the other bug. Hope it helps anyways..:]