[RESOLVED] trouble inserting array values into separate table records

[spivey]

I have written a form. I have some arrays, and I want to have each value of the array get its own line in my database. However, in this array, there can be anywhere from 1-8 values chosen.

It is an incident report form, and for an example, I will use "location". An incident typically happens in one area, but could cover more than one area (a leaky pipe that does damage in several areas, for example).

Here is the code from my form.

<select name="location[]" multiple="multiple">
		<option value="[Circulation]">Circulation</option>
		<option value="[Lobby]">Lobby</option>
		<option value="[Reference]">Reference</option>
		<option value="[Popular_Materials">Popular Materials</option>
		<option value="[Garden]">Reading Garden</option>
		<option value="[Meeting_Room]">Meeting Room</option>
		<option value="[YouthServ]">Youth Services</option>
		<option value="[ElecServ]">Electronic Services</option>
		<option value="[TechServ]">Tech Services</option>
		<option value="[Administration]">Administration Offices</option>
		<option value="[Parking_Lot]">Parking Lot</option>
		<option value="[BuildingServices]">Building Services</option>
	</select></font></p>

In the database, I want incident reports to be able to be sorted by location, so I have a separate table for location, linked to the "incident" table:

CREATE TABLE `location` (
  `lid` int(11) NOT NULL auto_increment,
  `location` varchar(100) default NULL,
  `incid` int(11) NOT NULL,
  PRIMARY KEY  (`lid`),
  KEY `loc_data` (`lid`),
  KEY `incid` (`incid`),
  CONSTRAINT `location_ibfk_1` FOREIGN KEY (`incid`) REFERENCES `incident` (`inc
id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

How do I create a statement that inserts the separate "location" values into distinct records in the location table, generating unique "lid" values, for exactly as many values as are entered? On one report it could only be one, on another report it could be three, etc.

I have come up with the following:

if (isset($_POST['location'])) {
$location = array('{$_POST[location]}');
foreach($location as $val) {
mysql_query("INSERT INTO location (lid, location, incid) VALUES (null, {$val}, {$incid}");
echo $val;
} 
}

It doesn't work, no big shock.

Thanks in advance to anyone willing to help.

Spivey

[__berfuzz]

edit: wrong of me

[spivey]

Answer:

foreach ($_POST['location'] as $row=>$name)
{
$location = mysql_real_escape_string($name);
$query2 = "INSERT INTO location (lid, location, incid) VALUES (null, '{$location}', '{$incid}')";
mysql_query($query2, $con);
}

Source: http://www.developertutorials.com/tutorials/php/inserting-array-into-database-070201/page1.html

Spivey