Get coupons code from email restriction using SQL query

Question

I am attempting to adapt How to get coupons from email restrictions with efficiency in WooCommerce. I want to filter only for the customer email, without the voucher discount type. Currently my shop uses multiple discount types (free product, fixed cart discount etc.)

I tried to delete these line but fail to produce the result I want.

INNER JOIN 
    {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
                               AND pm.meta_key = 'discount_type'
                               AND pm.meta_value = '%s'

How can I filter on only email whilst disregarding discount type?

give the actual and expected results, if now it produces not the expected ones — Suncatcher
– Suncatcher, Commented Jul 14 at 14:31
After some digging & try error, I manage to find the answer myself as below. — Weetameal
– Weetameal, Commented Jul 14 at 14:33
if you did, please post the answer yourself to give hint to other folks with similar problems — Suncatcher
– Suncatcher, Commented Jul 14 at 14:37

Friedrich · Accepted Answer · 2025-07-14 15:03:33Z

1

function get_coupons_from_email( $current_email  ) {
    global $wpdb;

    return $wpdb->get_col( $wpdb->prepare("
        SELECT p.post_name
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm
            ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_coupon'
            AND pm.meta_key = 'customer_email'
            AND pm.meta_value LIKE '%s'
        ORDER BY p.post_name DESC
    ", '%'.$current_email.'%' ) );
}

After some self reading, I manage to find the answer I need. The meta value for LIKE must use the symbol % value % which I don't know earlier. I have mistakenly deleted the symbol %.

edited Jul 14 at 15:03

Friedrich

5,45715 gold badges82 silver badges62 bronze badges

answered Jul 14 at 14:39

Weetameal

213 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Barmar Jul 14 at 19:20

You would only need that if you don't need to match the email exactly. Why is that necessary?

Weetameal Jul 15 at 7:10

I tried to use '=' to match exact email. Unfortunately return with no result. Only with LIKE will return value.

Barmar Jul 15 at 14:27

You'll get false matches. E.g. if $current_email is [email protected] you'll match [email protected]. Is meta_value a comma-separated list? Then you should use FIND_IN_SET().

Weetameal Jul 16 at 15:54

You're correct. I did not see that. Meta_value is single unique value.

Barmar Jul 16 at 15:55

Please update the question with sample data where LIKE works but = doesn't.

Weetameal · Accepted Answer · 2025-07-21 12:54:17Z

I tried another way. I added a meta for the coupon & query for the meta from SQL.

$coupon_code = 'coupon_'.date('ish');
$coupon = new WC_Coupon();
$coupon->set_code( $coupon_code );
$coupon->set_status( 'publish' );
$coupon->set_email_restrictions( 
    array($email,) );
$coupon->set_amount( $amount );
$coupon->set_discount_type( 'fixed_cart' );
$coupon->set_description( "Apply this code at check out page!");                            $coupon->set_individual_use( true );                            $coupon->set_usage_limit( 1 );                          
$coupon->set_usage_limit_per_user( 1 );                         
$coupon->set_date_expires( strtotime('+12 months') );                           $coupon->update_meta_data( 'coupon_customer_id', $user_id ); //this is what I add based customer ID//
$coupon->save();

then I will query via SQL : 

function get_coupons_from_id( $current_id  ) {
    global $wpdb;
    return $wpdb->get_col( $wpdb->prepare("
        SELECT p.post_name
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm
            ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_coupon'
            AND pm.meta_key = 'coupon_customer_id'
            AND pm.meta_value = '%s'
        ORDER BY p.post_name DESC
    ", $current_id ) );
}

//function will list all the coupon based on current user ID

function coupon_list() {
    $current_user = wp_get_current_user();
    $coupon_codes = get_coupons_from_id( $current_user->ID  );
    
if ( count($coupon_codes) > 0 ) {
    // Loop through smart coupons code
    
    foreach ( $coupon_codes as $coupon_code ) {
        $coupon = new WC_Coupon( $coupon_code ); // Get the WC_Coupon Object
        if ( ( $coupon->get_usage_count() < $coupon->get_usage_limit() ) && (date('Y-m-d') < $coupon->get_date_expires() ) ) { 
            //content
        }
    }
}
    else {
        echo "You currently have no reward voucher. ";
    }
}

Collectives™ on Stack Overflow

Get coupons code from email restriction using SQL query

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related