Posted 2 years ago #
Hello. In searching we've read that WP used to have the first name and last name as part of the searched fields, but that now they're not.
We are definitely unable to pull up a result when the search criteria matches one of these name fields.
We very much need to have these name fields included in the search properties.
Can anyone help us know how to get this change made, preferably in a way that would survive an upgrade? Thanks in advance for your help!
Posted 2 years ago #
Since first_name and last_name are in the usermeta table instead of the users table, that complicates any proposed Users Search code enough that I am currently unable to figure it out. Someone smarter than me has surely either already done it or knows how. If that is you, would you help us out?
Several people on the forum have already indicated they need this functionality. It would be deeply appreciated. Thank you!
Posted 2 years ago #
I am using the s2Member plugin, and it has a file called "users-list.inc.php", which (among its other duties) affects the normal Users Search.
I was able to tweak it to search first_name and last_name in the usermeta table.
Now my "Users Search" (users.php) is almost exactly what I need it to be.
The last thing I must tackle is getting the Users Search to be sorted (ordered) by usermeta.last_name
Anyone know any way to get that done? :-)
Posted 2 years ago #
What I'm currently muddling around with... (as of 2/16/11)
A ministerial organization I'm with needed its ministers directory online. We also needed to make an easy way for the members to pay their membership dues online (a way that was also affordable for the organization).
We turned to the s2Member plugin to make WP a little better at 'membership' type stuff. That killed two birds because it made it easy for online dues payment (through PayPal) and it made it a piece of cake to enhance the registration process with the extra sign-up fields we needed, and to store, retrieve, and edit those fields as needed.
The s2Member plugin is free, but we paid for the Pro "add on" for two reasons: (1) Of all the features that Pro adds, one is an import/export, and we had a lot of ministers names and info to upload as "new" accounts in our new system. (2) I hoped that paying a chunk of cash would make the owners of s2Member be a little bit kind if I needed a hand. I was apparently wrong on the latter reason.
We got one out of two. The import feature works (but we have hit a slight glitch). The general process had a few issues that hindered us somewhat, which led us to contact PrimoThemes (makers of s2Member plugin) for a question here and there. Our emails were all ignored, as of this writing. We /eventually/ solved all issues except the import glitch. We have no clue on it, and the plugin's makers have not written us back. Sigh. :-\
Anyhow, their code is killer. Wish their support was better (or even not non-existent), but the plugin and add-on are awesome stuff.
The final area we needed to tackle was creating an interface for the ministers to access the online directory. The s2Member plugin has a built in interface that will let our ministers update their own record. But we needed a highly tweaked version of the wp-admin "Users Search" (users.php) to "stand in" as our directory page.
This is where the killer code in s2Member again bailed us out. One of the s2Member include files (named "users-list.inc.php") makes all the custom fields (that we discussed above) viewable on "users.php" -- and not just viewable, but searchable too!
Our only lack was that we needed the usermeta table's "first_name" and "last_name" fields searchable as well. Below is one of their functions (from that page) where I only had to add two lines of code to make the "first_name" and "last_name" fields searchable.
One little bit I need help with here: The function lets me tweak the search query's "WHERE" criteria, but I also need to tweak the search query's "ORDER BY" settings too. That does not seem to be present in this function, and I cannot find it in WP code.
I need the user list to be sorted by the usermeta table's "last_name" field. Anyone know how to do that?
/*
Function that modifies the search query.
Affects searches performed in the list of Users.
Attach to: add_action("pre_user_search");
*/
function ws_plugin__s2member_users_list_search (&$search = FALSE)
{
global $wpdb; /* Need this global object reference. */
/**/
eval ('foreach(array_keys(get_defined_vars())as$__v)$__refs[$__v]=&$$__v;');
do_action ("ws_plugin__s2member_before_users_list_search", get_defined_vars ());
unset ($__refs, $__v); /* Unset defined __refs, __v. */
/**/
if ($search->search_term && ($s = "%" . esc_sql (like_escape ($search->search_term)) . "%")) /* Only when executing an actual search query. */
{
$search->query_from = " FROM <code>" . $wpdb->users . "</code> INNER JOIN <code>" . $wpdb->usermeta . "</code> ON <code>" . $wpdb->users . "</code>.<code>ID</code> = <code>" . $wpdb->usermeta . "</code>.<code>user_id</code>";
/**/
$search->query_where = " WHERE '1' = '1' AND (" . apply_filters ("ws_plugin__s2member_before_users_list_search_where_or_before", "", get_defined_vars ());
$search->query_where .= " (<code>" . $wpdb->usermeta . "</code>.<code>meta_key</code> = '" . $wpdb->prefix . "s2member_subscr_id' AND <code>" . $wpdb->usermeta . "</code>.<code>meta_value</code> LIKE '" . $s . "')";
// 2 lines below added to make last_name and first_name be searchable in Users Search:
$search->query_where .= " OR (<code>" . $wpdb->usermeta . "</code>.<code>meta_key</code> = 'first_name' AND <code>" . $wpdb->usermeta . "</code>.<code>meta_value</code> LIKE '" . $s . "')";
$search->query_where .= " OR (<code>" . $wpdb->usermeta . "</code>.<code>meta_key</code> = 'last_name' AND <code>" . $wpdb->usermeta . "</code>.<code>meta_value</code> LIKE '" . $s . "')";
$search->query_where .= " OR (<code>" . $wpdb->usermeta . "</code>.<code>meta_key</code> = '" . $wpdb->prefix . "s2member_custom' AND <code>" . $wpdb->usermeta . "</code>.<code>meta_value</code> LIKE '" . $s . "')";
$search->query_where .= " OR (<code>" . $wpdb->usermeta . "</code>.<code>meta_key</code> = '" . $wpdb->prefix . "s2member_custom_fields' AND <code>" . $wpdb->usermeta . "</code>.<code>meta_value</code> LIKE '" . $s . "')";
$search->query_where .= " OR <code>user_login</code> LIKE '" . $s . "' OR <code>user_nicename</code> LIKE '" . $s . "' OR <code>user_email</code> LIKE '" . $s . "' OR <code>user_url</code> LIKE '" . $s . "' OR <code>display_name</code> LIKE '" . $s . "'";
$search->query_where .= apply_filters ("ws_plugin__s2member_before_users_list_search_where_or_after", "", get_defined_vars ()) . ")"; /* Leaving room for additional searches here. */
$search->query_where .= " AND <code>" . $wpdb->users . "</code>.<code>ID</code> IN(SELECT DISTINCT(<code>user_id</code>) FROM <code>" . $wpdb->usermeta . "</code> WHERE <code>meta_key</code> = '" . $wpdb->prefix . "capabilities'" ./**/
(($search->role) ? " AND <code>meta_value</code> LIKE '%" . esc_sql (like_escape ($search->role)) . "%'" : "") . ")";
/**/
$search->query_from = apply_filters ("ws_plugin__s2member_before_users_list_search_from", $search->query_from, get_defined_vars ());
$search->query_where = apply_filters ("ws_plugin__s2member_before_users_list_search_where", $search->query_where, get_defined_vars ());
}
/**/
eval ('foreach(array_keys(get_defined_vars())as$__v)$__refs[$__v]=&$$__v;');
do_action ("ws_plugin__s2member_after_users_list_search", get_defined_vars ());
unset ($__refs, $__v); /* Unset defined __refs, __v. */
/**/
return;
}
Posted 2 years ago #
Update:
I ran a search on this site for "pre_user_search" and found this:
http://core.trac.wordpress.org/attachment/ticket/10781/sort-users.php
Which make a reference to:
$obj->query_orderby
I think that may just correspond with the similar code above:
$search->query_where
In other words, leading me to try
$search->query_orderby
... I will report back how it goes.
Posted 2 years ago #
The challenge is that the sort criteria is not a field, but a certain set of values in a row in a field.
I think the secret to solving this may be to query for those values and name that part of the query with an alias by which I can refer to it for the sort order.
I am not an expert on MySql querying. Is there a SQL guru out there that can help me?
Posted 2 years ago #
I searched for a while, then eventually gave up on trying to sort by a row in a meta_value column. I just punted. I'm manually editing the usernames to be in a "lastname-firstname" format.
This topic has been closed to new replies.
