php mysql compare checboxes returned values with field values

Question

I have a form which sends selected values from a checkbox list to a php script this way:

$selected_languages=$_POST["selected_languages"]; // --> "en-GB,it,fr"

My 'accounts' mysql table has a field called 'spoken_languages' which contains the list of the code(s) of the languages spoken by each user. The field is a VARCHAR field and keeps the code(s) this way:

en-US,ru,fr

I'd like to select all the users who speak any of the selected languages in $selected_languages but I didn't find a efficient and quick way around to do something like that:

SELECT 
COUNT(accounts.user_id) as users_number, 
countries.country_name 
FROM accounts, countries 
WHERE accounts.country_code=countries.country_code

and the following:

AND $selected_languages IN accounts.spoken_languages 

then:

GROUP BY countries.code;

so that the recordset could give the number of users who speaks English or/and Italian or/and French group by country_code

The volume of the exiting dataset is very large. Do I create a separate join table users_languages? Is there a mysql instruction to do it? Do I create a user function in mySQL?

Thanks a lot. Eric

Answer 1

$langs = explode(',', $selected_languages);

foreach($langs as $lang) {
    $res[] = " FIND_IN_SET('". mysql_escape_string($lang) . "', accounts.spoken_languages)";
}

$query = 'SELECT 
    COUNT(accounts.user_id) as users_number, 
    countries.country_name 
    FROM accounts, countries 
    WHERE accounts.country_code=countries.country_code 
    AND (' . implode(' OR ', $res) . ') GROUP BY countries.code;';

 //execute $query

Note: I believe that it should be slow on big datasets.

Answer 2

The volume of the exiting dataset is very large. Do I create a separate join table users_languages?

Yes, this is the best option because MySQL can then use an index to find the rows fast.

Is there a mysql instruction to do it?

yes, find_in_set see @Andrej's answer.

Do I create a user function in mySQL?

No, it will kill any option MySQL has to use indexes.