Run query inside WP hook?

Question

I want to use the hook from below. The hook will run when a draft is published

function mepr_clear_cached_ids($post) {
  $post_type = get_post_type($post);

  if($post_type && $post_type == 'memberpressproduct') {
    // Run query here.
  }
}
add_action('draft_to_publish', 'mepr_clear_cached_ids');

inside the hook I want to run that query to clear the database cache of the fields on the wp_postmeta table :

DELETE FROM [prefix]_postmeta WHERE 
  (
    meta_key LIKE '_mepr_stripe_product_id_[gateway-id]%' OR 
    meta_key LIKE '_mepr_stripe_plan_id_[gateway-id]%' OR 
    meta_key LIKE '_mepr_stripe_tax_id_[gateway-id]%' OR 
    meta_key LIKE '_mepr_stripe_initial_payment_product_id_[gateway-id]%' OR 
    meta_key LIKE '_mepr_stripe_onetime_price_id_%'
  )
  AND post_id IN ([memberships])

[gateway-id] - it will be replaced by the gateway id. it's a letters and numbers id. How should the correct and best syntaxis of the query from above be added inside the hook?

Answer 1

This is a job for $wpdb->esc_like(), $wpdb->prepare(), and $wpdb->query(), and implode().

I assume your [gateway_id] value is in a variable called $gateway_id. And I assume your [memberships] are in an array of integers called $memberships.

You probably want to do something like this to build up and then run your query.

/* a list of the meta keys */
$metaKeysToDelete = [
  "_mepr_stripe_product_id_{$gateway_id}%",
  "_mepr_stripe_plan_id_{$gateway_id}%", 
  "_mepr_stripe_tax_id_{$gateway_id}%".
  "_mepr_stripe_initial_payment_product_id_{$gateway_id}%", 
  "_mepr_stripe_onetime_price_id_%",
];

/* construct the LIKE clauses from the meta keys, using esc_like */
$clauses = [];
foreach ( $metaKeysToDelete as $metaKey ) {
  $clauses [] = "meta_key LIKE '" . $wpdb->esc_like( $metaKey ) . '"';
}

/* construct the query */
/* debug! use SELECT * in place of DELETE to make sure you have it right */
$q = "DELETE FROM $wpdb->postmeta WHERE " 
       . "(" . implode( ' OR ', $clauses ) . ")"
       . AND post_id IN (" . implode( ',', $memberships ) . ")";
/* debug! make sure your query is correct. */ print_r( $q );

/* prepare and run the query */
$wpdb->query( $wpdb->prepare( $q ) );

Answer 2

Thanks for your reply

[gateway_id] value is not a variable but gateway id. it's a letters and numbers id- similar to dewhj2_3j3.

[memberships] will be a comma separate list of the memberships ids. If I run the query directly in the database with modified/correct data for wp prefix, gataway id and memberships ids that do the work for what the query is and it clears the metadata from the wp_postmata table for the metakeys from the query.

I want to automate the process so that when membership is published from the draft to clear the cache and the metadata for the meta keys from the query

Hope all the above makes sense.