PHP search for form data in different languages

Question

I have a form in three different languages and store information on a single database. And I have search page that searches for data from database by selecting dropdown menus. But when user searches he gets results only in language he is using. I need to show results from all languages according the users selection. How can I achieve this goal? Any idea and help is appreciated.

More details from comments:

It is a site that trainers register and students search for Trainers. When trainer registers he fills the form and also selects for example the Sector from dropdown menu. And site has 3 version in different languages. And when student searches for a trainer he selects a Sector and I want to show all results related to the selected sector regardless of the language Trainer registered and the user uses.

I have a data table as following

// Table : data
// --------------------------------------------
// username     | name     | sector_id  | lang
// --------------------------------------------
//   jack       |  Jack    | 1      | en
// --------------------------------------------
//   smith      |  Smith   | 1      | fr
// --------------------------------------------

and Sector table as following

// Table : sector
// --------------------------------------------
// sector_id | sector_eng| sector_sp    | sector_fr
// --------------------------------------------
//   1       |  Finance  | Financiar    | la finance
// --------------------------------------------

For example, when user searches for sector Finance I need to get all data both for jack and smith but show la finance for Smith and finance for Jack in result

Answer 1

Suppose you are supporting 3 languages(English, Spanish, French) & let's take the example of Finance Sector. Whatever data you are storing for finance sector make 3 version of those data for respective languages & store in your DB.

But, when your user will search on any data - search that on the ENGLISH version of data only. Then, after getting the result(English version) fetch all similar data of other different versions(here it will be Spanish, French) also. Then, according to user's preference show that respective version data.

Hope it'll help you.

Here is a sample table structure according to the use cases which you've shared in your question. it could be different according to your application complexity which I don't know. But, hope it'll give you some basic understanding to move forward.

// Table : sector
// ---------------------------------
// sector_id    | sector_name_eng
// ---------------------------------
//  1           | Finance
// ---------------------------------
//  2           | Statistics
// ---------------------------------
//  3           | Biology
// ---------------------------------


// Table : lang_reference
// ------------------------------
// lang_id      | lang_name
// ------------------------------
//  1           | english
// ------------------------------
//  2           | spanish
// ------------------------------
//  3           | french
//  -----------------------------


// Table : sector_lang_details
// --------------------------------------------
// sector_id    | lang_id   | sector_name
// --------------------------------------------
//   1          |  1        | Finance
// --------------------------------------------
//   1          |  2        | Financiar
// --------------------------------------------
//   1          |  3        | Finance_in_french
// --------------------------------------------
//   2          |  1        | Statistics
// --------------------------------------------
//   2          |  2        | Statistics_in_spanish
// --------------------------------------------
//   2          |  3        | Statistics_in_french
// --------------------------------------------
//   3          |  1        | Biology
// --------------------------------------------
//   3          |  2        | Biology_in_spanish
// --------------------------------------------
//   3          |  3        | Biology_in_french
// ---------------------------------------------------



// Table : trainer_details
// --------------------------------
// trainer_id   | trainer_name
// ---------------------------------
//  1           | Tariner A
// ---------------------------------
//  2           | Tariner B
// ---------------------------------
//  3           | Tariner C
// ---------------------------------


// Table : trainer_teches_sectors
// ---------------------------------
// trainer_id   |   sector_id
// ---------------------------------
//  1           |   1
// ---------------------------------
//  1           |   2
// ---------------------------------
//  2           |   1
// ---------------------------------
//  3           |   3
// ---------------------------------