Insert 3 Arrays Into MySQL At Once

mj99

How can I post 3 arrays at once and insert all into the same table?

$variable1[]
$variable2[]
$variable3[]

I am posting them okay but how to correctly gather them and insert into mysql eludes me.

I have tried nesting 3 foreach statements, which inserts the correct data but inserts too many duplicate rows.

Anyway, I am not a very great programmer at all so I am really guessing how to do it. Hopefully one of you guys can help.

dagon

can you show the actual array and db structure

mj99

The table is "event_positions".

The fields are

id, event_id, position_id, time, quantity

Users fill in a form selecting multiple position_id[], time[] and quantity[].

So three arrays are posted from the form.

I need to know how to insert all the array data at once.

The event_id is not presented as an array.

I've tried using 3 different foreach statements with no luck.

Weedpacket

Change the field names in the form to something like

event[0][position_id], event[0][time], event[0][quantity],
event[1][position_id], event[1][time], event[1][quantity],
...

And then you have only one array ($_POST['event']) that you need to loop over.

mj99

Thanks. I wonder if you could also tell me what comes next. How can I get what is posted into mysql?

Weedpacket

How are you doing it now?

mj99

Now that you have given me a new way to submit the arrays, I do not know what code is used right before the mysql insert.

I definitely was not doing anything like you suggested.

mj99

I posted screenshots of the form, the evants table and the events_positions table. Does this help?

http://searchenheimer.com/screens/index.php

The stater and timer choices are both dynamic.

mj99

If it helps, here is one of the wrong ways I am trying. This actually inserts all the right data into the right fields. It just loops too many times and duplicats all the data. Also, I could not get the suggested way to work.

The Form Elements

echo '<input type="checkbox" name="event_position[]" value="'.$position_row['id'].'"> <b>'.$position_row['name'].'</b>';
echo '</td>';
echo '<td class=main nowrap>';
echo 'Time: <input type="text" size="14" name="position_time[]">';
echo '</td>';
echo '<td class=main nowrap>';
echo 'Quantity: <input type="text" size="3" name="position_quantity[]">';

The MySQL Insert

if(isset($_POST['add'])){

mysql_query("INSERT INTO events
(user_id, name, location, date, notes, contacts) VALUES('$user_id', '$name', '$location', '$date', '$notes', '$contacts' ) ")
or die(mysql_error());

$event_result = mysql_query("SELECT * FROM events ORDER BY id DESC LIMIT 1")
or die(mysql_error());

$event_row = mysql_fetch_array( $event_result );

foreach($event_position as $position)
{
echo $position;

foreach($position_time as $time)
{
echo $time;

foreach($position_quantity as $quantity)
{
echo $quantity;

	$query = mysql_query("INSERT INTO event_positions (event_id,position_id,time,quantity)VALUES ('".$event_row['id']."','$position','$time',$quantity)")or die(mysql_error());
	} 
	}
	}

Weedpacket

foreach($_POST['event'] as $event)
{
   $position = $event['position_id'];
   $time = $event['time'];
   $quantity = $event['quantity'];
  // Check that $position, $time, and $quantity are valid, and if they are INSERT them.
}