How to Retrieve Custom Fields Data from Ultimate Member plugin Database

January 11, 2023

Where Does Ultimate Member Store User Data

Ultimate Member users sometimes face trouble locating where the form data is stored. UM stores the user data in the wp_users and wp_usermeta tables. It saves the meta key as the field key. Ultimate Member stores data in the DB tables *options, *postmeta, *posts, *um_metadata, *usermeta, *users (where * is your Database tables prefix). 

If you turned ON the setting “Enable custom table for usermeta” to use this feature. Then Ultimate Member may use this table to speed up search members in the member directory.  The database table “{prefix}um_metadata” is optional. This table is used only by the search engine in the member directory. Don’t use this table for retrieving and showing data. 

wp_users – This database table contains user fields: “user_login”, “user_pass”, “user_nicename”, “user_email”, “user_url”, “user_registered”, “display_name”.

wp_usermeta – This table contains all custom fields and predefined fields Ultimate Member fields.

wp_um_metadata – This database table contains a copy of data from tables “wp_users” and “wp_usermeta”. It is used by the member directory search and filtering engine to increase performance. The plugin copies data from original tables into this table if the setting “Enable custom table for usermeta” is turned on.

View Form Data from WordPress Dashboard

To view the submitted user data, you can check it in the WP Admin > Users > Hover on any of the users in the list to see the “Info” link > click on that link and it will show the submitted data.

You can also check the submitted data in the Profile Form. See the Profile page generated by UM on first activation. It should be in yoursite.com/user/

You can add fields from Register form to Profile via UM Form Builder. Please go to WP Admin > Ultimate Member > Forms > Edit the profile Form > click on “+” to add a new field > see “Custom fields” to see all created fields from the Register form.

Retrieve Form Data Using WP_User_Query

You can use WordPress function get_user_meta() to retrieve the field value. Ultimate Member function um_user( $data ) may be useful in some cases.

Retrieve a list of users who registered after 1 January 2023

<?php
$args = array(
    'date_query' => array(
        array(
            'after' => 'January 1, 2023',
        ),
    ),
);

$user_query = new WP_User_Query( $args );
$users = $user_query->get_results();

foreach ( $users as $user ) {
    echo 'Username: ' . $user->user_login . '<br />';
    echo 'User email: ' . $user->user_email . '<br />';
    echo 'User registered: ' . $user->user_registered . '<br />';
}

This code will create a new instance of the WP_User_Query class, passing the $args array as an argument. The date_query argument is used to retrieve only users who have registered after 1 January 2023. The code then loops through the results and displays the username, email, and registration date of each user.

Retrieve a list of users who registered after 1 January 2023

<?php
$args = array(
    'meta_query' => array(
        array(
            'key'     => 'country',
            'value'   => '',
            'compare' => '!=',
        ),
    ),
);

$user_query = new WP_User_Query( $args );
$users = $user_query->get_results();

foreach ( $users as $user ) {
    echo 'Username: ' . $user->user_login . '<br />';
    echo 'User email: ' . $user->user_email . '<br />';
    echo 'Country: ' . get_user_meta( $user->ID, 'country', true ) . '<br />';
}

This code will create a new instance of the WP_User_Query class, passing the $args array as an argument. The meta_query argument is used to retrieve only users who have a non-empty value for the country field. The code then loops through the results and displays the username, email, and country of each user.

Retrieve Form Data Using SQL Query

Retrieve a list of users who registered after 1 January 2023

You can retrieve a list of all users who registered after 1 January 2023 by using the following SQL query:

This query selects all columns from the wp_users table and returns only those records where the user_registered field is greater than or equal to ‘2023-01-01 00:00:00‘. The result will be a list of all users who registered after 1 January 2023.

Note: You’ll need to replace wp_ with the actual prefix of your WordPress database tables, if it’s different.

This code uses the $wpdb->prepare method to safely format the SQL query and avoid SQL injection attacks. The %s placeholder is used to represent the ‘2023-01-01 00:00:00‘ argument, which is passed as the second argument to the prepare method. The $wpdb->get_results method is then used to execute the query and retrieve the results.

Note: You’ll need to replace {$wpdb->users} with the actual name of the wp_users table, if it’s different.

<?php
$getUsers = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT * FROM {$wpdb->users}
                        WHERE user_registered >= %s",
                        '2023-01-01 00:00:00'
                    ), ARRAY_A
    );

    foreach($getUsers as $getUser){
        print_r($getUser);
    }

Here we are using * which feteches all the data (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name) from that table. If you only want the display name then you can use display_name instead of *

Retrieve a list of users who have filled in the Country Field

This query selects all columns from the wp_users table and joins the wp_usermeta table, where user meta data is stored, on the ID field. The query returns only those records where the meta_key is ‘country‘ and the meta_value is not empty. The result will be a list of users who have filled in the country field, along with their country.

Note: You’ll need to replace wp_ with the actual prefix of your WordPress database tables, if it’s different, and replace ‘country‘ with the actual name of the meta key that the Ultimate Member plugin uses to store the country field, if it’s different.

<?php
$getUsers = $wpdb->get_results(
$wpdb->prepare(
                   "SELECT wp_users.display_name, wp_usermeta.meta_value
                    AS country
 
                    FROM wp_users
 
                    JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
 
                    WHERE wp_usermeta.meta_key = 'country' 
                    AND wp_usermeta.meta_value != ''"
 ), ARRAY_A
);

    foreach($getUsers as $getUser){
        print_r($getUser);
    }

Export/ Import Profile Data

The user import/export functionality is not available in Ultimate Member. There are, however, free plugins that can import and export WordPress users. Consider using one of the following plugins:

1. WordPress Users Import and Export

2. WP All Import and Export

3. Import and export users and customers

Use these plugins only for exporting or importing data fields. Above plugins are not built for transfering members from one site to another.

Related Posts

Scroll to Top