PriMoThemes — now s2Member® (official notice)

This is now a very OLD forum system. It's in READ-ONLY mode.
All community interaction now occurs at WordPress.org. See: new forums @ WordPress.org

Search users for custom field

s2Member Plugin. A Membership plugin for WordPress®.

Search users for custom field

Unread postby nemoprincess » October 25th, 2011, 3:58 pm

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.
User avatar
nemoprincess
Registered User
Registered User
 
Posts: 24
Joined: October 5, 2011
Location: London, UK

Re: Search users for custom field

Unread postby Jason Caldwell » October 26th, 2011, 2:08 pm

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
~ Jason Caldwell / Lead Developer
& Zeitgeist Movie Advocate: http://www.zeitgeistmovie.com/

Is the s2Member plugin working for you? Please rate s2Member at WordPress.org.
You'll need a WordPress.org account ( comes in handy ). Then rate s2Member here Image
.
User avatar
Jason Caldwell
Lead Developer
Lead Developer
 
Posts: 4045
Joined: May 3, 2010
Location: Georgia / USA

Re: Search users for custom field

Unread postby nemoprincess » November 9th, 2011, 3:50 pm

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
User avatar
nemoprincess
Registered User
Registered User
 
Posts: 24
Joined: October 5, 2011
Location: London, UK

Re: Search users for custom field

Unread postby Jason Caldwell » November 9th, 2011, 5:18 pm

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"; 
~ Jason Caldwell / Lead Developer
& Zeitgeist Movie Advocate: http://www.zeitgeistmovie.com/

Is the s2Member plugin working for you? Please rate s2Member at WordPress.org.
You'll need a WordPress.org account ( comes in handy ). Then rate s2Member here Image
.
User avatar
Jason Caldwell
Lead Developer
Lead Developer
 
Posts: 4045
Joined: May 3, 2010
Location: Georgia / USA

Re: Search users for custom field

Unread postby nemoprincess » November 9th, 2011, 7:19 pm

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.
User avatar
nemoprincess
Registered User
Registered User
 
Posts: 24
Joined: October 5, 2011
Location: London, UK

Re: Search users for custom field

Unread postby epixmedia » November 22nd, 2011, 7:29 am

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 :)
User avatar
epixmedia
Registered User
Registered User
 
Posts: 59
Joined: November 15, 2011

Re: Search users for custom field

Unread postby Jason Caldwell » November 22nd, 2011, 12:35 pm

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);
            }
    }
?>
~ Jason Caldwell / Lead Developer
& Zeitgeist Movie Advocate: http://www.zeitgeistmovie.com/

Is the s2Member plugin working for you? Please rate s2Member at WordPress.org.
You'll need a WordPress.org account ( comes in handy ). Then rate s2Member here Image
.
User avatar
Jason Caldwell
Lead Developer
Lead Developer
 
Posts: 4045
Joined: May 3, 2010
Location: Georgia / USA

Re: Search users for custom field

Unread postby epixmedia » November 23rd, 2011, 12:09 pm

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
Last edited by epixmedia on December 5th, 2011, 9:52 am, edited 1 time in total.
User avatar
epixmedia
Registered User
Registered User
 
Posts: 59
Joined: November 15, 2011

Re: Search users for custom field

Unread postby Jason Caldwell » November 23rd, 2011, 1:28 pm

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;
    }
?>

~ Jason Caldwell / Lead Developer
& Zeitgeist Movie Advocate: http://www.zeitgeistmovie.com/

Is the s2Member plugin working for you? Please rate s2Member at WordPress.org.
You'll need a WordPress.org account ( comes in handy ). Then rate s2Member here Image
.
User avatar
Jason Caldwell
Lead Developer
Lead Developer
 
Posts: 4045
Joined: May 3, 2010
Location: Georgia / USA


Return to s2Member Plugin

Who is online

Users browsing this forum: Alexa [Bot], Google [Bot], Yahoo [Bot] and 3 guests