0

I'm somewhat new to mysql/php and I'm not sure what the best way is to go about this problem.

I basically have a table of english words and another table of translations.

|ID | Word   |      | fkID | Language | Translation | 
| 0 | Dog    |      |  0   | Spanish  | perro       |
| 1 | Cat    |      |  0   | French   | chien       |
                    |  1   | German   | Katze       |
                    |  1   | Spanish  | gato        |`

On my php site I want to list out each english word in a table with all translations next to it.

| Word | Spanish | French | German |
| Dog  | perro   | chien  |        |
| Cat  | gato    |        | Katze  |

I can brute force this by joining the two tables and then looping through the result in php to find all translations for a given word and then making an array for each word that is aligned to the languages, but it seems like a very very inefficient way to do this.

Is there a better way?

(Is it possible to have mysql return a result that looks just like my desired output where the column names are the languages. I know I could just have a column for each language, but that could end up being a hundred columns that aren't used.)

WunderKlaus
  • 11
  • 1
  • There's nothing overtly wrong with your structure, other than you should normalize language into it's own table and store keys in the many-to-many table. An outer join to that table will guarantee that you get a row even where there are no translations. With that said, how wedded are you to mysql, because mongodb or possibly even redis would be able to handle this app quite easily and provide some simplification. If your matrix requires you to display every different language, i don't see how you get around the handling of that in HTML, so the actual code doesn't matter. – gview Aug 19 '15 at 05:29
  • It's best to handle issues of data display in the presentation layer, so something like your 'brute force' idea is the right way to go (although you only need one join); faster, more scalable, and more flexible. Note that 0 is not normally used as an id. It may be the 'first' item in a php array, but that's internal to php, and nothing to do with the id value – Strawberry Aug 19 '15 at 06:17

1 Answers1

0

Actually you are on the right track. What you're looking for is called table pivoting, but unfortunately there is no PIVOT in MySQL. Of course, there is a workaround, but keep in mind that the right tool for the job is to use your scriptting language to create the structure you want.

Community
  • 1
  • 1
Kristian Vitozev
  • 5,791
  • 6
  • 36
  • 56