What is the difference (if any) between:

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

and

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

What is the difference (if any) between:

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

and

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

• wp-query
• query

1 Answer 1

Firtly, in SQL the % symbol is a "wildcard operator". When used in a LIKE statement, it means "0 or more characters". So LIKE 'Hello%' means any string beginning with Hello, including just Hello. While LIKE '%Hello%' means any string that contains Hello, either at the beginning, middle, or end.

$wpdb->prepare() on the other hand uses the % symbol to support placeholders, with %d and %s. It allows you to safely insert a variable into a query.

So the problem is that the % has two different meanings, which will give you trouble if you try to $wpdb->prepare() a LIKE query.

The solution is to 'escape' the % symbol by using the character twice, like %%. This tells $wpdb->prepare() that the % symbol is a literal % symbol, and not part of a placeholder. This is how you use the % symbol in a LIKE query with a placeholder.

So in your first example:

LIKE '%%%s%%'

If $code is Hello, then the resulting SQL statement is:

LIKE '%Hello%'

Because %s has been substituted with $code, and the double %% have been interpreted as %, leaving us with a valid LIKE statement for any string that contains Hello, either at the beginning, middle, or end.

Your other example on the other hand:

LIKE '%s%'

Would not work. Because %s will be substituted with $code, but a stray % is remaining that will appear as a broken placeholder. If you only want to find strings beginning with $code, you would need to use:

LIKE '%s%%'

Which would give you:

LIKE 'Hello%'