Postcards From My Life

Dear Reader,

WARNING: This is not discussing a WooCommerce report I’ve written, this is raw SQL. If you are not a developer, this isn’t the post you are looking for. 

A Short History of Cal

Back in the early ’90s I was working for my parents company as a programmer. I wrote a report once that would scan the entire order system and list out the customers who had purchased a specific product. At that time, and in our industry, this was revolutionary. We actually had vendors calling us asking us to run it for various reasons. (None of them spam, this was pre-email)

Fast forward to today and I still need this information. These days however, I don’t have to sequentially scan hundreds of files of order history to find the information. We’ve got SQl and RDBMS to help with that. it should be easy, right? Easy is such a relative term.

So What?

For Nomad PHP, we use WooComemrce to handle all order processing. WooCommerce uses WordPress’s internal structure for most of it’s data storage. This means that things like First Name, Last Name, and Email Address are buried in a field named meta_value in the wp_postmeta table. This means that you lose all the advantage to storing things in a RDBMS. Still, I need this data.

Just Give Me the Code

It turns out, it is possible to get this data out of the system if you are patient and willing to write some incredibly ugly SQL. Still, it was fun (for me) since it’s been a long time since I’ve hand-coded a pivot table. (What we in the FoxPro world used to call a Cross Tab)

Here’s the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

DROP TABLE IF EXISTS cal_holding;
SELECT @SKU := "YOUR ITEM'S SKU";
 
SELECT @SKU_ID := post_id 
  FROM wp_postmeta WHERE meta_key="_sku" AND meta_value=@SKU ;
 
CREATE TABLE cal_holding AS
  SELECT order_id, 
          meta_key,
         meta_value
    FROM wp_postmeta
         LEFT JOIN 
           (SELECT order_id
              FROM wp_woocommerce_order_items `items`
                   LEFT JOIN 
                     (SELECT order_item_id
                        FROM wp_woocommerce_order_itemmeta
                       WHERE meta_key='_product_id' 
                             AND meta_value=@SKU_ID) AS `orders`
                    ON `items`.order_item_id = `orders`.order_item_id
             WHERE orders.order_item_id IS NOT NULL) AS `all_orders`
          ON all_orders.order_id = wp_postmeta.post_id
   WHERE order_id IS NOT NULL
         AND (wp_postmeta.meta_key='_billing_email'
              OR wp_postmeta.meta_key='_billing_first_name'
              OR wp_postmeta.meta_key='_billing_last_name');
 
SELECT h0.order_id,
       h1.meta_value AS email,
       h2.meta_value AS first_name,
       h3.meta_value AS last_name
 FROM cal_holding h0
      INNER JOIN cal_holding h1 ON h0.order_id=h1.order_id AND h1.meta_key='_billing_email' 
      INNER JOIN cal_holding h2 ON h0.order_id=h2.order_id AND h2.meta_key='_billing_first_name'
      INNER JOIN cal_holding h3 ON h0.order_id=h3.order_id AND h3.meta_key='_billing_last_name'
GROUP BY order_id;
 
DROP TABLE IF EXISTS cal_holding;

Now It’s Time for the Breakdown

I’m not going to break it down line by line but I’ll hit the highlights.

Line 1 just makes sure that the holding table doesn’t already exist.

Line 2 is very important. Where it says YOUR ITEM’S SKU, put in you item’s SKU.

Lines 7-26 create a table called cal_holding that holds the first name, last name, and email address of everyone who has purchased the SKU. This info is usable but not in the format we want. all the data is contained in individual rows and it’s still a pain to work with. There are 3 selects in that statement and two of them are sub-selects for joins. It is not pretty. However, if you run the first two select statements  (lines 2 and 4) you can run the selects individually to see what they produce.

Lines 28-36 create our pivot table. We take the data stored in the field meta_value and move it into  fields named more appropriately, first_name, last_name and email. We use the holding table but we use three times. Then we group on the order id to give us a single row for each order, that contains each of the three fields.

fClose()

This is not a scalable solution. If you’ve got more than about 5 fields, this gets unwieldy fast. There are articles out there on how to automate this but they required prepared statements and I just didn’t feel like doing that.

Note:

While it is possible to execute this in the MySQL cli client, I would strongly recommend you use a better tool. I like SequelPro for macOS and HeidiSQL for Windows. Both of these are excellent tools, both are free, (but you should donate) and both will make it much easier for you to play with this code and tinker a bit.

License:

The above code is released to the public domain. I claim no copyright on it at all. If you want to use it in a plugin that puts a button on a product that runs this and let’s me export the results to CSV, I’m fine with that. If you want to charge for it, I’m fine with that. If you want to give me a copy for free, I’M FINE WITH THAT.

I wrote it mainly because I’m tired of asking the question “In WooCommerce, how do I…?” and being told that the answer is, “I’m going to need your credit card number…every year.”

Until next time,

I <3 |<

Dear Reader,

Those that know me know that I have an obsession with marketing. I mean I’m no good at it, but the topic fascinates me. Almost all of the podcasts I listen to on a regular basis are marketing related. One topic in particular that interests me is “Marketing Automation”. Marketing Automation covers a huge swath of topics and since I am not an expert at the, I won’t attempt to explain them. However, three things that are covered by MA that I understand reasonably well are:

