PHP searches form data in different languages
I have a form in three different languages and store the information in one database. And I have a search page that searches for data from a database by selecting dropdown menus. But when the user searches, they only get results in the language they are using. I need to show results in all languages of users' choice. How can I achieve this goal? Any idea and help is appreciated.
More from comments:
This is the site where trainers and students are looking for Trainers. When the trainer registers, he fills out the form and also selects, for example, the Sector drop-down menu. And the site has 3 versions in different languages. And when a student is looking for a trainer, he selects a sector, and I want to show all the results related to the selected sector, regardless of the user and user registered in the language.
I have a data table as follows
// Table : data
// --------------------------------------------
// username | name | sector_id | lang
// --------------------------------------------
// jack | Jack | 1 | en
// --------------------------------------------
// smith | Smith | 1 | fr
// --------------------------------------------
and sector table as shown below
// Table : sector
// --------------------------------------------
// sector_id | sector_eng| sector_sp | sector_fr
// --------------------------------------------
// 1 | Finance | Financiar | la finance
// --------------------------------------------
For example, when a user searches for the finance sector, I need to get all the data for both jack and blacksmith, but show la finance for Smith and finance for Jack as a result
source to share
Suppose you support 3 languages (English, Spanish, French) and you can give an example of the financial sector. Whatever data you store for the financial sector, make 3 versions of that data for the respective languages and store it in your database.
But, when your user searches for any data - search, only in English. Then, when you get the result (English version), you will get all similar data from other versions (here it will also be Spanish, French). Then, according to user preference, show that the relevant version data.
Hope this helps you.
Here's an example of a table structure according to the use cases you shared in your question. it may differ depending on the complexity of your application, which I am not aware of. But hopefully this will 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
// ---------------------------------
source to share
You can work with a parent child structure which will allow you to work with multiple languages.
eg Languages are defined on all systems as harness 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 that will be null if the record is a parent and will have a parent id if the current record is a child.
Your select statement will look like this
select * from sector where language="en" and (id=$id or parent_id=$id );
link
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
source to share