Search users using meta key and meta values stored as comma separated in wordpress

0
votes

While creating the user in wordpress there is a list of checkboxes and i am storing id values of the checked boxes as comma separated in user meta table like 1,5,6,8,10. etc..

| user_id |  meta_key   | meta_value |
---------------------------------
|       1 |   service   |  1,4,5,10  |
|       2 |   service   |  4,5,6     |

Now i am working on search functionality along with first name and city where i have to get the list of users.

So there is the same list of checkboxes which is in when creating user. here i have to get the checked values and search it in the meta values in usermeta table which is stored as a comma separated and get the matching users.

example: meta key is Profession and value stored is say for user_1 1,4,5,10 and user_2 is 4,5,6.

Now if from the search 1,4 value is checked then it should return the user. in this case it should return user1 and user2.

If i search for 1,10 is checked it will return only user1.

Currently i am executing custom query to get the user.

Please help me to match value to comma separated meta values to get the users.

Thank you.

2
phpmysqlregexwordpress

2 Answers

1
votes

You could use FIND_IN_SET so:

SELECT * FROM your_table 
WHERE FIND_IN_SET(1, meta_value) AND FIND_IN_SET(4, meta_value);
0
votes

FIND_IN_SET can only search for one string at a time.

You can use this query to find users 

SELECT
  wp_users.*
FROM wp_users
  JOIN wp_usermeta
    ON wp_users.id = wp_usermeta.user_id
      AND wp_usermeta.meta_key = 'service'

     WHERE CONCAT(",", wp_usermeta.meta_value, ",") REGEXP ",(1|4),"