Mysql seletc query using in if row has komma seperated values

Question

I'm using a typo3 db. Users are stored in a table where the usergroups are inserted into a comma-separated row (usergroup = 1,5,88).

Now, I try to select a group for example by checking if a group exists in each user's dataset.

  select from ...... where ... usergroup IN (1,6,9) ...

This works well, if there is only one value in the user's data (usergroup=9) If the user has more than one usergroup (usergroup=9,11,5,3) the query above won't work.

I know this is not really normalized, bu I have no possibility to change this, so I'm searching for a way to use a selection query which can handle comma-separated values.

Is there any possibility?

you can find your answer here : http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql
hope that helps :) — Halayem Anis, Oct 26 '14 at 11:41

score 0 · Answer 1 · answered Oct 26 '14 at 11:41

0

Use

where find_in_set(usergroup, 1) > 0
   or find_in_set(usergroup, 6) > 0
   or find_in_set(usergroup, 9) > 0

answered Oct 26 '14 at 11:41

juergen d

195,137
36
275
343

score 0 · Answer 2 · answered Oct 27 '14 at 06:48

TYPO3 actually has an api-method for looking into a field with relations stored as comma separated values. If TYPO6 6.0+ you can find this method here \TYPO3\CMS\Core\Database\DatabaseConnection->listQuery.

This methods unfortunately only takes one uid to search for, so you would have to loop over all group uid's you want to search for (in your example 1,6 and 9).

For newer TYPO3 versions, this method is actually just a wrapper for the sql function FIND_IN_SET. Older versions used a another LIKE-based approach.

Mysql seletc query using in if row has komma seperated values

2 Answers2