Filter SQL Query contain Array

I have a query, to display Profile, one of the column contain Array of languages fluent in.

profile gender country               nationality fluent_in
    318 Male   United Arab Emirates  Lebanese    a:2:{i:0;s:6:"Arabic";i:1;s:7:"Bengali";}
    366 Female Lebanon               Lebanese    a:3:{i:0;s:6:"Arabic";i:1;s:7:"English";i:2;s:6:"French";}
    368 Male   Jordan                Australian  a:2:{i:0;s:7:"English";}
   2924 Male   United Arab Emirates  Lebanese    a:2:{i:0;s:6:"Arabic";i:1;s:7:"English";}

My question, what i should add to the Query, to Filter query “English”, AND “Arabic” ?

My SQL Query:

SELECT p2p_to as profile, pm1.meta_value as gender, pm2.meta_value as country, pm3.meta_value as nationality , pm4.meta_value as fluent_in
FROM `wp_p2p`
LEFT JOIN wp_postmeta AS pm1 ON (wp_p2p.p2p_to = pm1.post_id AND pm1.meta_key='gender')
LEFT JOIN wp_postmeta AS pm2 ON (wp_p2p.p2p_to = pm2.post_id AND pm2.meta_key='country')
LEFT JOIN wp_postmeta AS pm3 ON (wp_p2p.p2p_to = pm3.post_id AND pm3.meta_key='nationality')
LEFT JOIN wp_postmeta AS pm4 ON (wp_p2p.p2p_to = pm4.post_id AND pm4.meta_key='fluent_in')
WHERE `p2p_type` LIKE 'service_learn'

Answer

Try:

pm4.meta_value LIKE "%Arabic%" AND pm4.meta_value LIKE "%English%"

You can put it in the WHERE clause or in the JOIN ON. It’s better in the JOIN part.

Advertisement

Answer