• Lead Generators
• Landing Pages
• Email Marketing Campaigns

Even here we have topics so broad that entire books have been written on each of them. Still, these thee topics represent the heart of what is called “Inbound Marketing”.

Inbound Marketing is you trying to convince people to come to your site and buy/join your mailing list. This is as opposed to Outbound Marketing where you contact potential customers directly and try to convince them to buy.

Introducing Mautic

Because I am interested in Marketing Automation and want to start applying the techniques in the projects I run.

I started looking around for vendors who could provide these services. What I found is that most SaaS vendors assume that everybody who wants to use their software has deep pockets.

Side Note: I had a real interesting interview with someone form PostMark this past week after I tweeted that I did not choose them but chose Mail Gun. The subject of price came up and my words to him were “Yes, it’s only $15/month. However, right now I’ve got 7-8 companies wanting just $15-$25 per month. It all ads up quick.

During my research into solutions that may or may not work but I couldn’t afford to try, I cam across a project called Mautic. Mautic had three major things going for it right away.

1. It is Open Source
2. It is written in PHP
3. One of the leads at Mautic is a friend of mine, Don Gilbert

Wow! To me, a long time PHP developer, this was a home run. I began digging deeper into it.

• It integrates into WordPress, my tool of choice for building websites.
• I can host it myself. (This is probably more important to me than others. My reasons are partly technical and partly political.)
• I can contribute back to the project.

So we have a winner and I was able to give a big raspberry to all the other SaaS vendors who wanted me to pony up each month. Well, that’s what I thought at least.

As it turns out Mautic – while it it is most of the things I said – is still open source software. This means that development is at the whim of contributors that have other priorities. This meas that there are problems with Mautic that will get fixed when they are a problem for someone with the knowledge and time to fix them. While this is ok for me because it is possible for me to dive in and fix things if they reach a level of importance to me, it’s probably a downside to most non-developer users.

Where to go from here?

Despite some obvious flaws and at least one huge show stopping bug, I see a bright future for Mautic. So I’m going to invest my time in getting it setup and running. I’ve already run the install twice and I’m happy with the results the second time.

Along the way, I am going to blog what I learn. This is both for me so I can reference it later, and to help anyone else who is working with Mautic.

My setup will be:

• Mautic for the Marketing Automation
• WordPress for content, landing pages, and e-commerce
• Mail Gun for transactional mail
• MailChimp for broadcast emails
• Digital Ocean droplet for hosting

Most of what I do can be done without having to worry about hosting your own copy. I am doing it this way so that I can integrate Mautic into my existing infrastructure. So don’t worry if you aren’t a programming, you can still learn from my mistakes. :)

Along the way, I will get things wrong – my definitions above may already be wrong. Leave me a comment and correct me. I’m not claiming that I know what I’m doing. I’m just saying that I’ll tell you what I’ve done, and what I’ve learned.

Posts

• Step 0 – Installing Mautic
• Step 1 – Configuring an Email Service Provider
• Step 2 -Cron Jobs

Until next time,
I <3 |<
=C=

Dear Reader,

I’m making some changes to my blog. Specifically, I am moving most of my Public Speaking blog posts to my mailing list. I’ve got more of those than I realized. Being a program, my thought process was of course “Why spend an hour copying and pasting all of these posts when I can spend two hours and write a script?” :) So I wrote a simple script using WP-CLI to gather the information. So I hammered out some bash to get the job done. I am proud that other than wp-cli, I did not have to resort to any additional PHP code to do the job. It was tempting at times, but I did it.

Yes, I am aware that wp-cli will export to a WXR file. I wanted something simpler.

No, this is not a complete solution. It doesn’t deal with attachments, comments, or metadata. I don’t need those for this project.

Purpose

This bash script will export all of the blog posts in a given WordPress category into individual HTML files.  There is no templating to control how they are output, it is not that smart. It takes no parameters, everything is hard coded.

Assumptions

1. You have wp-cli installed on your machine, it is named wp, and it is in your path.
2. You have a WordPress blog

Here is the script. Below, I will break it down line-by-line in case it’s not clear.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#!/bin/bash
HOME_DIR=~
WP_DIR=/path/to/public_html/
CATEGORY=speaking
mkdir -p $HOME_DIR/blog/$CATEGORY
cd $WP_DIR
 
for LINE in $(wp post list --category_name=$CATEGORY --fields=ID,post_name --format=csv| tail -n +2); do
        ID=$(echo $LINE | cut -f1 -d,)
        SLUG=$(echo $LINE | cut -f2 -d,)
        TITLE=$(wp post get $ID --field=post_title)
        POST_DATE=$(date -d "$(wp post get $ID --field=post_date)" +"%Y-%m-%d")
        AUTHOR=$(wp user get $(wp post get $ID --field=author) --field=display_name)
        echo "Processing $TITLE"
        echo "<h1>$TITLE</h1>" > $HOME_DIR/blog/$CATEGORY/$SLUG.blogpost.txt
        echo "<strong>Author: </strong>$AUTHOR</storng><br />"  >> $HOME_DIR/blog/$CATEGORY/$SLUG.blogpost.txt
        echo "<strong>Date Published : </strong>$POST_DATE<br />" >> $HOME_DIR/blog/$CATEGORY/$SLUG.blogpost.txt
        wp post get $ID --field=post_content >> $HOME_DIR/blog/$CATEGORY/$SLUG.blogpost.txt
