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));
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%'
