Category: mySQL

Event Espresso 4 – Helpful Functions

event-espresso-logo-600x213-black1-600x198

I’ve recently been working on a project using Event Espresso 4 and I’ve had to customize it quite extensively. I wrote a few functions that I think might be handy for others so I thought I would share them:

Get Event ID From Registration Code

I wrote this one to help me get the event information when a user clicks on a ‘cancel registration’ link. The reg code is more secure way to pass as a GET variable in a link than the user/event/registration id.

The Function

function get_eventid_of_reg_id($regid){

    global $wpdb;

    $query = "SELECT EVT_ID from wp_esp_registration where REG_code = '".$regid."'";
        
    $result = $wpdb->get_row($query);

    $result = $result->EVT_ID;

    return $result;

}

Usage Example

$regid = "axssa-12343";

$eventid = get_eventid_of_reg_id($regid);

$event_info_Object =  get_post($eventid);

// Returns a WP_Post object https://codex.WordPress.org/Class_Reference/WP_Post

Get Event Date

This one is handy if you have the event id and you are just looking for the date of the event.

The Function

function get_event_date($eventid, $format){

    global $wpdb;

    $query = "SELECT DTT_EVT_start from wp_esp_datetime where EVT_ID = '".$eventid."'";
        
    $result = $wpdb->get_row($query);

    $result = $result->DTT_EVT_start;

    $phpdate        = strtotime( $result );
    $mysqldate      = date( $format, $phpdate );
                                                    
    return $mysqldate;

}

Usage Example

$event_id = 34;

$event_date = get_event_date($event_id, 'Y-m-d');

echo $event_date;

Get the Status of the Registration From Registration Code

This one can be used to see the status of a particular registration. I wrote it to provide confirmation that a registration had been cancelled in a custom built ‘cancellation journey’.

The Function

function get_regid_status_from_regcode($regid){

    global $wpdb;

    $query = "SELECT STS_ID from wp_esp_registration where REG_code = '".$regid."'";
        
    $result = $wpdb->get_row($query);

    $result = $result->STS_ID;
                                                    
    return $result;  

}

This function returns a status code:

“RAP” (registration status of approved. This is the default behavior.)
“RCN” (registration status of cancelled.)
“RDC” (registration status of declined.)
“RIC” (registration status of incomplete.)
“RNA” (registration status of not approved.)
“RPP” (registration status of pending payment.)

Usage Example

$registration_code = "j9ojjsk-ookkd1";

$registration_status = get_regid_status_from_regcode($registration_code);

if($registration_status == "RCN"){
   echo "This registration is cancelled";
}

Get the Status of the Event From Registration ID

Same as above just using the Registration ID.

The Function

function get_regid_status_from_id($regid){

    global $wpdb;

    $query = "SELECT STS_ID from wp_esp_registration where REG_ID = '".$regid."'";
        
    $result = $wpdb->get_row($query);

    $result = $result->STS_ID;
                                                    
    return $result;  

}

This function returns a status code:

“RAP” (registration status of approved. This is the default behavior.)
“RCN” (registration status of cancelled.)
“RDC” (registration status of declined.)
“RIC” (registration status of incomplete.)
“RNA” (registration status of not approved.)
“RPP” (registration status of pending payment.)

Usage Example

$registration_id = 72;

$registration_status = get_regid_status_from_regcode($registration_id);

if($registration_status == "RCN"){
   echo "This registration is cancelled";
}

Get Registrant Details From Registration ID

This function gives you the all the registration details of a Registrant if you have the registration ID.

The Function

function get_registrant_from_reg_id($regid){

    global $wpdb;

    $query = "SELECT wp_esp_registration.ATT_ID,  ATT_fname as 'first_name', ATT_lname as 'last_name', ATT_email as 'email' from wp_esp_registration 
              join wp_esp_attendee_meta on wp_esp_attendee_meta.ATT_ID = wp_esp_registration.ATT_ID
              where REG_ID = '".$regid."'";
        
    $result = $wpdb->get_row($query);

    return $result;

}

This function returns an object with ‘first_name’, ‘last_name’ and ’email’ as the parameters.

Usage Example

$registration_id = 42;

$registration_detail = get_registrant_from_reg_id($registration_id);

echo "The registrants first name is ". $registration_detail->first_name. "<br>";
echo "The registrants last name is ". $registration_detail->last_name. "<br>";
echo "The registrants email adddress is ". $registration_detail->email. "<br>";

 

More

ACF Option Fields on a Multisite

Get Options Fields From Advanced Custom Fields on a WordPress Multisite Installation

file5509689aae448

I’ve recently been working on a multisite project where it became apparent that it would be nice to have a ‘global options’ page. This would hold values to be used over all other sites on that same WordPress multisite network. At the time of writing this post there is no way to achieve this with functions built into Advanced Custom Fields. So I wrote my own.

Detail

wpmaz says'wp_' is the default database prefix added when installing WordPress. This can different if it pleases you.
In Advanced Custom Fields when an Option Field is saved by a user in the WordPress CMS it is saved into the ‘wp_options’ table of the database.

Like so:

option_name option_value
options_date_of_event 20150410

The ‘option name’ can be broken up into two sections. First ‘options_’ which is the prefix for an Advanced Custom Field option key and then then name of the field which is, in the above example, ‘date_of_event’. We could create a WordPress function to get this option value by querying the database:

global $wpdb;

$query =   "select option_value
	    from ". $wpdb->base_prefix . "_options
	    where option_name = 'options_date_of_event'";

$result = $wpdb->get_var($query);

return $result;

Note that the above example is redundant because you can already do the same thing much easier with:

get_field('date_of_event','options');

However! The reason we are talking about this is if we wanted to get a value from another site on the same WordPress multisite installation!

To do this we need to look at how WordPress manages its multisite database.

In a normal installation we may see a list of tables like so:

wp_commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_usermeta, wp_users.

With each site that is added WordPress will increment the names of the tables in the database like this:

wp_2_commentmeta, wp_2_comments, wp_2_links, wp_2_options, wp_2_postmeta, wp_2_posts, wp_2_terms, wp_2_term_relationships, wp_2_term_taxonomy, wp_2_usermeta, wp_2_users.

Where the ‘2’ is actually the site or ‘blog’ ID number.

We can therefore adjust the query to be dynamic enough so we can point the query to the correct options table for a desired sub site on the multisite network.

The Function

Add this to your functions.php file:

function wpmaz_get_multisite_option($blogid, $optionName){

	 global $wpdb;
         
         $blogid = ($blogid == 1) ? '' : $blogid . '_';                 

         $query = "select option_value
                   from ". $wpdb->base_prefix . $blogid . "options
                   where option_name = 'options_". $optionName ."'";
       
         $result = $wpdb->get_var($query);

         return $result;
}

You can now call the function in your template file(s) like:

<?php echo wpmaz_get_multisite_option('1', 'date_of_event') ?>

Where ‘1’ is the ID for the site holding the Global options, or the site options you wish to use and ‘date_of_event’ is the ‘Advanced Custom Field’ field name.

Notes on Final Code

Line 5: This is an if statement in php shorthand to provide logic to get round the fact that the first table in the database (ID = 1) doesn’t have a numerical prefix.

Line 8: I used $wpdb->base_prefix incase the normal WordPress database prefix wasn’t used in your WordPress multisite installation.

More