Creating one PHP array from two sql tables

Question

I am not the best at writing complicated (for me) mysqli queries.

I am trying to create one array using data from two different tables.

TABLE 1: text_strings

id    string
1     hello
2     good_morning

TABLE 2: text_translations

id    textid    lang    translation
1     1         en      Hello
2     1         es      Hola
3     2         en      Good Morning
4     2         es      Buenos Dias

So, I am trying to make an array of translations based on the language. If my language is set to Spanish (es), the array would be like:

['hello'=>'Hola','good_morning'=>'Buenos Dias']

So, the structure of the array would be:

$lang['string'] = ['translation'];

In written English, if language is "es", select all from "text_translations" where lang is "es" and make the array key equal the value of the textid string value in the "text_strings" table.

I'm assuming something with LEFT JOIN, but I really don't know how to set up this query.

Answer 1

Thats pretty simple JOIN. Fetch this query from db to array.

SELECT t.translation, s.string
FROM text_strings AS s
JOIN text_translations AS t ON s.id = t.textid AND lang = 'es'

Answer 2

Sounds like your question is more "how to join two tables". I advise you figure that out before worrying about how to handle them in php. W3 schools has a really simple and straightforward rundown of sql. The section on joins can be found here.

Answer 3

Below query will get you two columns associated with each other, string and translation, and over PHP you can fetch it, and create an array as required, (string in place of key and translation in place of value).

I had used INNER JOIN cause, both the string and translation should be there, in case if any one of them not found. That will be excluded.

SELECT TextString.`string`, TextTranslation.`translation` FROM text_strings TextString INNER JOIN text_translations TextTranslation 
ON TextString.id = TextTranslation.text_id
WHERE TextTranslation.lang = 'en'

and over PHP, you can do it like:

$translations = array();
while ($strings = $query->fetch_array(MYSQLI_ASSOC)) {
  $translations[$strings['string']] = $strings['translation'];
}