Problme looping through insert statement

[lauramayer]

Afternoon,

I am on mysql v.3.23 (its not my fault, its what they have here :rolleyes: ) I have a script that runs fine in mssql but won't run here. If someone could take a look and tell me why it isn't looking? Thanks so much.

else if (isset($_POST['submit'])) {    

    foreach($arrRowData as $key => $arr) {

// get array keys (database field names)
      $arrKeys   = array_keys($arrDefaultRow);
      $arrValues = array();
      foreach($arrKeys as $key2 => $value) {
        // add submitted values of this row to value
        // array using mysql_escape_string to escape
        // e.g. single quotes which would break up the
        // SQL query otherwise
        $arrValues[] = $arr[$value];
      }	  

//Connect to the database
include ("../connect.php");
	//query
   $strInserts .= "INSERT INTO Res_Life_Dev.InterviewTimeTbl ("
                    .implode(",",$arrKeys)
                    .") VALUES ('" 
					. implode("','",$arrValues)."')";						
    }

mysql_query($strInserts)or die ("Query failed: " . mysql_error() . " Actual query: " . $strInserts);;	
header ("location: index.php"); 	
  } 

It keeps dying and giving me this error:

Query failed: You have an error in your SQL syntax near 'INSERT INTO Res_Life_Dev.InterviewTimeTbl (Application_Period,Location,Interview' at line 1 Actual query: INSERT INTO Res_Life_Dev.InterviewTimeTbl (Application_Period,Location,Interview_Date,Start_Time,End_Time,Interview_Type,Max_Interviewees) VALUES ('107701','Baker 102','2006-11-30','12:43','12:43','I','1')INSERT INTO Res_Life_Dev.InterviewTimeTbl (Application_Period,Location,Interview_Date,Start_Time,End_Time,Interview_Type,Max_Interviewees) VALUES ('107701','Baker 102','2006-11-30','12:43','12:43','I','1')INSERT INTO Res_Life_Dev.InterviewTimeTbl (Application_Period,Location,Interview_Date,Start_Time,End_Time,Interview_Type,Max_Interviewees) VALUES ('107701','Baker 102','2006-11-30','12:43','12:43','I','1')

It looks like it isn't looping through the variables. When I run it in QA and throw a GO statement in between the selects it works fine.

Any thoughts would be great.

Laura

[Neumy]

I'm not sure if this is your problem, but I had a similar issue with my latest project. I noticed that you are trying to insert the same value into your DB table multiple times. MySQL does not like this and will send out an error.

1) Something is wrong with your foreach loop which builds your $arrValues, making an array with the same insert statements. You'll have to look at your codes $arrDefaultRow and $arrRowData to figure this problem out.
2) If you want to add multiple similar entries into your table, you'll need to distinguish each insert as a unique entry. For my project, I added a count variable to the end of my Table. and just $i++ to count through the loop. Since each INSERT statements are now unique, the error disappeared.

Hopefully something here helps.

[lauramayer]

Thanks for that. I ended up breaking up the insert statement and putting the values in its own foreach loop.

Laura