1

I just wanted to know if there is an easy way when I have following entries:

++++++++++++++++++
+ id + languages +
++++++++++++++++++
+ 1  + DE        +
++++++++++++++++++
+ 2  + DE,EN     +
++++++++++++++++++
+ 3  + FR        +
++++++++++++++++++

and the value of the parameter in my procedure is 'DE,EN,FR' that he finds all above entries.

I googled and googled and came to the solution that I have to iterate over all values in the parameter and split them with a SUBSTRING and make a

FIND_IN_SET(splittedParam, `languages`)

for them.

Is there an easier (with shorter code) way?

cherry-wave
  • 731
  • 2
  • 6
  • 21
  • 1
    I think you're going to find that your data structure is bad for this sort of thing. You shouldn't have single values AND lists in one column. – durbnpoisn Jan 15 '16 at 17:35
  • 1
    Yes, it is called **normalization**, but you need to change your schema. – Lukasz Szozda Jan 15 '16 at 17:35
  • You should see this; http://stackoverflow.com/questions/3070384/how-to-store-a-list-in-a-column-of-a-database-table/3070426 as a way to understand why this structure is bad. – David Manheim Jan 15 '16 at 17:40
  • Why is that so? It is a column where you can save one or more values of a predefined SET: ´languages´ set('DE','EN','FR') I don't know why phpMyAdmin offers this column-type then. – cherry-wave Jan 15 '16 at 17:42
  • 1
    So you say I should use a table LANGUAGES that has a one to many relationship to my ENTRIES table rather than a SET-column? – cherry-wave Jan 15 '16 at 17:46
  • Yes; this is called normalization, and you should definitely read a bit more about database structuring if you're building one. The link above is a decent start, but it's a fairly complex area. Another useful link, if you don't know about indexing, is here: http://stackoverflow.com/questions/11299217/how-can-i-optimize-this-sql-query-using-indexes/11299218#11299218 – David Manheim Jan 15 '16 at 18:02

1 Answers1

1

To clarify how you should normalize this, I would suggest altering your table as follows;

ID | Langauge
-------------
1  | DE
2  | EN
2  | DE
3  | FR
David Manheim
  • 2,553
  • 2
  • 27
  • 42
  • Yeah thanks I guessed that^^ Answers were really helpfull. I am just wondering why phpMyAdmin offers a SET-type for columns then – cherry-wave Jan 15 '16 at 17:53