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
// ---------------------------------

Answer 2

You can work with parent-child structure which will allow you to work with multiple languages.

for example Languages are defined in all systems as tow char (ar,en,de,fr..etc).

So your table will be like this

sector table

id - sector_name       - parent_id - language
1  - Sector in English - null -       en 
2  - Sector in French  - 1    -       fr 
3  - Sector in Spain   - 1    -       sp

• parent_id is a foreign key which will be null when the record is the parent and will have the parent id when the current record is child .

Your select statement will be like this

select * from sector where language="en" and (id=$id or parent_id=$id );

reference

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

How to create a MySQL hierarchical recursive query