We’ve been using WooCommerce at our school for a few years to make it easier for our students to buy tickets to Senior Week events. What once required our Student Activities folks to sit at a table in our student center for weeks hoping to catch everyone has now gone online, allowing students to purchase tickets, passes and more 24 hours a day, 7 days a week.
We’re in our third year, and it’s been going great. One of the challenges we’ve faced is getting data out of WooCommerce. There are plugins that will export store data, or orders, but sometimes those are premium plugins that don’t exactly get the data out the way our student activities team likes it.
So, I began to toy around with querying the data right out of MySQL directly, skipping WordPress and WooCommerce. I’ll give them credit – WooCommerce doesn’t make some crazy schema to store information, it uses WordPress’ posts and post_meta tables to store everything. That makes my job of querying it out very easy.
So I fooled around in my MySQL client of choice, Sequel Pro, and tried various queries, but couldn’t get the first name and last name to show up on the same line, because those values are stored in different rows using WordPress’ meta_key and meta_value fields. Here’s such a query:
SELECT * from sweek_posts LEFT JOIN sweek_postmeta v1 on (sweek_posts.ID = v1.post_id) WHERE sweek_posts.post_status = "wc-processing" order by ID
Note I changed my WordPress table prefix. So should you. Here’s why.
That query worked, but returned hundreds of rows, one for each result in the posts_meta table. I tried more MySQL-fu, but struggled. A quick Google search led me to this StackOverflow post, asking something kind of like what I was looking for.
I didn’t know you could LEFT JOIN on the same table multiple times, but that’s exactly what worked to get the rows that were separate into 1 row for each result. That query looks like this:
SELECT p.ID,p.post_date, pm1.meta_value as first_field, pm2.meta_value as second_field, pm3.meta_value as third_field FROM sweek_posts p LEFT JOIN sweek_postmeta pm1 ON (pm1.post_id = p.ID AND pm1.meta_key = "_billing_first_name") LEFT JOIN sweek_postmeta pm2 ON (pm2.post_id = p.ID AND pm2.meta_key = "_billing_last_name") LEFT JOIN sweek_postmeta pm3 ON (pm3.post_id = p.ID AND pm3.meta_key = "_billing_email") WHERE post_status = 'wc-processing'
Bingo – now I’ve got order date, ID, first name, last name, and email address and I didn’t have to mess with WordPress or a plugin. Now, I can format the data any way I need it. It would also be easy to take that query and make a quick WordPress plugin.