Support

Home Forums Event Espresso Premium Exporting Registration Data with Additional Custom Fields from Ultimate Member

Exporting Registration Data with Additional Custom Fields from Ultimate Member

Posted: September 27, 2018 at 9:44 am


timssci

September 27, 2018 at 9:44 am

Hello,

We are needing to include some additional custom fields from the Ultimate Member plugin to be included in the export to CSV for the Event Espresso Registrations. We know that the table wp_usemeta contains a key value pair to obtain the Attendee ID (key: wp_EE_Attendee_ID, Value= Attendee ID). From that know that we can find data in the wp_esp_registration table.

Do you have an example code of how to include custom fields from other plugins (in our case Ultimate Member)?

Thank you for your help!


Tony

  • Support Staff

September 27, 2018 at 9:50 am

Hi there,

The CSV data is an array, so you can add custom fields to it as you would any normal array, the data you pull in, and how you pull that data from whichever plugin will change depending on said plugin so we don’t have examples.

I do have examples of adding a people column to the CSV array, which then pulls in the people assigned to the event and populates the column here:

https://gist.github.com/Pebblo/e38dd403563b793c8cd2dde0189deed3

The general idea will be the same, but the data you pull (and how) will be completely different, I don’t think we have any examples of pull custom fields from ultimate member but I’m assuming you already know how to pull the data you need, you just need to know how to add it to the CSV?

Note that to me it sounds like you have the above a little backwards, with the CSV you’ll have the EE_Registration object, from that you can find the attendee ID and pull the data using that rather than the other way around.


timssci

October 5, 2018 at 8:27 am

Hello, thank you for the response. We have been working through trying to get this to work and we have another question. We want to append a constant value to the excel export. When the export file is created, it has a column heading named “0”. How do we pass a value to get a column heading named “MyConstant”? See sample code below.


add_filter( 'FHEE__EE_Export__report_registrations__reg_csv_array', 'ssci_add_um_fields_to_export', 10, 2);

function ssci_add_um_fields_to_export( $reg_csv_array, $reg_row ) {

// Added a hard coded value so we are just adding one column
array_push( $reg_csv_array, "myConstantValue");

// always return csv array
return $reg_csv_array;

} //End Function

Thank you again for your help.


timssci

October 5, 2018 at 8:29 am

For some reason our code did not paste right. See if below looks better.

add_filter( ‘FHEE__EE_Export__report_registrations__reg_csv_array’, ‘ssci_add_um_fields_to_export’, 10, 2);

function ssci_add_um_fields_to_export( $reg_csv_array, $reg_row ) {

// Added a hard coded value so we are just adding one column
array_push( $reg_csv_array, “myConstantValue”);

// always return csv array
return $reg_csv_array;

} //End Function


Tony

  • Support Staff

October 5, 2018 at 9:25 am

$reg_csv_array is an associative array, using array_push() will add the element using the last index, so it will add [0] => 'myConstantValue' to the array, which is why you see ‘0’ in the column header.

Use $reg_csv_array[$key] => $value; to add to the array.

So in the example you gave, you would have:

$reg_csv_array['MyConstant'] => 'myConstantValue';
return $reg_csv_array;


timssci

October 5, 2018 at 10:30 am

Thank you for trying to steer us in the right direction. Our PHP skills are somewhat limited so any help is appreciated. We tried your response for the example and if fails on our site. The array_push will run whereas
$reg_csv_array['MyConstant'] => 'myConstantValue'; crashes the site.


Tony

  • Support Staff

October 5, 2018 at 10:51 am

Sorry, I assumed based on your opening thread you were comfortable with PHP.

The reason my example crashes the site is because I made a schoolboy error.

Remove the > so its:

$reg_csv_array['MyConstant'] = 'myConstantValue';


timssci

October 5, 2018 at 3:20 pm

Thanks for latest response. I tested that change and it worked.

Working code:
$reg_csv_array['MyConstant'] = 'myConstantValue';

