How WooCommerce Coupons are stored in Database?
14,826
WooCommerce Coupon are stored in
wp_posts
table withpost_type => 'shop_coupon'
. And all the associated data are stored inwp_postmeta
table.
You can use this MySQL query to get all the Coupon:
SELECT * FROM `wp_posts` WHERE `post_type` = 'shop_coupon' ORDER BY `ID` DESC
And this gets all coupons
SELECT p.`ID`,
p.`post_title` AS coupon_code,
p.`post_excerpt` AS coupon_description,
Max(CASE WHEN pm.meta_key = 'discount_type' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS discount_type, -- Discount type
Max(CASE WHEN pm.meta_key = 'coupon_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS coupon_amount, -- Coupon amount
Max(CASE WHEN pm.meta_key = 'free_shipping' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS free_shipping, -- Allow free shipping
Max(CASE WHEN pm.meta_key = 'expiry_date' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS expiry_date, -- Coupon expiry date
Max(CASE WHEN pm.meta_key = 'minimum_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS minimum_amount, -- Minimum spend
Max(CASE WHEN pm.meta_key = 'maximum_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS maximum_amount, -- Maximum spend
Max(CASE WHEN pm.meta_key = 'individual_use' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS individual_use, -- Individual use only
Max(CASE WHEN pm.meta_key = 'exclude_sale_items' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_sale_items, -- Exclude sale items
Max(CASE WHEN pm.meta_key = 'product_ids' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_ids, -- Products
Max(CASE WHEN pm.meta_key = 'exclude_product_ids'AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_ids, -- Exclude products
Max(CASE WHEN pm.meta_key = 'product_categories' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_categories, -- Product categories
Max(CASE WHEN pm.meta_key = 'exclude_product_categories' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_categories,-- Exclude Product categories
Max(CASE WHEN pm.meta_key = 'customer_email' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS customer_email, -- Email restrictions
Max(CASE WHEN pm.meta_key = 'usage_limit' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit, -- Usage limit per coupon
Max(CASE WHEN pm.meta_key = 'usage_limit_per_user' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit_per_user, -- Usage limit per user
Max(CASE WHEN pm.meta_key = 'usage_count' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS total_usaged -- Usage count
FROM `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm ON p.`ID` = pm.`post_id`
WHERE p.`post_type` = 'shop_coupon'
AND p.`post_status` = 'publish'
GROUP BY p.`ID`
ORDER BY p.`ID` ASC;
Reference: MySQL Query to Get All Coupon
Related videos on Youtube
Author by
lufizi
Updated on May 16, 2022Comments
-
lufizi about 2 years
I need to run a script to check my coupons every time I have a new register, but I can't find where the coupons from coupon generator (WooCommerce) are stored.
-
Deepak Shenoy over 6 yearsAny idea where the expiry date might be?
-
Raunak Gupta over 6 years@DeepakShenoy: expiry date is stored in
wp_postmeta
table against thepost_id
andmeta_key = expiry_date
. Please check the reference link attached with the answer. -
Schalk Joubert over 5 yearsHi, sorry for duplicate comment, left one on your website. Is it possible to retrieve order meta of the user that used the coupon? My coupon usage limit is set to 1, for all coupons. I would like ti include in the report the user's name, email address and possibly some other custom fields. Thank you.
-
Raunak Gupta over 5 yearsHi @user1664798: As this question is not directly related with your question so I cannot update my answer, it would be grt if you can post a new question and ping me the link I'll try to figure out a solution, in a mean time you can check this ans.
-
Schalk Joubert over 5 years@RaunakGupta I have posted my question here. Hope this is how I am suppose to ping you? Thank you very much. stackoverflow.com/questions/52763254/…