CODEWITHSUNDEEP
phpmysql
a7omiton
a7omiton

Reputation: 1617

Querying a Multiple Select Box

I'm curious as to how you can insert the selected items into a database when you don't know exactly how many options a user may select.

Say I have the following code:

<select name="foo[]" multiple="multiple">
     <option value="dog">Dog</option>
     <option value="cat">Cat</option>
     <option value="fish">Fish</option>
</select>

<?php

$pets = $_POST['foo'];

$query = mysql_query("INSERT INTO `pets` (`pet1`, `pet2`, `pet3`) VALUES ('".$pets[0]."', '".$pets[1]."',  '".$pets[2]."')");
?>

And the user selects only the option 'dog', or 'dog' and 'cat', or 'cat' and 'dog', etc. Basically any combination of the three.

Would I have to hard-code the query to accept all possible options and then specify a WHERE clause saying "WHERE $pets != ''" or something similar?

I'd appreciate any help.

Upvotes: 0

Views: 233

Answers (2)

Seder
Seder

Reputation: 2763

First of all, do not use mysql_* functions because are deprecated

If you need to insert them in multiple rows you can simply use loop like this 

foreach($pets as $pet)
{
      Insert to db each single pet in different row 
}

If you want to insert them in the same row you can use 

$toInsert = implode(',',$pets);

put if you have n numbers of columns which i don't think you can use the 2nd option and the implode function for number of pets 

$petsColumns = array(); 

for($i = 1; $i <= count($pets); $i++) 
     $petsColumns[] = "pet".$i ; 

$petsColumnsString = implode(',',$petsColumns);

And concate them to the query

If hope this can help of what you are trying to do

Upvotes: 1

G-Nugget
G-Nugget

Reputation: 8836

There are a few options for this situation.

First, you could store the data as a serialized string. This is fine if you only need to use the data on the PHP side. The main downside is that MySQL won't really understand the data, so it can't filter the data before it gets to PHP.

The second option is a SET data type column. the SET data type allows you to have any combination of possible options while making the database more aware of the data. There is a limit of 64 members in a SET. The SET option is the smallest option, but filtering on the column is not optimal. Changing the possible values is also a bit difficult.

The third option is to normalize the values to a separate table. You would have the users table and a pets table. The pets table would have the user ID, a pet column, and a unique ID for the table. Then you would just insert a row into the pets table for each entry the user select. This allows you to have as many entries per user and allow you to easily filter on the data. This is usually the best way to go. It's probably also a good idea to normalize the possible pet values into a third table. That table would have a pet ID, pet name, and whatever else you might want with it. The second table would then act as a bridge between the users and pets, having the user ID, pet ID, and a unique ID for the table.

Upvotes: 0

Related Questions