How do I insert into a mysql database using a loop for multuple rows?

Question

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 procedure that will insert live data, the data is not in a file.

Answer 1

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.

Answer 2

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.

Answer 3

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..:]