I am looping through an array of products and want to create an OR string with the product skus.

From my understanding, you pass a query and the variables to replace the placeholders in the SQL. So a simplified example would like this.

$sql = "SELECT id, sku FROM document_product 
        WHERE 1=1
        AND sku LIKE %s
        OR sku LIKE %s
        OR sku LIKE %s";

$prep_sql = $wpdb->prepare($sql, ['42ts', '55zb', '66bc']);

$results = $wpdb->query($prep_sql, ARRAY_A);

That's my understanding. So I have block doing this:

$skus = [];
$skus_like = [];
foreach($matchedProducts as $matchedProduct) {
    $skus[] = $matchedProduct->sku;
    $skus_like[] = "product_sku LIKE %%%s%%";
}

$sql = "SELECT document_id, product_sku
        FROM document_product
        WHERE 1 = 1 AND (" . implode(' OR ', $skus_like) . ")";

$prep_sql = $wpdb->prepare($sql, $skus);

$product_document_results = $wpdb->query($prep_sql, ARRAY_A);

And I am getting back mysqli_query(): Empty query

Edit: Current Error

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near


'%WPR2-100-10\\'% 
OR product_sku LIKE %WPR2-100-10\\'-LCD% 
OR product_sku LIKE %WPR' 

at line 3 for query 

SELECT document_id, product_sku
FROM document_product
WHERE 1 = 1
AND (product_sku LIKE %APR2-100-10\\'% 
OR product_sku LIKE %APTR2-100-10\\'-BCD% 
OR product_sku LIKE %APTR2-100-20\\'% 
OR product_sku LIKE %APTR2-100-20\\'-BCD% 
OR product_sku LIKE %APTR2-30-10\\'% 
OR product_sku LIKE %APTR2-30-10\\'-BCD% 
OR product_sku LIKE %APTR2-30-20\\'% 
OR product_sku LIKE %APTR2-30-20\\'-BCD% 
OR product_sku LIKE %APTR2-300-10\\'% 
OR product_sku LIKE %APTR2-300-10\\'-BCD% 
OR product_sku LIKE %APTR2-300-20\\'% 
OR product_sku LIKE %APTR2-300-20\\'-BCD%) 

made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/the-theme/search.php'), search_documents

I am looping through an array of products and want to create an OR string with the product skus.

From my understanding, you pass a query and the variables to replace the placeholders in the SQL. So a simplified example would like this.

$sql = "SELECT id, sku FROM document_product 
        WHERE 1=1
        AND sku LIKE %s
        OR sku LIKE %s
        OR sku LIKE %s";

$prep_sql = $wpdb->prepare($sql, ['42ts', '55zb', '66bc']);

$results = $wpdb->query($prep_sql, ARRAY_A);

That's my understanding. So I have block doing this:

$skus = [];
$skus_like = [];
foreach($matchedProducts as $matchedProduct) {
    $skus[] = $matchedProduct->sku;
    $skus_like[] = "product_sku LIKE %%%s%%";
}

$sql = "SELECT document_id, product_sku
        FROM document_product
        WHERE 1 = 1 AND (" . implode(' OR ', $skus_like) . ")";

$prep_sql = $wpdb->prepare($sql, $skus);

$product_document_results = $wpdb->query($prep_sql, ARRAY_A);

And I am getting back mysqli_query(): Empty query

Edit: Current Error

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near


'%WPR2-100-10\\'% 
OR product_sku LIKE %WPR2-100-10\\'-LCD% 
OR product_sku LIKE %WPR' 

at line 3 for query 

SELECT document_id, product_sku
FROM document_product
WHERE 1 = 1
AND (product_sku LIKE %APR2-100-10\\'% 
OR product_sku LIKE %APTR2-100-10\\'-BCD% 
OR product_sku LIKE %APTR2-100-20\\'% 
OR product_sku LIKE %APTR2-100-20\\'-BCD% 
OR product_sku LIKE %APTR2-30-10\\'% 
OR product_sku LIKE %APTR2-30-10\\'-BCD% 
OR product_sku LIKE %APTR2-30-20\\'% 
OR product_sku LIKE %APTR2-30-20\\'-BCD% 
OR product_sku LIKE %APTR2-300-10\\'% 
OR product_sku LIKE %APTR2-300-10\\'-BCD% 
OR product_sku LIKE %APTR2-300-20\\'% 
OR product_sku LIKE %APTR2-300-20\\'-BCD%) 

made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/the-theme/search.php'), search_documents

• wpdb

1 Answer 1

Not quite there. In your first example you have sku LIKE %s and in your example you have product_sku LIKE '%{$matchedProduct->sku}%'. That's not the same.

$wpdb->prepare() uses sprintf replacements. That's where you get the %s in your first query. If you look at the sprintf documentation under specifiers, you'll find a list of replacement patterns. $wpdb->prepare() uses the same specifiers, for example %s for a string, %d for an integer, etc.

In your case, you probably want to use product_sku LIKE %%%s%%.

Why did you add extra % signs?

With printf() and sprintf() functions, escape character is not backslash \ but rather %.

That means %% is output as % and %s is output as 42ts (for example). Altogether the output looks like product_sku LIKE %42ts%.