Wordpress shortcode linked to another DB

Question

First thing first, this is my first shortcode attempt. It is worthed mention that I'm also using woocommerce on my website.

Let's start:

I know that to add shortcodes into wordpress, you need to write something similar to the code below into the functions.php file: (this is just an example)

function myshortcode() {   
    return "This is a shortcode example!";
}
add_shortcode( 'mycode', 'myshortcode' );

and if i add [mycode] into the wordpress page editor, the preview shows my text correctly.

But what if i need to use a variable (in my case woocommerce order number) in the shortcode?

Let's say i need to compare woocommerce_order_number with my_custom_uid (inserted into another database, outside wordpress).

I usually use a db request like the one below, and usually it works fine (as before, this is only an example):

select 'my_custom_uid' from 'my_custom_database' where 'woocommerce_order_number' = '1234'

The problem is that i don't know the woocommerce_order_number (it changes everytime!), because this shortcode needs to go inside an html email body i need to send out to customers after they placed the order.

How can i get the customer woocommerce order (variable that changes everytime) so that i will be able to use it into my shortcode to link it to my custom_uid?

If the question is not clear enough, please feel free to ask for clarification! thanks a lot

Answer 1

I don't see a reason to use a shortcode. If you want to add something to an email, you should use one of the hooks in the email. For example:

function kia_display_email_order_meta( $order, $sent_to_admin, $plain_text ) {
    $some_field = get_post_meta( $order->id, '_some_field', true ),
    $another_field = get_post_meta( $order->id, '_another_field', true ),
    if( $plain_text ){
        echo 'The value for some field is ' . $some_field . ' while the value of another field is ' . $another_field;
    } else {
        echo '<p>The value for <strong>some field</strong> is ' . $some_field. ' while the value of <strong>another field</strong> is ' . $another_field . '</p>';
    }
}
add_action('woocommerce_email_customer_details', 'kia_display_email_order_meta', 30, 3 );

Note that the $order object is the first parameter available to the kia_display_email_order_meta function. So you'd be able to get the ID via $order->id. This should add the data after the customer address details, but there are other hooks available if woocommerce_email_customer_details isn't appropriate.

Answer 2

I finally managed to solve this, and here's what i did if someone is interested:

<?PHP

add_action('fue_before_variable_replacements', 'register_variable_replacements', 11, 4);
add_action('fue_email_variables_list', 'email_variables_list');

/**
 * This gets called to replace the variable in the email with an actual value
 * @param $var - Modify this: array key is the variable name, value is the replacement
 */


function register_variable_replacements($var,  $email_data, $queue_item, $email){
    global $wpdb;
    // Look up UID from order number
    $orderNumber = $var->get_variables()['order_number'];
    $sql = " //Your sql statement here...//";
    $results = $wpdb->get_results($sql);
    $UID = $results[0]->meta_value;
    $variables = array(
        'uid' => $UID
    );
    $var->register($variables);
}

function email_variables_list($email)
{
    global $woocommerce;
    ?>
    <li class="var hideable var_subscriptions">
        <strong>{uid}</strong>
        <img class="help_tip" title="<?php _e('Order UID', 'follow_up_emails'); ?>" src="<?php echo $woocommerce->plugin_url(); ?>/assets/images/help.png" width="16" height="16"/>
    </li>
    <?php
}

Now on your follow up email plugin, you can use {uid} as a variable and this will be replaced with the correct value on every email.

I though the best way was to use short code, but then i discovered this filter and i think this is the best way to handle this. This code is also pretty flexible and you can add as many variables as you want.

Hope this can help someone.