如何在WordPress的draft_to_publish钩子中正确编写清理指定wp_postmeta缓存的SQL查询？

Great question! When working with WordPress, it’s critical to use the built-in wpdb database abstraction layer instead of raw SQL queries—this avoids SQL injection risks, handles dynamic table prefixes automatically, and ensures compatibility across different site setups. Here’s how to safely integrate your cleanup logic into the draft_to_publish hook:

Complete Working Code #

function mepr_clear_cached_ids($post) {
    // Only execute for the memberpressproduct post type
    $post_type = get_post_type($post);
    if (!$post_type || $post_type !== 'memberpressproduct') {
        return;
    }

    global $wpdb;

    // 1. Replace with your actual alphanumeric gateway ID
    $gateway_id = 'your-gateway-id-123';
    // 2. Replace with your target membership IDs (array of integers)
    // Example: Pull related memberships from the product's post meta:
    // $membership_ids = get_post_meta($post->ID, 'mepr_related_memberships', true);
    // Or hardcode if needed (not recommended for dynamic setups):
    $membership_ids = array(45, 67, 89);

    // Validate membership IDs to ensure they're valid integers
    $membership_ids = array_filter($membership_ids, 'is_int');
    if (empty($membership_ids)) {
        return; // Exit early if no valid memberships to target
    }

    // Prepare LIKE patterns with escaped wildcard characters
    $escaped_gateway_id = $wpdb->esc_like($gateway_id);
    $meta_key_patterns = array(
        "_mepr_stripe_product_id_{$escaped_gateway_id}%",
        "_mepr_stripe_plan_id_{$escaped_gateway_id}%",
        "_mepr_stripe_tax_id_{$escaped_gateway_id}%",
        "_mepr_stripe_initial_payment_product_id_{$escaped_gateway_id}%",
        "_mepr_stripe_onetime_price_id_%" // No gateway ID needed for this pattern
    );

    // Build sanitized LIKE conditions
    $like_conditions = [];
    foreach ($meta_key_patterns as $pattern) {
        $like_conditions[] = $wpdb->prepare('meta_key LIKE %s', "%{$pattern}%");
    }
    $like_clause = implode(' OR ', $like_conditions);

    // Build sanitized IN clause for membership IDs
    $id_placeholders = implode(',', array_fill(0, count($membership_ids), '%d'));
    $in_clause = $wpdb->prepare("post_id IN ($id_placeholders)", ...$membership_ids);

    // Assemble and execute the final query
    $delete_query = "DELETE FROM {$wpdb->prefix}postmeta 
                     WHERE ($like_clause) AND $in_clause";
    $wpdb->query($delete_query);
}
add_action('draft_to_publish', 'mepr_clear_cached_ids');

Key Best Practices Explained #

• Security: We use $wpdb->prepare() to sanitize all dynamic values, eliminating SQL injection risks. $wpdb->esc_like() ensures wildcard characters (%, _) in your gateway ID don’t break the LIKE pattern matching.
• Dynamic Prefix: {$wpdb->prefix} automatically uses your site’s unique table prefix (instead of hardcoding wp_), which is essential for multisite or custom setups.
• Validation: We filter membership IDs to ensure they’re valid integers and exit early if there are no valid IDs—this avoids unnecessary database calls.
• Testing: Always test this on a staging site first! Replace DELETE with SELECT to verify the correct rows are targeted before making permanent changes:

  SELECT * FROM {$wpdb->prefix}postmeta 
  WHERE ($like_clause) AND $in_clause
  

内容的提问来源于stack exchange，提问作者Ilianskia