Search users for custom field

[nemoprincess]

Hello, I have added several custom fields to users' registration. Now I need to search my user and list them if they have a particular custom value for custom field.

How can I achieve it? I can recover the custom field from the wp_s2member_custom_fields meta_key?

Thanks a lot.

[Jason Caldwell]

Thanks for the great question.

Yea, you're running into a known design flaw within s2Member. s2Member was designed to store all Custom Registration/Profile Fields configured with s2Member, into a serialized array in the wp_usermeta table, under the meta_key wp_s2member_custom_fields.

While this DOES have some nice advantages, it unfortunately has many disadvantages too. In your case, you're seeing that it's difficult to search your MySQL database for specific information in these fields, because they're all globbed together into one serialized array, stored with key wp_s2member_custom_fields.

We are planning to address this issue in a future release of s2Member, in a way that preserves backward compatibility. Until then, if you need to search your MySQL database in this way, I suggest using the MySQL REGEXP operator, as seen below.

Code: Select all

SELECT `user_id` as `ID` FROM `wp_usermeta` WHERE `meta_key` = 'wp_s2member_custom_fields' AND `meta_value` REGEXP '.*"my_unique_field_id";s:[0-9]+:"string value I am looking for".*'

So if I wanted all User IDs with Profile Field country_code, with the value US, I might do this.

Code: Select all

SELECT `user_id` as `ID` FROM `wp_usermeta` WHERE `meta_key` = 'wp_s2member_custom_fields' AND `meta_value` REGEXP '.*"country_code";s:[0-9]+:"US".*'

Doing this "the WordPress way", it might look like this in your PHP code.

Code: Select all

<?php
global $wpdb;
$users = $wpdb->get_results ("SELECT `user_id` as `ID` FROM `" . $wpdb->usermeta . "` WHERE `meta_key` = '" . $wpdb->prefix . "s2member_custom_fields' AND `meta_value` REGEXP '.*\"country_code\";s:[0-9]+:\"US\".*'");
if (is_array ($users) && count ($users) > 0)
    {
        foreach ($users as $user)
            {
                $user = /* Get full User object now. */ new WP_User ($user->ID);
                print_r($user); /* Get a full list of properties when/if debugging. */
            }
    }
?>

---

Reference articles ( might assist you with this ).
http://stackoverflow.com/questions/4116 ... ized-array
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

[nemoprincess]

Thanks a lot Jason, I have managed to search my users for custom field and I would like to order them by their user level. I add to my query Order by I have no results at all.

This is my query where $professione and $paese come from the search form.
It works properly if I remove ORDER BY

Code: Select all

$query = "SELECT * FROM $wpdb->usermeta WHERE (`meta_key` = 'wp_s2member_custom_fields') AND (`meta_value` LIKE '%$professione%') AND (`meta_value` LIKE '%$paese%') ORDER BY wp_usermeta.wp_user_level DESC"


Thanks a lot

[Jason Caldwell]

Thanks for the follow-up.

Well, there is no wp_user_level column in the the wp_usermeta table,
so that's why your query is failing. Try it like this perhaps.

Code: Select all

$query = "SELECT * FROM `".$wpdb->usermeta."` WHERE (`meta_key` = '".$wpdb->prefix."s2member_custom_fields') AND (`meta_value` LIKE '%".esc_sql(like_escape($professione))."%') AND (`meta_value` LIKE '%".esc_sql(like_escape($paese))."%') ORDER BY `user_id` DESC"; 

[nemoprincess]

Hi Jason, thanks to you, you' re right. I took the code from somewhere and I tried it, maybe your code works but I would like to order them by their level I would like to see the premium users on the top of my user list.

[epixmedia]

Hi Jason, I found your code really useful for altering the sql to enable users to search - however I'm having trouble with simply formatting a list of all users. I want to display specific custom fields like "company" and omit things like password. I'm using the following code but it currently prints all user meta data:

Code: Select all

<?php
global $wpdb;
$users = $wpdb->get_results ("SELECT `user_id` as `ID` FROM `" . $wpdb->usermeta . "` WHERE `meta_key` = '" . $wpdb->prefix . "s2member_custom_fields'");
if (is_array ($users) && count ($users) > 0)
    {
        foreach ($users as $user)
            {
                $user = /* Get full User object now. */ new WP_User ($user->ID);
                print_r($user); /* Get a full list of properties when/if debugging. */
            }
    }
?>

Any help would be appreciated, I have 2 websites to put together and I'd like to use s2member pro on both.

Cheers

[Jason Caldwell]

Thanks for the follow-up.

It sounds like this API Function is what you're looking for: get_user_field().
viewtopic.php?f=40&t=12453&src_doc_v=111105#src_doc_get_user_field%28%29

See also, WordPress function get_users()
http://codex.wordpress.org/Function_Reference/get_users

Perhaps something like this in your code:

Code: Select all

<?php
foreach (get_users () as $user)
    {
        $user = /* Get full WP_User object instance. */ new WP_User ($user->ID);
        if /* If they're at least a "Subscriber", possibly a "Member". */ ($user->has_cap ("access_s2member_level0"))
            {
                echo get_user_field ("my_unique_field_id", $user->ID);
            }
    }
?>

[epixmedia]

Hi Jason, thanks again for the help - that's worked an absolute treat! Are you able to direct me to a post/documentation that explains how to include paging? My member database is going to be about 1000 so I don't fancy loading it all on to one page!

Cheers

Zoe

[Jason Caldwell]

Thanks for the follow-up.

Well you can use the offset argument to the get_users() function for this.
See: http://codex.wordpress.org/Function_Reference/get_users

As for the implementation of a paging system, that's something your theme and/or custom code will need to do ( i.e. via your custom coding ). You might try a search on Google for "pagination php class" for some ideas on how to accomplish this, or for an existing class that's already been designed for this.



You may also find it helpful (in some cases) to use a custom MySQL query of your own, instead of the get_users() function, where it is possible to use SQL_CALC_FOUND_ROWS in MySQL, to calculate the total number of results, thereby making your pagination system more powerful.

For example.

Code: Select all

<?php
function users_in_database ()
    {
        global $wpdb; /* Global database object reference. */
        /**/
        $q1 = mysql_query ("SELECT SQL_CALC_FOUND_ROWS `" . $wpdb->users . "`.`ID` FROM `" . $wpdb->users . "`, `" . $wpdb->usermeta . "` WHERE `" . $wpdb->users . "`.`ID` = `" . $wpdb->usermeta . "`.`user_id` AND `" . $wpdb->usermeta . "`.`meta_key` = '" . esc_sql ($wpdb->prefix . "capabilities") . "' LIMIT 1", $wpdb->dbh);
        $q2 = mysql_query ("SELECT FOUND_ROWS()", $wpdb->dbh);
        /**/
        $users = (int)mysql_result ($q2, 0);
        /**/
        mysql_free_result ($q2);
        mysql_free_result ($q1);
        /**/
        return $users;
    }
?>