Posted 6 months ago #
Hello there, I have build a search form to retrieve users with particular user meta values and I would like to order them by their user level.
$query = "SELECT * FROM $wpdb->usermeta WHERE (meta_key= 'wp_s2member_custom_fields') AND (meta_valueLIKE '%$professione%') AND (meta_valueLIKE '%$paese%')";
This is my query. How can I order them by their level?
Thanks a lot.
Hi,
WordPress has been moving away from user levels toward user roles and capabilities instead, so my first suggestion would be to use Roles instead of Levels.
Now, since the orderby parameter does not have an option for sorting by Role, what you could do is use multiple queries per user role (with wp_user_query), store each result in a separate variable, and then echo them in the desired order.
The only problem with this approach is server load.
Another approach would be to use only one query, loop through your users with a foreach statement, and use current_user_can or user_can in a multiple if statement to echo the results. I'm thinking something like this:
<?php
$query = "SELECT * FROM $wpdb->usermeta WHERE (meta_key= 'wp_s2member_custom_fields') AND (meta_valueLIKE '%$professione%') AND (meta_valueLIKE '%$paese%')";
foreach ($query as $user) {
if ( user_can($user->ID, 'administrator') {
echo 'admin bla...';
}
elseif ( user_can($user->ID, 'editor') {
echo 'editor bla...';
}
}
?>
Posted 6 months ago #
Hi Marventus, thanks a lot for you suggestion. I choose to have 2 queries, one to retrieve my premium user and the second one to retrieve just the free subscribers.
So this is the kind of query that I have:
$query2 = "SELECT * FROM $wpdb->usermeta WHERE (meta_key= 'wp_s2member_custom_fields') AND (meta_valueLIKE '%$professione%') AND (meta_valueLIKE '%$paese%') AND user_id IN (SELECT user_id FROM $wpdb->usermeta WHERE (meta_key = 'wp_capabilities') AND (meta_valueLIKE '%$livello%') ORDER BY $wpdb->usermeta.meta_value ASC )";
where $livello is the variable containing, at first premium level and then free subscriber.
But I just have the premium ones as results. So it just run the first query. Is there any function that can clear the query to the db?
If I remove the first query, then the second one is going to retrieve the free subscribers
Thanks a lot.
No problem.
You can reset the query with:
wp_reset_query();
Posted 6 months ago #
I have tried that one and then also:
$wpdb->flush();
but they don' t seem to work.
SQL is really not my forte, but maybe since you are storing everything in the same PHP value, perhaps the meta_value parameters of your query are getting overwritten in the process.
What if you use two different queries and store them inside two different php values (say, $query1 and $query2) with a wp_reset_query() between each one?
Also, it would help to know, if possible, what exactly you are trying to accomplish, because I might be able to come up with a workaround. Will you echo or display that list on your site or do you just need to retrieve it to use in additional functions?
Posted 6 months ago #
Hi Marvenuts. I have $query1 and $query2 and so
$premium_users = $wpdb->get_results( $query1 );
and
`$free_users = $wpdb->get_results( $query2 );
the only different thing in the query is this $livello that for $query1
is
$livello1
and for $query2 is
$livello2.
I' m trying to search and display my users ordering them by their membership level.
There are now 2 membership levels: subscriber (free_users) and s2member_level1 (premium users).
Oh, sorry about that. I thought you were retrieving both user roles at the same time conditionally.
What happens if you do this:
$query1 = SQL query for premium users;
$wpdb->flush();
$query2 = SQL query for free users;
$wpdb->flush();
Posted 6 months ago #
No problem :-)
in this casa it will display just premium users
What if you used wp_user_query for your DB queries? It has role, meta_key, and meta_value arguments, and should work well with wp_reset_query().
Posted 6 months ago #
Hi Marventus thanks for your help. I have solved in a different way. Now there is one query, much better and I can order my 2 users' levels by
meta_key = 'wp_capabilities'
The order will be in alphabetical order so anyway i Can have an order.
Thanks again for your support.
Glad you were able to figured it out.
Good luck, and happy blogging!
You must log in to post.
