Inserting Multiple Datas in Database using single query with for loop

Question

How to Insert Multiple Datas into database for each month in my $dateentry I have 6 dates. I need to store them into database with the same ID.

mysql_query("INSERT INTO testtable (ID, MonthEntries) VALUES('1','$dateentry')")
    or die(mysql_error()); 

$dateentry = 2010-01-01, 2010-02-01, 2010-03-01, 2010-04-01, 2010-05-01, 2010-06-01,

Structure ID=INT MonthEntries=DATE

for every dates in $dateentry I need to insert them each with the same ID. example data table output in my database table below.

-------------------
ID | MonthEntries |
01 | 2010-01-01   |
01 | 2010-02-01   |
01 | 2010-03-01   |
01 | 2010-04-01   |
01 | 2010-05-01   |
01 | 2011-06-01   |
-------------------

I think that I need to use for loop in sql query but I'm a bit lost and I don't know how to construct it.

possible duplicate of [Inserting multiple rows in a single SQL query?](http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) — CRABOLO, Aug 16 '14 at 14:13
You need a loop in application level code, not in SQL (you can choose to run multuple single statements or one bulk insert as you see fit though). And please, do not store multiple values in one string, that's what arrays are for. — Wrikken, Aug 16 '14 at 14:13
I don't have primary key in my database and my dateentry are determined by two date. I dunno I can use array for that. — ImNoobSarry, Aug 16 '14 at 14:18
I think I got a solution to my problem. customize my $dateentry into ('$id', '2010-01-01'), ('$id', '2010-02-01') and so on — ImNoobSarry, Aug 16 '14 at 14:32
possible duplicate of [How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?](http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part) — Peter O., Aug 18 '14 at 13:17

Pagerange · Accepted Answer · 2014-08-17T19:25:02.720

Like @Wrikken says in his comment, you need a loop at the application level. The entry data should first be store in an array you can loop through. Something like:

$id = 1;
$entries = array( '2010-01-01', 
                  '2010-02-01', 
                  '2010-03-01', 
                  '2010-04-01', 
                  '2010-05-01', 
                  '2010-06-01');

foreach($entries as $entry) {

mysql_query("INSERT INTO testtable (ID, MonthEntries) VALUES($id,'{$entry}')")
    or die(mysql_error());

}

Note: I haven't checked your SQL to see if it works.

Revised code block based on the code snippet in your comment:

$date = '2010-01-01';
    $end_date = '2010-06-01';

    $dateentry = array();
    $id = 1; // required by your MySQL insert statement

    // populate $dateentry array with dates
    while (strtotime($date) <= strtotime($end_date)) {
        $dateentry[] =  date("Y-m-d", strtotime($date));
        $date = date ("Y-m-d", strtotime("+1 month", strtotime($date)));
    } // end while

    // loop through $dateentry and insert each date into database
    foreach($dateentry as $entry) {

        mysql_query("INSERT INTO testtable 
            (ID, MonthEntries)  VALUES($id,'{$entry}')")
            or die(mysql_error());

    } // end foreach

Again, I have not checked your MySQL statement for errors

thank you this works. got a question how come in my array I don't have any comma in my date but it still works if I echo my $entries it will be 2010-01-012010-02-012010-03-012010-04-012010-05-01 — ImNoobSarry, Aug 16 '14 at 15:04
While your code snipped may work, it probably isn't what you really want. You have your for loop nested within your while loop. You may want something like this instead. I have edited my answer to add a revised code block based on your code snipped. — Pagerange, Aug 17 '14 at 19:23

Inserting Multiple Datas in Database using single query with for loop

1 Answers1