[RESOLVED] Inserting multiple rows with auto-increment field

[ali_p]

Hi,

I have a table in my DB that contains these fields:
id, dates_supp, week, weekend, mid_week

What i am trying to do is enter multiple rows into the table using one input form , theres a few rows to go in but this is what they look like:

<input type="text" name="date_supp[]" value="11 Mar - 7 Apr">
<input type="text" name="week[]" value="500" size="5>
<input type="text" name="weekend[]" value="350" size="5 ">
<input type="text" name="midweek[]" value="300" size="5">

Here is the input code that i am using to try to put all rows into my table

$conn = mysql_connect("$glob_host", "$glob_user", "$glob_pass")
	or die(mysql_error());
mysql_select_db($db_name,$conn) or die(mysql_error());






foreach ($_POST['date_supp'] as $row=>$date)
{
  $date_supp = mysql_real_escape_string($date);

  $week = mysql_real_escape_string($_POST['week'][$row]);

  $weekend = mysql_real_escape_string($_POST['weekend'][$row]);

  $midweek = mysql_real_escape_string($_POST['midweek'][$row]);

  mysql_query('INSERT INTO `cottage_rates` VALUES(" '', ' . $date_supp . ', ' . $week . ',' .$weekend .','. $midweek .'")') or die(mysql_error());
}

My id field needs to generate a new number , but none of the above seems to be working. Can anyone shed any light on this please.

Thanks in advance

[devinemke]

i suggest you reorganize the incoming array like this:

<input type="text" name="data[1][date_supp]"> 
<input type="text" name="data[1][week]"> 
<input type="text" name="data[1][weekend]"> 
<input type="text" name="data[1][midweek]">
<br>
<input type="text" name="data[2][date_supp]"> 
<input type="text" name="data[2][week]"> 
<input type="text" name="data[2][weekend]"> 
<input type="text" name="data[2][midweek]">
<br>
<input type="text" name="data[3][date_supp]"> 
<input type="text" name="data[3][week]"> 
<input type="text" name="data[3][weekend]"> 
<input type="text" name="data[3][midweek]">

then you form processor would look something like:

foreach ($_POST['data'] as $value)
{
	$value = array_map('addslashes', $value);
	extract($value);

mysql_query("INSERT INTO cottage_rates SET 
date_supp = '$date_supp',
week = '$week',
weekend = '$weekend',
midweek = '$midweek'") or exit(mysql_error());
}

[ahundiak]

You need to use NULL for the value of the ID field

INSERT INTO cottage_rates VALUES(NULL,'the date','the week','weekend','midweek');

And avoid using SET with INSERT. It works in mysql but it's not standard SQL.
http://dev.mysql.com/doc/refman/5.0/en/insert.html

[ali_p]

Many thanks for your replies , I changed my code to the following and it also seems to do the job.

$field1 = $_POST['date_supp'];
$field2 = $_POST['week'];
$field3 = $_POST['weekend'];
$field4 = $_POST['midweek'];

$values=""; 
for ($i=0;$i<count($field1);$i++) 
{ 
$values.="('','$field1[$i]','$field2[$i]','$field3[$i]','$field4[$i]'),"; 
} 
$values=substr($values,0,-1); // to remove last comma 
$query="insert into cottage_rates values $values"; 
$result = mysql_query($query, $conn)or die(mysql_error());