Add ability to search on specific user and user meta fields to the PMPro members list page.

my_pmpro_members_list_sql.php

/*
	Add ability to search on specific user and user meta fields
	in the members list page.

	e.g. last_name:coleman will search for users with last_name LIKE %coleman%
	
	Some common PMPro-related user and user meta fields:
	* first_name, last_name, user_login
	* pmpro_bfirstname, pmpro_blastname, pmpro_baddress1, pmpro_bcity, pmpro_bstate, pmpro_bcountry, pmpro_bphone
	* subscription_transaction_id, payment_transaction_id
	
	Planning on updating this to also tweak searches on the default users table and including this in the PMPro core someday.
*/
//filter members list sql
function my_pmpro_members_list_sql($sqlQuery)
{
	global $wpdb;
	
	if(empty($_REQUEST['s']))
		return $sqlQuery;
	
	$s = $_REQUEST['s'];	
	
	//look for : to detect specific searches
	if(strpos($s, ":") !== false)
	{
		//found one, figure out the field to search on
		$parts = explode(":", $s);
		
		if(!empty($parts))
		{
			$field = trim($parts[0]);
			$value = trim($parts[1]);
		}

		if(!empty($field))
		{
			//found a field, check if it's a user field or meta
			$user_fields = array(
				"user_login",
				"user_email",
				"user_nicename",
				"user_url",
				"user_status",
				"display_name"
			);
			$order_fields = array(
				"subscription_transaction_id",
				"payment_transaction_id",
				"code",
			);

			if(in_array($field, $user_fields))
			{
				//search users table
				$user_ids = $wpdb->get_col("SELECT ID FROM $wpdb->users WHERE `" . $field . "` lIKE '%" . esc_sql($value) . "%' GROUP BY ID");
			}
			elseif(in_array($field, $order_fields))
			{
				//search orders table
				$user_ids = $wpdb->get_col("SELECT user_id FROM $wpdb->pmpro_membership_orders WHERE `" . $field . "` lIKE '%" . esc_sql($value) . "%' GROUP BY user_id");
			}
			else
			{
				//search user meta table
				$user_ids = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . $field . "' AND meta_value lIKE '%" . esc_sql($value) . "%' GROUP BY user_id");
			}
		}
	}

	//if we noticed a field and got users, change search
	if(isset($user_ids))
	{		
		//if no users_ids, use empty array
		if(empty($user_ids))
			$user_ids = array();

		//some vars for the search
		$l = $_REQUEST['l'];
		
		if(isset($_REQUEST['pn']))
			$pn = $_REQUEST['pn'];
		else
			$pn = 1;
			
		if(isset($_REQUEST['limit']))
			$limit = $_REQUEST['limit'];
		else
			$limit = 15;
		
		$end = $pn * $limit;
		$start = $end - $limit;	
		
		//filter results to only include these user ids
		$sqlQuery = "SELECT SQL_CALC_FOUND_ROWS u.ID, u.user_login, u.user_email, UNIX_TIMESTAMP(u.user_registered) as joindate, mu.membership_id, mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number, mu.billing_limit, mu.trial_amount, mu.trial_limit, UNIX_TIMESTAMP(mu.startdate) as startdate, UNIX_TIMESTAMP(mu.enddate) as enddate, m.name as membership FROM $wpdb->users u LEFT JOIN $wpdb->usermeta um ON u.ID = um.user_id LEFT JOIN $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id ";
		
		if($l == "oldmembers")
			$sqlQuery .= " LEFT JOIN $wpdb->pmpro_memberships_users mu2 ON u.ID = mu2.user_id AND mu2.status = 'active' ";
		
		//this is the line changed
		$sqlQuery .= " WHERE u.ID IN(" . implode(",", $user_ids) . ") ";				
		//---
	
		if($l == "oldmembers")
			$sqlQuery .= " AND mu.status = 'inactive' AND mu2.status IS NULL ";
		elseif($l)
			$sqlQuery .= " AND mu.status = 'active' AND mu.membership_id = '" . $l . "' ";					
		else
			$sqlQuery .= " AND mu.status = 'active' ";			
		
		$sqlQuery .= "GROUP BY u.ID ";
		
		if($l == "oldmembers")
			$sqlQuery .= "ORDER BY enddate DESC ";
		else
			$sqlQuery .= "ORDER BY u.user_registered DESC ";
		
		$sqlQuery .= "LIMIT $start, $limit";
	}
	
	return $sqlQuery;
}
add_filter('pmpro_members_list_sql', 'my_pmpro_members_list_sql');

hello! how can i use it?

This recipe is included in the blog post on "Search Specific User and User Meta Fields (Register Helper Fields) on the Members List" at Paid Memberships Pro here: https://www.paidmembershipspro.com/search-specific-user-and-user-meta-fields-register-helper-fields-on-the-members-list/