done

2: This is the home directory. A directory named blog/CATEGORY will be created under this directory. It is set to the user’s home directory.

3: This is the root of your WordPress installation.

4: This is the category that you want to export.

5: Create the directory to hold the posts

8: Get a list of the post IDs for the given category. Execute lines 9-18 once for each post. The wp command in the for loop will return a csv list of ID and post names. (the slug)

9: Get the post ID from the CSV line using cut.

10: Get the slug.

11: Use wp to get the title of the post.

12: Use wp to get the post date. Use date and a format of YYYY-MM-DD to strip off the time.

13: Use wp to get the author id and feed that to wp to get the author’s display name.

14: Let the user know what we are currently processing.

15: Output the Title of the post as an H1

16: Output the By-line.

17: Output the post date

18: Use wp to gather the content of the actual post and output it.

Lather, rinse, repeat.

:Conclusion

This is one of those “gets the job done” scripts. It is brittle and it is fragile. There are a lot of ways to break it and there is zero error-handling in it. All that having been said, it gets the job done. More importantly, it illustrates one of the cool things about wp-cli, scriptability of WordPress. I live in the command line, wp-cli has quickly become one of my most used tools. This, however, is the first time I’ve used it as part of a larger script. I think that’s cool. :)

Until next time,
I <3 |<
=C=

Dear Reader,

The Lovely and Talented Kathy is responsible for how all our websites look here at EICC. If it looks good, she did it, if it doesn’t look good, I’ve obviously been mucking around in it and she will eventually fix it. Occasionally, she has need for my particular skills. Compared to her artistic pen strokes, my code in a sledge hammer, but when dealing with WordPress, sometimes a sledgehammer is what is needed.

With Stylesheets, order matters

I’ll never understand why theme and plugin authors don’t understand this one simple idea. User stylesheets should be loaded last. They should be able to override anything and everything because the end designer, not you the theme or plugin designer know what is best. Still there are some that simply insist that the the user’s stylesheet not be the last thing enqueued to load. I’ve grown tired of fixing this on each and ever site we have by digging into the functions.php of the last site we built to figure out how I did it. So I’m blogging my solution.

Hacking WP_Styles

What I found is that WordPress has a wonderful object called WP_Styles When theme and plugin authors enqueue styles, it adds them to this object. (For fun one day drop a print_r($wp_styles) into your functions.php. There is a LOT of stuff in there.)

One of the properties that I noticed when studying WP_Styles was the queue array. Looking at it, this was a list of the tag ids of the stylesheets to be queued. Examining the source of the page that was output, it was obvious that they were in the same order as the stylesheets were output. So I decided to test and see if the order of the array controlled the output order. Sure enough it did. So from now on, this little piece of code lives in the functions.php of every child theme she creates.

The Code

To use this, just identify the IDs of the style sheets you want to move, and add them to the $keys array.

1
2

<link rel='stylesheet' id='bootstrap-basic-style-css'  href='/style.css' type='text/css'/>
<link rel='stylesheet' id='it-exchange-child-theme-css-css'  href='/style.css' type='text/css' />

The stylesheets above are the two I have listed in the function below. Notice that each is appended with an additional -css. Make sure you do not put that in the code below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

function cal_adjuststylesheets() {
  global $wp_styles;
 
  $keys=[
    'bootstrap-basic-style',
    'it-exchange-child-theme-css'
  ];
  $wp_styles->queue = array_values($wp_styles->queue);
 
  foreach($keys as $currentKey) {
    $keyToSplice = array_search($currentKey,$wp_styles->queue);
      if ($keyToSplice!==false && !is_null($keyToSplice)) {
        $elementToMove = array_splice($wp_styles->queue,$keyToSplice,1);
        $wp_styles->queue[] = $elementToMove[0];
      }
  }
}
 
add_action( 'wp_print_styles', 'cal_adjuststylesheets',99);

The last line calls this code just before the stylesheets are printed. Make sure you put that in there or WordPress will ignore it.  Order is important as the style sheets you specify will float to the bottom of the list but be in the order that you have specified.

WrapUp

Drop that little snippet in your functions.php and you never have to worry about your stylesheet being listed before the one you want to override.

Until next time,
I <3 |<
=C=

Dear Reader,

Most of the time when I am behind the microphone, I an the interviewer. It is my job to pronounce the guest’s name correctly, ask interesting questions, and try not to say “Ummm…”. (It’s harder than it sounds) Back in May though, I was the guest on a podcast produced by Clark Buckner of Technology Advice. It’s fun only having to worry about not saying “Ummm…” :)

Until next time,
I <3 |<
=C=