A client of mine has a rather large WooCommerce database. We’ve been trying to run reports using the WooCommerce Customer/Order CSV Export plugin but have been running into timeout issues—the glorious WordPress white screen of death.
I got fed up and wrote the following script to export the needed information. Customize as you see fit. Enjoy.
Update 26 Aug 2016: By popular demand, I added a way to query for orders based on product name. Querying by product id is the right way but makes my head hurt. Suggestions welcome.
select
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
post_type = 'shop_order' and
post_date BETWEEN '2015-01-01' AND '2015-07-08' and
post_status = 'wc-completed' and
oi.order_item_name = 'Product Name'
group by
p.ID
Thank you.
Your query to me served as a basis for arriving at this one that adds the phone and order status id.
This part of your query seems not to work according to my wp_posts fields table:
post_status = 'wc-completed'I do not have any ‘wc-completed’ value on p.post_status field.
This is the version I use, if I let someone else serves.
Greetings!
select p.ID as order_id, p.post_date, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email, max( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_phone, max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name, max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name, max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total, max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax, max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date, max( CASE WHEN p.ID = wptr.object_id THEN wptr.term_taxonomy_id END ) as status from wp_posts as p, wp_postmeta as pm, wp_term_relationships as wptr where post_type = 'shop_order' and p.ID = pm.post_id and post_date BETWEEN '2015-06-31' AND '2015-08-01' group by p.IDHi Pedro, thanks for the additions and you’re welcome. WooCommerce added the prefix
wc-to their order statuses in version 2.2. I’m guessing that might be why you’re not seeing any orders. What version do you have?Read more here:
https://woocommerce.wordpress.com/2014/08/07/wc-2-2-order-statuses-plugin-compatibility/
Let me know your findings.
-Jibran
Hi pedro , can you please help me i usted your script but sql is returning NULL can you please advise
Thank you
I’d there a way to tweak this to include the order status?
Hi Pero , can you please help me , i used your query but i get NULL as the result can you please
advise
Thank you
Where are stored information about ordered products which I can match to particular order retrieved using query above? I have tried to use data from wp_woocommerce_order_items table but I have no idea what correlation is between order posts and order_items.
Hi Adam, sorry it took me so long to respond to you. The correlation is
p.IDin above query andorder_idinwp_woocommerce_order_items.I figured out a solution to output pipe delimited product names to a new column, which demonstrates their relationship. Try this:
It doesn’t however provide you with quantity, price, etc. That information is in the wp_woocommerce_order_itemmeta (
order_item_idis the column to key off).Hope that helps.
How to use this in a foreach? Very good article 🙂
Hi Marcos, take a look at
$wpdb. Here’s a starting point.$rows = $wpdb->get_results( "select id, name from table" ); foreach( $rows as $row ) { var_dump($row) }Make sure to send your SQL statement through
$wpdb->prepare, especially if you’re passing in user inputs.Hope that helps.
Hi,
How can I get the ordered items meta datas with this SQL query? 🙂
Hi Peter, take a look at my response to Adam. It should give you starting point. Good luck.
Thanks for the script, it saved me a ton of time. Very helpful!
You’re welcome. Glad it was useful.
You saved bro! I put as a view in MySQL to get this information more easily.
Hi!
I’m really a noob in SQL, and I would like to know how to export all of these data to a CSV file… Thank you 🙂
Hi there,
I’m keen to get the following data extracted – can you guide me?
order id, product category like shirt, MRP, Discount %, Actual sale price, dispatch date, courier tracking id, courier company, last update date, last update by (user).
Most are possible, but some aren’t.
Thanks!
Sam
Really thank you for this code .I need to download order report of woocommerce in csv format, for that i made the following query:
header(‘Content-Type: text/csv; charset=utf-8’);
header(‘Content-Disposition: attachment; filename=data.csv’);
// create a file pointer connected to the output stream
$output = fopen(‘php://output’, ‘w’);
fputcsv($output, array(‘ID’, ‘Date’, ‘Status’,’Name’));
$rows = mysql_query(‘SELECT ID,post_date,post_status,post_name FROM wp_posts WHERE post_date LIKE “%2016-03-30%”‘);
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
And this is just a trail and here i only fetch data from post table.
But i need to connect to *postmeta* and other tables, so that i can get all information about order.
I see your code ,and i am very thankful to you . The same situation i faced now . But i don’t know how to integrate with this with my code.
Please help to complete this using your code.
Currently i have a plugin, but that is working slow, that’s why i making this page.
Hi Manik, glad you found it useful!
I’d recommend using MySQLWorkbench or the like to export to a CSV.
http://stackoverflow.com/questions/17113812/how-to-export-table-data-in-mysql-workbench-to-csv
Another option is PHPMyAdmin. I haven’t used it to export to CSV but it may prove useful. Check with your host for how to connect.
Good luck!
-Jibran
thanks a ton
Hi can you please tell me how to export email Id of customers who have purchased a specific porduct. Thanks
Hi Tushar, good question. I don’t have time right now to figure this out but I’ve always wanted to.
For this report you’d likely need to query:
1. wc order items table to get orders (order_id) that have particular product line item. It would be a search by product name, which is not ideal but easier than joining multiple tables to search by product ID.
2. Take those order_id’s to query wp_postmeta (post_id) to get the users email which is stored as meta_key = _customer_email (I think).
Hope this helps.
Hi,
i want to get all the order data like all this data :
Order ID,Client Email,Ship Type,Notes(or comments if any),Payment method(Stored Cards, net due on delivery,credit card etc),CreatedDate,Updated Date,Shipping Charge,Payment Status,Total Amount,Order Status (pending ,processed, completed,dispatched etc.),Payment Profile Id (this is related to authorize.net),Shipping Address, -Address1, -Address2, -shipping_country, -shipping_state, -postcode,
From woocommerce and want to create Json of all these values. can you let me know how can i get all this data.
regards,
Rohit
cool…. thanks jibran
Have you created a script for the same or maybe some sql query?
Hi Tusharattar, I do not understand your question. Please elaborate. Thx. -J
You bet.
thanks very handy, gave me a good starting point for another woo query I needed to write
You’re welcome—glad it was useful.
Hi – just wondering if each of the lines beginning with max, e.g.
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
could be replaced with a join statement instead?
Hi Steve, thanks for your comments. Can you elaborate and give me an example of the join you have in mind? Or do you mean select? If so, I didn’t want a hundred sub-queries bogging it down. Let me know!
Also just curious – why do you use the Max function on text strings like the email address, first name etc?
Good question. It’s been a while since I wrote this and it does seem weird to use max() on strings. I’m guessin I found an example on the interwebs and it worked and got me away from a bunch of sub-queries. I just played around with it again and removing max() returns NULL in every one of those fields. If you have any ideas on improving I’m happy to update. -J
Great script. I didn’t see where we could change the script for a particular ID though. Was that asked somewhere in comments? I’d love to be able to pull only one product and not all products if need be.
Thanks.
Hi Stan, I haven’t been able to query by product ID, but I just made an update to query by product name. See above. Let me know if there are any issues. -J
hai……
now i am developing the woo-commerce website ,i want to add the quick buy option without using plugin .now i create page template for quick buy option and its has same fields of checkout page ,tell me how to store the my new checkout form data into wordpress databse? .
Hi Shivkumar, sorry but your question isn’t related to this post and sounds complicated. I’d recommend you look at overriding WooCommerce’s template files.
https://docs.woocommerce.com/document/template-structure/
With inspiration from this post, I managed to put together an SQL script, that exports completed orders from pre-2.2 versions of WooCommerce. I’ve written about it here:
https://www.omkalfatring.dk/export-orders-to-csv-from-woocommerce-versions-before-2-2/
Nice, thanks for the contribution!
Thank you very much. I’ve been looking for this.. Hugs & Kisses from me.. XD
How can I get coupons code that order has been used !
Did you figure this out? Anyone else know how to get the coupon code used?
Using left join is much faster
SELECT p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = ‘_billing_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = ‘_billing_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = ‘_billing_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = ‘_billing_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = ‘_billing_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = ‘_shipping_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = ‘_shipping_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = ‘_shipping_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = ‘_shipping_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = ‘_shipping_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = ‘_shipping_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = ‘_shipping_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘_order_tax’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator ‘|’ ) from pwp_woocommerce_order_items where order_id = p.ID ) as order_items
FROM
`pwp_posts` p LEFT JOIN pwp_postmeta pm ON (pm.post_id = p.ID )
LEFT JOIN pwp_woocommerce_order_items oi ON (p.ID = oi.order_id)
WHERE
p.post_type = ‘shop_order’
group by p.ID
Hi and happy thanks giving. I found this post when searching on how to export all of our customer database info and order info with SQL. I tried searching for the plugin mentioned but I believe the name has changed, any chance you could confirm what the current plugin is for this query? Is there a basic how to on how to run this script within the plugin?
Thanks!
Hi Myles,
Yes, happy holidays. The plugin to which I was referring is this one:
https://woocommerce.com/products/ordercustomer-csv-export/
However, the script in this post is separate from the plugin and I don’t believe you can run your own scripts from it. Good luck.
Your code is awesome. I changed it a little to add some additional metafields I added but I was hoping to add to the products ordered at the end with a size variation. The meta_key value for that table is Size. Here is was I was trying:
select
p.ID as order_id,
p.post_date,
p.post_status,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = ‘_billing_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = ‘_billing_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = ‘_billing_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = ‘_billing_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = ‘_billing_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = ‘_shipping_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = ‘_shipping_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = ‘_shipping_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = ‘_shipping_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = ‘_shipping_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = ‘_shipping_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = ‘_shipping_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘_order_tax’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
max( CASE WHEN pm.meta_key = ‘Pickup Persons First Name’ and p.ID = pm.post_id THEN pm.meta_value END ) as pickup_persons_first_name,
max( CASE WHEN pm.meta_key = ‘Pickup Persons Last Name’ and p.ID = pm.post_id THEN pm.meta_value END ) as pickup_persons_last_name,
max( CASE WHEN pm.meta_key = ‘Troop Number’ and p.ID = pm.post_id THEN pm.meta_value END ) as troop_number,
( select group_concat( CONCAT(order_item_name,’ ‘, (CASE WHEN oim.meta_key = ‘Size’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value End)) separator ‘|’ ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
group by
p.ID
Thanks Shauny. Did your size addition work?
Thanks a lot for this wonderful code !
Instead of the pipe delimited product names, how do you think it would be possible that each product got is own line ? Repeating the order meta (ID, billing, etc) could achieve this I think.
I finally ended up with this code to get the items and how many of them listed :
SELECT oi.order_id AS order_id,
po.post_date AS date,
oi.order_item_name AS item,
CASE WHEN oim.meta_key = ‘_qty’ and oim.meta_value IS NOT NULL THEN oim.meta_value end as qty
FROM wp_woocommerce_order_items AS oi
LEFT JOIN wp_woocommerce_order_itemmeta AS oim
ON oi.order_item_id = oim.order_item_id
LEFT JOIN wp_posts AS po
ON po.id = oi.order_id
WHERE oim.meta_key = ‘_qty’ and
po.post_status ‘trash’
ORDER BY oi.order_item_id ASC
I don’t know if this is the clean way to do it but it gives me what i need. The next step would be mixing your query with this one to get the wp_postmeta field ont the same row but i didn’t find out how and even if it is possible.
Anyway, I can continue coding with these requests. Thanks a lot!
Sorry for the multiple reply, i don’t know how to edit the previous one.
With the help of your code, I found it, thanks a lot :
SELECT oi.order_item_id AS order_item_id,
oi.order_id AS order_id,
oi.order_item_name AS item,
max( CASE WHEN oim.meta_key = ‘_qty’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value END ) as qte,
max( CASE WHEN oim.meta_key = ‘_line_total’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value END ) as item_total,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as nom,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as courriel,
max( CASE WHEN pm.meta_key = ‘_order_total’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as order_total
FROM wp_woocommerce_order_items AS oi
LEFT JOIN wp_woocommerce_order_itemmeta AS oim
ON oi.order_item_id = oim.order_item_id
left join wp_postmeta AS pm on oi.order_id = pm.post_id
left join wp_posts AS p on oi.order_id = p.ID
GROUP BY oi.order_item_id ASC
This is what I used to select meta contents and order number. Other cols can be added.
SELECT
a.order_item_id,
a.order_id,
b.order_item_id,
b.meta_key,
b.meta_value
FROM wp_woocommerce_order_items a LEFT JOIN
wp_woocommerce_order_itemmeta b on a.order_item_id = b.order_item_id WHERE b.meta_key LIKE ‘%Bottle%’
Hi everybody this thread has been super useful, thanks!
I’m having a bit of trouble creating this table. My “order Items” don’t show up. I don’t think I’m doing the JOIN correctly, would someone be able to set me straight?
I’m hoping to get a table with First Name, Last Name, Order Total, and Order Item.
Thanks in advance!
SELECT
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘First Name’,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘Last Name’,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘Order Total’,
max( CASE WHEN pm.meta_key = ‘order_item_name’ and p.ID = pm.post_id THEN oi.order_item_name END ) as ‘Order Item’,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘Paid Date’,
max( CASE WHEN p.ID = wptr.object_id THEN wptr.term_taxonomy_id END ) as status
FROM
wp_posts AS p,
wp_term_relationships AS wptr,
wp_postmeta AS pm
INNER JOIN wp_woocommerce_order_items as oi ON pm.post_id = oi.order_id
WHERE 1=1 AND
post_type = ‘shop_order’ AND p.ID = pm.post_id
GROUP BY
p.ID DESC
Hi all,
reading some of your queries I created the one I’m using now, that query solves me a lot of problem, but not all:
1) the “product_item” field shows not only the product, but the product + attributes variation. How can I get these 2 parts in 2 separated columns?
2) the “order_total” field sometimes is incorrect and appear in a terrible format (exported in excel is a datetime :-/ )
Could you please help me in fix the issues above?
select
p.post_date,
max( CASE WHEN oim.meta_key = ‘_qty’ THEN oim.meta_value END ) as qty,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as last_name,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
min(order_item_name) as product_item,
( select group_concat( order_item_name separator ‘|’ ) from wper_2_woocommerce_order_items where order_id = p.ID ) as order_items,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘PayPal Transaction Fee’ and p.ID = pm.post_id THEN pm.meta_value END ) as Paypal_fee,
max( CASE WHEN pm.meta_key = ‘_cart_discount’ and p.ID = pm.post_id THEN pm.meta_value END ) as cart_discount,
max( CASE WHEN pm.meta_key = ‘_order_key’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_key,
p.ID as order_id,
max( CASE WHEN pm.meta_key = ‘_payment_method’ and p.ID = pm.post_id THEN pm.meta_value END ) as payment_method,
max( CASE WHEN pm.meta_key = ‘_cart_hash’ and p.ID = pm.post_id THEN pm.meta_value END ) as cart_hash,
max( CASE WHEN pm.meta_key = ‘_transaction_id’ and p.ID = pm.post_id THEN pm.meta_value END ) as transaction_id,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
max( CASE WHEN oim.meta_key = ‘_product_id’ THEN oim.meta_value END ) as product_id,
max( CASE WHEN oim.meta_key = ‘_variation_id’ THEN oim.meta_value END ) as variation_id,
post_status,
CAST(DATE_FORMAT(p.post_date,’%Y%m%d’) AS UNSIGNED) as yearmonthday
from
wper_2_posts p
join wper_2_postmeta pm on p.ID = pm.post_id
join wper_2_woocommerce_order_items oi on p.ID = oi.order_id
join wper_2_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
group by
p.ID
having yearmonthday > ‘20171016’
Hi, We have a website where the user must prove they are over 18 by way of uploading ID photo to either the order or their user account. I am trying to link to the user_meta table and I’m using the below sql. However the values from this table always appear as null on my query when I know there is data in there. I’m not great with sql so any help would be much appreciated, here’s the query:
select
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = ‘_billing_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = ‘_billing_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = ‘_billing_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = ‘_billing_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = ‘_billing_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = ‘_shipping_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = ‘_shipping_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = ‘_shipping_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = ‘_shipping_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = ‘_shipping_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = ‘_shipping_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = ‘_shipping_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘_order_tax’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
max( CASE WHEN pm.meta_key = ‘_woo_ufdc_uploaded_file_path_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as ID_upload,
max( CASE WHEN pm.meta_key = ‘_customer_user’ and p.ID = pm.post_id THEN pm.meta_value END) as customer_user,
max( CASE WHEN u.meta_key = ‘make_verify’ AND “customer_user” = u.user_id THEN u.meta_value END) as verified,
max( CASE WHEN u.meta_key = ‘user_uploaded_id’ AND “customer_user” = u.user_id THEN u.meta_value END) as user_upload,
( select group_concat( order_item_name separator ‘|’ ) from wp_woocommerce_order_items where order_id = p.ID AND order_item_name != “UK Delivery”) as order_items
from
wp_posts p
LEFT join wp_postmeta pm on p.ID = pm.post_id
LEFT join wp_woocommerce_order_items oi on p.ID = oi.order_id
LEFT join wp_usermeta u on “customer_user” = u.user_id
WHERE
p.post_status = ‘wc-processing’ AND
oi.order_id > 54930
GROUP BY p.ID
Hi, I have SQL below that works when I ran it from mySQL query but I get BAD query when running it with php. Can someone help me change it to php?
select
p.ID as order_id,
p.post_date,post_status,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = ‘_billing_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = ‘_billing_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = ‘_billing_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = ‘_billing_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = ‘_billing_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = ‘_shipping_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = ‘_shipping_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = ‘_shipping_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = ‘_shipping_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = ‘_shipping_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = ‘_shipping_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = ‘_shipping_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘_order_tax’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
max( CASE WHEN oim.meta_key = ‘_qty’ and oi.order_item_id = oi.order_item_id THEN oim.meta_value END ) as Qty,
( select group_concat( order_item_name separator ‘|’ ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
where
post_type = ‘shop_order’
group by
p.ID
So how do you take this and import it back into a new database?
Good job! How can I get with this query all the orders of a certain customer id?
Thanks a lot for the snippet! ?
This was so amazingly helpful!! Thanks!
Thank you, this saved me a lot of time!
Excellent, I am collecting and publishing all related SQL queries https://www.crawlspider.com/where-to-find-products-in-woocommerce-database-tables/
Thank you! This helped me alot in a pinch!