0 
SELECT user_id FROM $wpdb->usermeta WHERE meta_key= .$wpdb->prefix. _capabilities AND meta_value= ROLE in serialized array.

How can I set meta_value in serialized array?

Many thanks in advance :)

Improve this question
7
  • 1
    This can be done via WP CLI without needing to write out a big SQL query, and it can be done pretty easily with WP_User_Query in PHP
    – Tom J Nowell ♦
    Commented Mar 20, 2020 at 15:59
  • @Tom J nowell Thans for your reply! Isn't it better to use query directly rather than use WP_User_Query regarding DB performance?
    – hiyo
    Commented Mar 20, 2020 at 23:49
  • It doesn't make any difference if the query comes from PHP or if it comes from an SQL client, chances are you're using PHPMyAdmin anyway. Just note that if you need to query for something that's inside a serialised PHP string, you're in for a world of pain, for which there isn't a 100% foolproof solution. The recommended solution for querying serialised PHP data directly is don't, there are other ways to get the data
    – Tom J Nowell ♦
    Commented Mar 21, 2020 at 22:19
  • I see @TomJNowell Thanks for your help. It helps a lot :) So is it better to store plain value instead of using serialized array and take out with SQL query? I had found this thread: stackoverflow.com/questions/4116419/…
    – hiyo
    Commented Mar 22, 2020 at 1:50
  • Yes, especially where user meta is concerned, you can have more than one user meta with the same key as well. But if you really must store a structured value as a string, don't use PHP serialisation either, use JSON. There is a Q on this stack that asks how to do it, which got a lot of answers and upvotes, including one ultra sketchy way to do it that sometimes works, but it also has a lot of examples of better ways to deal with it
    – Tom J Nowell ♦
    Commented Mar 22, 2020 at 14:51

1 Answer 1

Reset to default
0

I've decided to use plain Key according to @TomJNowell 's comments. If I would like to search a key in serialized data, I would get those serialized array from DB and use maybe_unserialize() in PHP OR use WP_User_Query in PHP. Thank you @TomJNowell

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.