Multiple Select Box and MySQL

PHP
1

I have a multiple selection box on my page and need to store the selections to one record in mysql:

<select name=“service” id=“service” size=“5” multiple=“multiple”>

<option value=“histopathology”>Histopathology</option>
<option value=“cytology”>Cytology</option>
<option value=“endometrial biopsy”>Endometrial Biopsy</option>
<option value=“joint fluid”>Joint Fluid</option>
<option value=“bone marrow”>Bone Marrow</option>

</select>

I need to insert into one row what the user has picked. The table has a column for each selection and can be NULL for the selections not picked. The fields are expected to insert a ‘1’ for the chosen selection. I could loop through the array and insert to mysql, but I need it in one single row.

2

Excellent points. Thank you for your help.

3

thanks, that will help

i just realized that you posted in the php forum, not the mysql forum, and i don’t do php, sorry

4

the following is the table I want to write to :

services CREATE TABLE services (
service_id int(10) unsigned NOT NULL AUTO_INCREMENT,
form_number int(20) NOT NULL,
histo tinyint(1) DEFAULT NULL,
cytology tinyint(1) DEFAULT NULL,
endo tinyint(1) DEFAULT NULL,
joint_fluid tinyint(1) DEFAULT NULL,
bone_marrow tinyint(1) DEFAULT NULL,
other varchar(50) DEFAULT NULL,
PRIMARY KEY (service_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

5

rlowe — you should change the <option> value attributes to match those column names (i.e. bone_marrow instead of bone marrow)

Then do this:


if(isset($_POST['my_form_submission...'])) {
	
	//Set form_number and other to your needs
	$form = 1234;
	$other = '';
	
	$cols = array('histo', 'cytology', 'endo', 'joint_fluid', 'bone_marrow');
	
	$query = "INSERT INTO `services` (form_number, other, histo, cytology, endo, joint_fluid, bone_marrow) " .
			 "VALUES ($form, '$other'";
			 
	foreach($cols as $col) {
		if(in_array($col, $_POST['service'])) {
			$flag = 1;
		}
		else $flag = 0;
		
		$query .= ", $flag";
	}
	
	$query .= ')'; //close VALUES()
	
	//Debug
	echo $query;
}

The allowed/expected column names are defined, so users can’t mess with your query by creating their own <options>
The code loops through the expected columns and flags them as 1 or 0 depending on whether they were selected.

6

could you do a SHOW CREATE TABLE please

7

no, your table is fine :slight_smile:

8

what I confused about is the sql insert statement. Do dynamically build the insert statement by looping through the values picked:

$services = $_POST[‘service’];
for ($i = 0; $i < count($service); $i++) {
$serviceName = $serviceName.‘,’.$service[$i];

}

$sql = "INSERT INTO services (histo, cytology, endo, joint_fluid, bone_marrow, other) values (’ ".$serviceName ;

but now I have the NULL values to think about that will not show up in the loop.
Maybe change the strategy regarding the table?