How to look for words before comma (,) in a string

Question

I 'm currently working on a language project with someone else, and I 'm using a database for words in a language, but we also have translations and the best way to do it is by including the words in one column. So right now we have (language) (English) (German) (Dutch). The problem is that some words can be translated by multiple words, so for English, you get a translation like:

good, healthy

My question is, I want to avoid having to make a new (English2) column for more different translations of one word in one language, and instead of that putting them all at one page, but how can I make sure that if people look for something, I can also let the code distinguish between the words before and after the comma? So that if you look for 'healthy', you can find the main word, and not only if you type 'good, healthy', what no one will do. I have some knowledge of PHP, but working with strings is quite difficult for me and I still don't get how to do this.

Possible duplicate: http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string — Aziz Saleh, Mar 28 '14 at 01:07

score 0 · Answer 1 · answered Mar 28 '14 at 01:14

0

I don't know where you are storing this data, but I will assume a MySQL database or something you have similar control over.

You really should simply use two tables, one to store the words and an ID associated with them and another table to store the translations for those words.

words
   ID     INT
   Word   VARCHAR(32)

translations
  ID            INT
  Lang          ENUM('ENGLISH', 'GERMAN', 'FRENCH')
  Translation   VARCHAR(32)

In PHP you would make a query like this:

SELECT `Word`, `Translation`
FROM `words`
LEFT JOIN `translations` ON (`translations`.`ID` = `words`.`ID` AND `Lang`='FRENCH')
WHERE `Word` = 'Funky'

This query would return the word and a translation if available, or NULL if no translation was available.

answered Mar 28 '14 at 01:14

Kristopher Ives

5,838
7
42
67

You see, I know how databases work at a basic level, but the solution provided by another user is way more comprehensible for me than this. I don't get how I could insert multiple English translations and 1 French translation in 1 column, and letting MySQL still recognize them, how is that possible? Doesn't this solution only work when there is 1 translation of the word for each language? – Ubiz Mar 28 '14 at 16:25
This is not possible by the way. We want to add the possibility to our site for members to translate the words in their own languages, what is needed for that are extra columns for their languages, that's a better solution in my opinion than 1 column in which they constantly have to edit present data. Not only that, they could change existing data, so this is very unpractical for our idea. – Ubiz Mar 28 '14 at 16:30
This solution lets you use multiple translations but the query would change. You should not be wasting your time joining or separating by comma, but instead using the database library to properly query and iterate that query. – Kristopher Ives Mar 29 '14 at 01:55
The problem is that I don't understand your solution, I have a basic knowledge of using databases, joining has always been a problem for me, I still don't understand what it exactly is. – Ubiz Mar 30 '14 at 16:39
I have already included 273 words with the FIND_IN_SET method and I wonder why I shouldn't use it because in our specific case this is perfect for what we want to do. – Ubiz Mar 30 '14 at 17:04
It's up to you if you have more in your database you will have problem probably around 100,000 or so and that's not out of scope for the concept of a words database. Also each place you do a query you will have to do more and more tricks, creating bugs. – Kristopher Ives Mar 31 '14 at 10:12
Ok, but your method is incomprehensible. – Ubiz Apr 01 '14 at 14:59
You really should learn about basic data design before saying such a thing lol – Kristopher Ives Apr 01 '14 at 18:20

score 0 · Answer 2 · answered Mar 28 '14 at 01:21

Wouldn't it be nice to have a DB structure like this:

  WORD          ENGLISH               GERMAN                  DUTCH
-----------------------------------------------------------------------------
well            good           <any Dutch/German translation or can be null>
well            healthy        <any Dutch/German translation or can be null>

Then when you want to query the translations for well then you could just

SELECT word, GROUP_CONCAT(english) as "English Translations" FROM myTable WHERE word='well'

Then it will result to :

  word                  English Translations
----------------------------------------------
well                    good,healthy

This sounds good, but when people look for the word instead of their translation, what will the result be, and can it result both? — Ubiz, Mar 28 '14 at 16:30

score 0 · Answer 3 · answered Mar 28 '14 at 17:20

0

I have found the solution! Thanks to Aziz I could find the function FIND_IN_SET in his referred question, in that way I solved the problem by letting MySQL look for values seperated by commas.

answered Mar 28 '14 at 17:20

Ubiz

91
10

How to look for words before comma (,) in a string

3 Answers3