如何从HTML多重选择组合框查询存储为逗号分隔值的值

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

我在html页面上有一个多选组合框。实际的国家名单大约有60个。

<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.

在数据库中,国家/地区列表存储为行中的逗号分隔字符串,例如项目一可以是uk,de或fr,de,jp等。

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.

我的问题是FIND_IN_SET只匹配整个字符串。上面的代码可能会找到一个项目,其国家/地区只有DE或UK,但不会找到“FR,UK”的项目。

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

如何解析列国家/地区并创建SQL查询以显示我在组合框中选择的国家/地区?

1 个解决方案

#1

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.

在您的示例中,您不会使用Mysql btw(技术上也应该可以),但是您需要在输出

#1