Skip to content

Instantly share code, notes, and snippets.

@ideadude

ideadude/wordpress_colon_searches.php

Last active January 23, 2024 02:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ideadude/e240468701168359baf6d1d1c2e957c5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ideadude/e240468701168359baf6d1d1c2e957c5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ideadude revised this gist Jan 23, 2024. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -112,7 +112,11 @@ function pre_user_query_colon_search( $user_query )
    } else {
    $user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql($key) . "' AND meta_value lIKE '%" . esc_sql($search) . "%'" );
    }
    } elseif ( function_exists( 'wp_is_large_user_count' ) && wp_is_large_user_count() ) {
    // Just search the users table fields.
    $user_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->users WHERE user_login LIKE '%" . esc_sql( $search ) . "%' OR user_nicename LIKE '%" . esc_sql( $search ) . "%' OR user_email LIKE '%" . esc_sql( $search ) . "%'" );
    }

    if(!empty($user_ids))
    {
    $user_query->query_where = "WHERE 1=1 AND ID IN(" . implode(",", $user_ids) . ") ";
  2. ideadude revised this gist Feb 28, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -57,7 +57,7 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    if( in_array( $key, my_pmpro_get_user_table_columns() ) ) {
    $key_column = 'u.user_' . esc_sql($key);
    $sqlQuery .= " WHERE $key_column LIKE '%" . esc_sql($s) . "%' ";
    } elseif ( $key == 'discount' || $key == 'discount_code' || $key = 'dc' ) {
    } elseif ( $key == 'discount' || $key == 'discount_code' || $key == 'dc' ) {
    $user_ids = $wpdb->get_col( "SELECT dcu.user_id FROM $wpdb->pmpro_discount_codes_uses dcu LEFT JOIN $wpdb->pmpro_discount_codes dc ON dcu.code_id = dc.id WHERE dc.code = '" . esc_sql($s) . "'" );
    $sqlQuery .= " WHERE u.ID IN(" . implode(",", $user_ids) . ") ";
    } else {
  3. ideadude revised this gist Nov 18, 2020. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -20,7 +20,7 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    global $wpdb;

    if( !empty( $_REQUEST['s'] ) ) {
    $s = $_REQUEST['s'];
    $s = sanitize_text_field( $_REQUEST['s'] );
    } else {
    $s = null;
    }
    @@ -31,15 +31,15 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    $key = $parts[0];
    $s = $parts[1];
    //some vars for the search
    $l = $_REQUEST['l'];
    $l = intval( $_REQUEST['l'] );

    if(isset($_REQUEST['pn']))
    $pn = $_REQUEST['pn'];
    $pn = intval( $_REQUEST['pn'] );
    else
    $pn = 1;

    if(isset($_REQUEST['limit']))
    $limit = $_REQUEST['limit'];
    $limit = intval( $_REQUEST['limit'] );
    else
    $limit = 15;

    @@ -69,7 +69,7 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    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 . "' ";
    $sqlQuery .= " AND mu.status = 'active' AND mu.membership_id = '" . esc_sql( $l ) . "' ";
    else
    $sqlQuery .= " AND mu.status = 'active' ";

  4. ideadude revised this gist Jun 5, 2019. 1 changed file with 3 additions and 5 deletions.
    8 changes: 3 additions & 5 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -3,11 +3,9 @@
    * Functions affecting admins and the admin dashboard.
    */
    /**
    * Return array of fields found in the wp_users table
    * This function used in the others below to determine
    * if a key entered in a search like "email:address@domain.com"
    * is referencing the user_email column of the wp_users table
    * or a unique meta key.
    * Return array of fields found in the wp_users table.
    * This function used in the others below to determine if a key entered in a search like "email:address@domain.com"
    * is referencing the user_email column of the wp_users table or a unique meta key.
    */
    function my_pmpro_get_user_table_columns() {
    return array( 'login', 'nicename', 'email', 'url', 'display_name' );
  5. ideadude revised this gist Jun 4, 2019. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    <?php
    /**
    * Functions affecting admins and the admin dashboard.
    */
  6. ideadude revised this gist Dec 23, 2018. 1 changed file with 9 additions and 9 deletions.
    18 changes: 9 additions & 9 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,6 @@
    <?php
    /**
    * Functions affecting admins and the admin dashboard.
    */

    /**
    * Return array of fields found in the wp_users table
    * This function used in the others below to determine
    @@ -13,7 +11,6 @@
    function my_pmpro_get_user_table_columns() {
    return array( 'login', 'nicename', 'email', 'url', 'display_name' );
    }

    /**
    * Speed up searching by email in the PMPro Members List.
    * Use a colon in your search to denote the key.
    @@ -23,14 +20,17 @@ function my_pmpro_get_user_table_columns() {
    function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    global $wpdb;

    $s = $_REQUEST['s'];
    if( !empty( $_REQUEST['s'] ) ) {
    $s = $_REQUEST['s'];
    } else {
    $s = null;
    }

    if( !empty( $s ) && strpos( $s, ':' ) !== false ) {
    //user specified key
    $parts = explode( ':', $s );
    $key = $parts[0];
    $s = $parts[1];

    //some vars for the search
    $l = $_REQUEST['l'];

    @@ -58,9 +58,12 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    if( in_array( $key, my_pmpro_get_user_table_columns() ) ) {
    $key_column = 'u.user_' . esc_sql($key);
    $sqlQuery .= " WHERE $key_column LIKE '%" . esc_sql($s) . "%' ";
    } elseif ( $key == 'discount' || $key == 'discount_code' || $key = 'dc' ) {
    $user_ids = $wpdb->get_col( "SELECT dcu.user_id FROM $wpdb->pmpro_discount_codes_uses dcu LEFT JOIN $wpdb->pmpro_discount_codes dc ON dcu.code_id = dc.id WHERE dc.code = '" . esc_sql($s) . "'" );
    $sqlQuery .= " WHERE u.ID IN(" . implode(",", $user_ids) . ") ";
    } else {
    $user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql($key) . "' AND meta_value lIKE '%" . esc_sql($s) . "%'" );
    $sqlQuery .= " WHERE u.ID IN(" . implode(",", $user_ids) . ") ";
    $sqlQuery .= " WHERE u.ID IN(" . implode(",", $user_ids) . ") ";
    }
    //---

    @@ -84,7 +87,6 @@ function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    return $sqlQuery;
    }
    add_filter( 'pmpro_members_list_sql', 'pmpro_members_list_sql_colon_search' );

    /**
    * Speed up search by email or user meta in the WP Users search in the dashboard.
    * Use a colon in your search to denote the key.
    @@ -105,15 +107,13 @@ function pre_user_query_colon_search( $user_query )
    $parts = explode( ':', $search );
    $key = $parts[0];
    $search = $parts[1];

    if( in_array( $key, my_pmpro_get_user_table_columns() ) ) {
    $key = 'user_' . $key;
    $user_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->users WHERE " . esc_sql($key) . " LIKE '%" . esc_sql($search) . "%'");
    } else {
    $user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql($key) . "' AND meta_value lIKE '%" . esc_sql($search) . "%'" );
    }
    }

    if(!empty($user_ids))
    {
    $user_query->query_where = "WHERE 1=1 AND ID IN(" . implode(",", $user_ids) . ") ";
  7. ideadude created this gist Aug 19, 2018.
    124 changes: 124 additions & 0 deletions wordpress_colon_searches.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,124 @@
    <?php
    /**
    * Functions affecting admins and the admin dashboard.
    */

    /**
    * Return array of fields found in the wp_users table
    * This function used in the others below to determine
    * if a key entered in a search like "email:address@domain.com"
    * is referencing the user_email column of the wp_users table
    * or a unique meta key.
    */
    function my_pmpro_get_user_table_columns() {
    return array( 'login', 'nicename', 'email', 'url', 'display_name' );
    }

    /**
    * Speed up searching by email in the PMPro Members List.
    * Use a colon in your search to denote the key.
    * email:search will search the user_email column of the wp_users table.
    * meta_key:search will search the wp_usermeta table.
    */
    function pmpro_members_list_sql_colon_search( $sqlQuery ) {
    global $wpdb;

    $s = $_REQUEST['s'];

    if( !empty( $s ) && strpos( $s, ':' ) !== false ) {
    //user specified key
    $parts = explode( ':', $s );
    $key = $parts[0];
    $s = $parts[1];

    //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;

    //search only on this one meta key
    $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 part of the query that is changed
    //figure out if it's a user column or user meta search
    if( in_array( $key, my_pmpro_get_user_table_columns() ) ) {
    $key_column = 'u.user_' . esc_sql($key);
    $sqlQuery .= " WHERE $key_column LIKE '%" . esc_sql($s) . "%' ";
    } else {
    $user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql($key) . "' AND meta_value lIKE '%" . esc_sql($s) . "%'" );
    $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', 'pmpro_members_list_sql_colon_search' );

    /**
    * Speed up search by email or user meta in the WP Users search in the dashboard.
    * Use a colon in your search to denote the key.
    * email:search will search the user_email column of the wp_users table.
    * meta_key:search will search the wp_usermeta table.
    */
    function pre_user_query_colon_search( $user_query )
    {
    // Make sure this is only applied to user search
    if ( $user_query->query_vars['search'] ){
    $search = trim( $user_query->query_vars['search'], '*' );
    if ( $_REQUEST['s'] == $search ){
    global $wpdb;

    //check for colons
    if( !empty( $search ) && strpos( $search, ':' ) !== false ) {
    //user specified key
    $parts = explode( ':', $search );
    $key = $parts[0];
    $search = $parts[1];

    if( in_array( $key, my_pmpro_get_user_table_columns() ) ) {
    $key = 'user_' . $key;
    $user_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->users WHERE " . esc_sql($key) . " LIKE '%" . esc_sql($search) . "%'");
    } else {
    $user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql($key) . "' AND meta_value lIKE '%" . esc_sql($search) . "%'" );
    }
    }

    if(!empty($user_ids))
    {
    $user_query->query_where = "WHERE 1=1 AND ID IN(" . implode(",", $user_ids) . ") ";
    }
    }
    }
    }
    add_action( 'pre_user_query', 'pre_user_query_colon_search', 20 );