Now, Iā€™m wanting to do something in reverse. My assumption is that the second parameter is also an associative array. So, why would this code cause a crash?

#1 Causes Crash:
$attendee_id = $reg_row['Registration.ATT_ID'];

Code fragment

add_filter( 'FHEE__EE_Export__report_registrations__reg_csv_array', 'ssci_add_um_fields_to_export', 10, 2);

function ssci_add_um_fields_to_export( $reg_csv_array, $reg_row ) {

  // use global wpdb object to access db (select user meta fields to export)
  global $wpdb;

  // Add user id obtain from attendee id
  $attendee_id = $reg_row['Registration.ATT_ID'];   //#1: --Causes Site Crash;  Is it an Associative Array?

   $user_id = $wpdb->get_results( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'wp_EE_Attendee_ID' and meta_value = 10498" );    //#2: -- returns value is a:1:{i:0;O:8:"stdClass":1:{s:7:"user_id";s:4:"6404";}} 
    
//$reg_csv_array['User id'] = $user_id;    
$reg_csv_array['ATT_ID'] => $attendee_id;       
                
   // always return csv array                          
   return $reg_csv_array;
                
} //End Function

Data Returned from $reg_row (first three columns from CSV Export):

Registration.REG_ID Registration.EVT_ID Registration.ATT_ID
54 10508 10999
53 10508 10498

User_id issue
The return to $user_id is

a:1:{i:0;O:8:”stdClass”:1:{s:7:”user_id”;s:4:”6404″;}}. It should be 6404 which I see in the data returned.

I know user_id is a bigint(20) so I may not know the proper way to get the integer value of it.

My PHP syntax is weak so I appreciate any information to help get me going.

Thank you!


Tony

  • Support Staff

October 8, 2018 at 9:23 am

Now, Iā€™m wanting to do something in reverse. My assumption is that the second parameter is also an associative array. So, why would this code cause a crash?

Because that assumption is incorrect šŸ™‚

I’m not sure what made you come to that conclusion, but $reg_csv_array is an associative array where each key is a string and each value is a single value.

For example, when we did this:

$reg_csv_array['MyConstant'] = 'myConstantValue';

We add an element to the array with a key ‘MyConstant’ and a value ‘myConstantValue’. That’s included directly in the CSV and as it’s a string (integers and floats also work) it worked correctly. So if you wanted the user ID to be included in the CSV, the value you add to the array is 6404, not array/object containing that value.

We actually have methods within EE that help you do all of this much easier:

//Any fields you add to the CSV must be added to all, if even if the value is empty, so initialize the 'User ID' key:
$reg_csv_array['User ID'] = ''; 

//Do we have a wp_user for this attendee record?
$user_id = EE_WPUsers::get_attendee_user($reg_row['Registration.ATT_ID']);
        
if (!empty($user_id)) {
    //You have a user id saved to $user_id.
    $reg_csv_array['User ID'] = $user_id;  
}

EE_WPUsers::get_attendee_user({ATT_ID}) pulls a user id for the contact’s ATT_ID you pass to it, it returns the user id as an int (whole number) if there is one, or null if not. So we check if user_id is not empty, and if not, add it to the array.

As you have the $user_id in the variable, I’m assuming from your first question your going to pull details fro user meta, again you need to add each value to the array individually as key => value so you’ll need to check which data types you have first.


timssci

October 8, 2018 at 6:16 pm

Okay, we’re making progress on our end. Another question for you, is there a link to access the various EE methods, such as the example you provided for EE_WPUsers?


Tony

  • Support Staff

October 9, 2018 at 3:54 am

Not currently, you would need to look through the code itself.


timssci

October 9, 2018 at 3:02 pm

I’m wanting to pull in some meta data from the wp_usermeta table and include it in registration filtered csv report. Only want to include meta_value where the meta_key matches the ones in the $meta_keys_include array (see code snippet below).

My problem is I’m not a PHP developer and have very limited knowledge of the espresso event/wP product along with its classes and methods. I’m assisting a front end web developer with some back end processing. My background is a desktop developer using C#.

If you could assist me within the START BLOCK and STOP BLOCK section, I believe it would accomplish our desired result. For a given user id, we need to pull meta_value for matched meta_keys and place in the $reg_csv_array for inclusion into the csv report.

Using two arrays and determine the index offset for the key into a values array, it should be the value which I want to display (string value). I do not know the best way to read the two fields from the table. Your recommendation would be appreciated.

//Code snippet to add wp_usermeta data meta data to csv export:

//———————————————————————————————————-//
// Used by: Registraction CSV Export (button Filtered CSV Export) //
// Purpose: Include additional fields from the Ultimate Member Plugin data from the wp_usermeta table //
//———————————————————————————————————-//
add_filter( ‘FHEE__EE_Export__report_registrations__reg_csv_array’, ‘ssci_add_um_fields_to_export’, 10, 2);

function ssci_add_um_fields_to_export( $reg_csv_array, $reg_row ) {

// use global wpdb object to access db (select user meta fields to export)
global $wpdb;

$meta_keys_include = array(
‘member_program_yn’,
‘dvsv_organization’,
‘phone_number’,
‘kcsdv_address’,
‘city_kcsdv’,
‘state_kcsdv’,
‘title’,
‘your_profession’,
‘job_type’,
‘professional_type’
);

// Initialize User ID Column to Nothing
$reg_csv_array[‘User ID’] = ”;
// Initialize All meta key Values to Nothing
foreach ($meta_keys_include as $column) {
$reg_csv_array[$column] = ”;
}

//Do we have a wp_user for this attendee record?
$user_id = EE_WPUsers::get_attendee_user($reg_row[‘Registration.ATT_ID’]);

if (!empty($user_id)) {
//You have a user id saved to $user_id.
$reg_csv_array[‘User ID’] = $user_id;

//——-START Block

// Now, let get a the meta value for this user id and only include values for ones in the meta_keys_include array
// Define Empty Arrays
$metakeys = array();
$metavalues = array();

$query1 = “SELECT meta_key FROM $wpdb->usermeta WHERE user_id =%d”;
$result1 = $wpdb->get_var($wpdb->prepare($query1, (int)$user_id));
if ( mysqli_num_rows($result1) > 0 ) {
while ( $row = mysqli_fetch_assoc($result1) ) {
$metakeys[] = $row;
}
}

$query2 = “SELECT meta_value FROM $wpdb->usermeta WHERE user_id =%d”;
$result2 = $wpdb->get_var($wpdb->prepare($query2, (int)$user_id));
if ( mysqli_num_rows($result2) > 0 ) {
while ( $row = mysqli_fetch_assoc($result2) ) {
$metavalues[] = $row;
}
}

$idx = 0;
foreach ($metakeys[0] as $metakey) {
if ( in_array( $metakey, $meta_keys_include ) ){
$reg_csv_array[$metakey] = $metavalues[$idx];
}
$idx++;
}

//——-STOP Block

// $results = $wpdb->get_results(
// $wpdb->prepare(“SELECT meta_key, meta_value FROM {$wpdb->wp}usermeta WHERE user_id=%d”, $user_id);

// // Loop thru all meta_keys and only include values for matched include keys
// foreach ($results as $key => $value) {
// if ( in_array( $key, $meta_keys_include ) ){
// $reg_csv_array[$key] = $value;
// }

}

// always return csv array
return $reg_csv_array;

} //End Function


Josh

  • Support Staff

October 9, 2018 at 8:43 pm

Hi,

WordPress actually has a function you can use to get a specific user meta field. You’ll find some examples of how to use the function in its documentation:

https://developer.wordpress.org/reference/functions/get_user_meta/


timssci

October 10, 2018 at 10:03 am

Thanks Josh! It works as expected. I’ll share my code snippet in case someone else would like to do the same thing.

//-----------------------------------------------------------------------------//
// Used by: Registraction CSV Export (button Filtered CSV Export)                                           //
// Purpose: Include additional fields from the Ultimate Member Plugin data from the wp_usermeta table       //
// Usage  : Include meta keys in array $meta_keys_include to obtain meta data as additional csv column.     //
//----------------------------------------------------------------------------------------------------------//
add_filter( 'FHEE__EE_Export__report_registrations__reg_csv_array', 'ssci_add_um_fields_to_export', 10, 2);

function ssci_add_um_fields_to_export( $reg_csv_array, $reg_row ) {

   // Add Altimate Member meta_key values that you want to include in the CSV Array from table wp_usermeta 
   $meta_keys_include = array(
			'member_program_yn',
			'dvsv_organization',
			'phone_number',
			'kcsdv_address',
			'city_kcsdv',
			'state_kcsdv',
			'title',
			'your_profession',
			'job_type',
			'professional_type'
	        );

    // Initialize All meta key Values to Nothing (creates column headings in CSV)
    foreach ($meta_keys_include as $column) {       
    	$reg_csv_array[$column] = '';     
    }
    
    //Do we have a wp_user for this attendee record?
	$user_id = EE_WPUsers::get_attendee_user($reg_row['Registration.ATT_ID']);
        
	if (!empty($user_id)) {
    	//You have a user id saved to $user_id.        
        // Get meta data for desired meta key
        foreach ($meta_keys_include as $key) {       
        	$reg_csv_array[$key] = get_user_meta($user_id, $key, true);
        }
	}
    
   // always return csv array		
   return $reg_csv_array;

} //End Function


timssci

October 10, 2018 at 11:34 am

Two more questions.

#1. The code in the previous example works except in this case. If participant A and B are registered users, A sign in and registers for an event and then also signs up B. B’s ultimate member data shows as serialized data in the csv report for radio buttons. I recall that Tony indicated that I had to be aware of the data type. Although data from wp_usermeta is from field meta_value which is only one data type. So, how does it work for most cases and not others by way they sign up.

#2. How would I remove some of the columns from the csv report for the registration event like ‘Address Part 1[ATT_address]’, ‘Address Part 2[ATT_address]’, and ‘City[ATT_city]’? These fields will always be blank in my case.

I really appreciate the awesome support. Thank you!


timssci

October 10, 2018 at 3:01 pm

I was able to resolve #2. Found code snippet on github.com


Tony

  • Support Staff

October 11, 2018 at 5:43 am

Although data from wp_usermeta is from field meta_value which is only one data type.

This isn’t actually true, get_user_meta() will return either a string or array, with your setup it’s a string.

However, when plugins add fields types such as checkboxes to user meta, they’ll store the values in a serialized array, which when returned, it still a string.

Try something like this:

$value = maybe_unserialize(get_user_meta($user_id, $key, true));
if( is_array($value) ) {
    $value = implode(', ', $value);
}
$reg_csv_array[$key] = $value;

Pull the value, unserialize if needed (it will just be returned to $value if not needed), if it’s an array piece them together and return as a string, then set the value.

I installed Ultimate member to test this and got the same issue with both registrants, I’m not sure why your only getting it on the second. Does the first definitely have those checkboxes checked in their profile?


timssci

October 11, 2018 at 8:06 am

Thank you so much! That resolved my issue. Espresso support has been awesome!


Josh

  • Support Staff

October 11, 2018 at 8:23 am

Glad to hear. If you have a moment, perhaps you can share a brief note about your support experience here:

https://wordpress.org/support/plugin/event-espresso-decaf/reviews/#new-post

Thanks!

The support post ‘Exporting Registration Data with Additional Custom Fields from Ultimate Member’ is closed to new replies.

Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.

Support forum for Event Espresso 3 and Event Espresso 4.
Documentation for EE3 and EE4
Documentation for Event Espresso 3

Documentation for Event Espresso 4

Status: closed

Updated by  Josh 2 months ago ago

Topic Tags

Notifications

This topic is: not resolved
Do NOT follow this link or you will be banned from the site!