1

It seems MySQL's LIKE operator behaves like a = operator.

The following MySQL query returns the expected result (1 entry):

$meta_key = '_locality';
$meta_value = 'The Hague';
$post_ids = $wpdb->get_col( $wpdb->prepare( 
  "
  SELECT      post_id
  FROM        $wpdb->postmeta
  WHERE       meta_key = %s
              AND meta_value LIKE %s
  ", 
  $meta_key, 
  $meta_value
) ); 

But the following returns an empty array:

$meta_key = '_locality';
$meta_value = 'The';
$post_ids = $wpdb->get_col( $wpdb->prepare( 
  "
  SELECT      post_id
  FROM        $wpdb->postmeta
  WHERE       meta_key = %s
              AND meta_value LIKE %s
  ", 
  $meta_key, 
  $meta_value
) ); 

What am I doing wrong here?

  • LIKE doesn't automatically do a substring match. Try "The%" rather than just "The" where % is a wildcard. – Rup Oct 9 '14 at 10:53
  • The title of this question is perhaps best read in Valleyspeak. :-) – Stephan Oct 9 '14 at 13:31
3

Try to replace:

AND meta_value LIKE %s

with

AND meta_value LIKE '%%%s%%'

so your SQL will become:

AND meta_value LIKE '%The%'

instead of:

AND meta_value LIKE 'The'
  • This is certainly correct, one thing I think should be noted is: WP_Query and WP_Meta_Query are by default constructing the SQL like this, if LIKE is given for compare. So generally raw SQL isn't needed to achieve this. – Nicolai Oct 9 '14 at 11:15
  • 1
    @ialocin yes I agree and it's a good point you make, usually there's no reason for constructing SQL by hand since the WP*Query classes are rather flexible. But it helps understanding the WordPress core (at least a tiny part of it) of course to know how to construct the SQL queries ;-) – birgire Oct 9 '14 at 11:22
  • 1
    true, just added it for others to know – Nicolai Oct 9 '14 at 11:32
  • the reason I needed this SQL query is that I need to make a query with multiple relations - one OR and then AND - which I believe is not possible with WP_Query – pdme Oct 10 '14 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.