0

I have a multiselect combobox on the html page. The actual list of countries is about 60 items.

<select name="country[]" multiple="multiple">
  <option value="UK">United Kingdom</option>
  <option value="FR">France</option>
  <option value="DE">Germany</option>
  <option value="JP">Japan</option>
</select>

In the Database, the country list is stored as a comma delimited string in the rows such as project one may be uk,de or fr,de,jp and so forth.

I would like to be able to select multiple items in the combobox and pull up the projects so that projects with any of the countries selected with the country will display. An issue is that the column country is stored a comma delimited string value. 

SELECT * FROM projects WHERE FIND_IN_SET("country","DE, UK")'

My issue is that the FIND_IN_SET will only match the entire string. The above code may find a project with country that has only DE or UK but will not find one with "FR,UK" for example.

How do I parse the column country and create the SQL query to display the countries that I selected in the combobox?

hohner
  • 11,498
  • 8
  • 49
  • 84
ViK
  • 1
  • 1
  • 3
    DO NOT store your data as a comma separated value – Your Common Sense Jan 19 '13 at 16:16
  • Agree with @YourCommonSense. See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) – eggyal Jan 19 '13 at 16:17
  • Base64 or string separated values is like meth. Not even once. – Rémi Breton Jan 19 '13 at 16:18
  • @RémiBreton: Meth isn't *that* bad. – eggyal Jan 19 '13 at 16:19
  • I've inherited the database and only have permissions to read so I won't be able to change it from a delimited list. – ViK Jan 19 '13 at 16:20
  • I've never yet worked on a project where, when a monumentally AWFUL design decision has been taken in the past, it is impossible to present a case for migrating to sensible design. When that day comes, I will be sure never to work with those morons again. – eggyal Jan 19 '13 at 16:24

1 Answers1

0

If you want to store multiple values in one field (a form of a set), you need to do it in a way that allows you to more easily parse it later on.

A common method is to have the delimiter on each side, including for the first and the last element:

,de,us,en,

allowing you to search for a single value in that set by just wrapping it inside the delimiter (here ,).

In your example you won't do that with Mysql btw (which technically should be possible as well), but you need to check that when you output the <SELECT> input element so that you can mark those elements as selected which value is inside the set.

hakre
  • 193,403
  • 52
  • 435
  • 836
  • Or better yet, use MySQL's [`SET`](http://dev.mysql.com/doc/en/set.html) type together with its set functions: for example, [`FIND_IN_SET()`](http://dev.mysql.com/doc/en/string-functions.html#function_find-in-set). – eggyal Jan 19 '13 at 16:21