Skip to content

WooCommerce “Who Bought It”

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 |<

Twitter Player Card for Blubrry PowerPress WordPress Plugin

Dear Reader,

For the past 10 years I have been a podcaster. In that time I’ve used a lot of tools to present my podcast. None of them are better than WordPress and PowerPress. The one thing that PowerPress is lacking is the ability to create a Twitter Player Card for my content. So I hacked something together.

PowerPress Player Card is a WordPress plugin. You have to have PowerPress installed and activated before it will activate. Without PowerPress, it’s useless anyhow.

The Readme contains installation instructions. You have to install it manually because it isn’t in the WordPRess plugin repo and most likely never will be.

Once you have it configured and you’ve validated your card using the Twitter Card Validator, everything is automatic. Add your podcast post and when the link is tweeted, users will see the player, right in their page.

 

Screen capture of a twitter player card for Voices of the ElePHPant
So now casual listeners can enjoy your podcast without having to subscribe to your feed or get a podcast app.

Thank you BluBrry for a great plugin.

Until next time
I <3 |<
=C=

Reordering Style Sheets in WordPress

Dear Reader,

10/09/2019
Updated the code. The original code would not work properly if the array had any missing keys. (e.g. a style sheet was unenqueued). To fix this, I use array_values() to remove any gaps in the index before processing it.

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=

WordPress REJECTED MY PLUGIN!

The Rejection by Andreas WintererDear Reader,

One of the biggest complaints I hear about WordPress is that yes, you can choose from thousands of plugins, but many of them are crap. Because there was no barrier to entry, many of them were poorly coded and could even introduce security vulnerabilities to your site. I recently found out the hard way that WordPress is moving to change that. :)

First, they have been retiring old and unmaintained plugins for a while now. Thankfully, that includes all the plugins I wrote back in the 1.5-1.9 days. :) More importantly though, there is now a code review before accepting new plugins. This is a very good move on their part and I applaud them for this move.

There is a small problem though. They don’t seem to have published anything on what is acceptable/unacceptable in a plugin. In discussing my particular plugin with the reviewer, it seems the rules are kind of fuzzy as to what gets accepted or not, and they seem to be changing.

Overall, I think that a code review process is a very positive move for the WordPress ecosystem. It would be nice however, if the review team published the current rules. Even if those rules change, a current set of rules would help make sure that plugin developers don’t waste time and effort on plugins that won’t be accepted.

If these guidelines are already available, linking to them from “Writing a Plugin” would be apprecaited.

Well done, WordPress!

Until next time,
I <3 |< =C= p.s. I am working on updating my plugin to change the reported problem so that hopefully it will be accepted next time. (fingers crossed) :) Photo Credit: The Rejection by Andreas Winterer. Used under Creative Commons License.

Setting Up a (FREE) WordPress Development Site

Dear Reader,

The Problem

Most of you who read this blog are software developers. You know the importance of separate DTAP (Development, Testing, Acceptance, and Production) environments. However, not everyone understands this. I was recently at the WordPress Nashville meetup when someone mentioned having a development environment for their blog and you could here the crickets chirping. A lot of the attendees of that – and many other WordPress meetups – are not developers and may have never considered the need for a development area, after all, they don’t develop, right?

Everyone however, experiments. Whether it’s a new theme or a new plugin, you really, really need someplace to test things. one of the worst thing you can do is what I do with this blog, just install things and play with them in production. You need someplace where you can try out new plugins, new ideas, new themes. Not necessarily someplace where you post all your content, WordPress does a great job of allowing you to test things out content-wise before committing.

Development systems used to mean having your own server; as a matter of fact, I still do have one running here at the house. I do not recommend this though. It’s a gross waste of resources if you are just testing out a single blog, or even a few. You could also load WordPress on your laptop or desktop but I don’t recommend this either. To make it work though, you have to install and maintain a complete “web stack” (Apache, PHP, MySQL) This is just more software on your machine that has to be kept up to date.

There is a solution though, actually, I’ll present you with two. One for PHP developers who know what they are doing and want control, and one for regular bloggers who just want someplace to test plugins and themes before pushing them live. In both cases though, the services are free.

The Developer’s Solution

If you know what an ssh key is, and you understand source code control systems, then the solution you want is phpcloud.com. it’s a free service from Zend that is specifically designed for developers. phpcloud.com is a cloud-based development hosting. They host your project that you are working on, not the ones in production. You can’t use phpcloud.com for production systems. (and by can’t I don’t mean it’s against their ToS, I mean it won’t work. it’s not designed for that.)

Getting setup is easy.

  • Create an account. Find me on twitter if you need a beta invite and don’t know how to get one.
  • Create a Container.
  • Create an application inside that container. At this point, you have the option of selecting what kind of application, select WordPress.
  • Using git, clone the application to your local machine.
  • Code or experiment, commit, push, test, rinse repeat.

Again, this is a developer solution. I left out a lot of small details like setting up keys, etc. The docs tell you how to do that but if you aren’t familiar with the concepts, . You wouldn’t play with a jackhammer just because you thought ti was pretty, don’t play with tools like phpcloud.com unless you understand them.

Using phpcloud.com you can install themes, plugins, hack the core, (don’t you dare) or do just about anything else. To install plugins, you unpack them locally in the plugins directory, commit them to the repo and push. Then go into your test area and activate them. It’s not quite as simple as using the built-in installer but you know the saying “with great power…”.

Once you have tested a plugin, theme, widget or idea and know that it works properly, feel free to install in production knowing that you’ve done your due diligence.

I will mention one downside of phpcloud.com for WordPress developers. The automatic install and upgrade system will not work. You can try, you can fiddle with permissions all you want but at the end of the day, it’s just not going to work. Boaz and the team are aware of this shortcoming and it’s on their roadmap to fix.

The Blogger’s Solution

Ok, if you aren’t a developer, there’s still a way for you to get a free development area and it’s still important for you to test thing before you start mucking around with your production system. For you non-developers, use the free offering from my friends over at phpfog.com. Like phpcloud.com, there are limits to what you can do with the free offering but you should be able to get up and running with the free offering. The main limit you will hit is the 20MB space limit on your database. If you like phpfog.com, you may want to consider either setting up a pay account or moving all your hosting over to them. If you do the latter though, don’t just use your testing area as your new production area. If you do, you are really missing the point of this post.

  • Create an account at phpfog.com
  • Once in, select your “Shared Cloud”. (There is a button for it.)
  • Create an app in your shared cloud.
  • Select WordPress
  • Answer the questions.
    One of the questions is what domain name to use. If you are not familiar with DNS settings, use a phpfog.com subdomain. It’s easier that way. If you do understand DNS and know what you are doing, you can setup test.yourdomain.com or beta.yourdomain.com or something like that.
  • Be patient. No matter what it says, it can take up to five minutes for your app to be created.

Now you can begin installing plugins, themes, and widgets. If you are working with a developer, they can setup their ssh keys and get to the source code. Unlike phpcloud.com you can use WordPress’ automatic install and update tools to keep your test site up to date.

Wrap-Up

No matter which way you go – phpcloud.com, phpfog.com, or some other solution – every WordPress blogger needs to have a development area that is wholly separate from your production environment. This means don’t just install another instance of WordPress on your production server and call it test. It needs to be separate from your production server.

In this post we’ve discussed two solutions that you can use to get a test system up and running for free but there are other ways to accomplish this. The takeaway is not to use one of these services but to get a test system setup and use that for all your experimenting.

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

Photo Credit: Huasonic
Released under a Creative Commons license.