0

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
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%.

  • Hey, just updated the OP. – Daniel Foust May 6 at 12:42
  • The %s isn't getting replaced. Are you passing an array of values as the second parameter of $wpdb->prepare()? – MikeNGarrett May 6 at 13:32
  • Sorry, that was a mistake on my end. I changed it back and reimplemented it and forgot to prepare it. It's now putting in the values, but still throwing a syntax error. OP updated again. Seems to be an issue with SKUs that have single quotes. – Daniel Foust May 6 at 14:33
  • 1
    Your single quotes are getting double-escaped. Your like statements should also be wrapped in single quotes. – MikeNGarrett May 6 at 14:43
  • 1
    That was it. I appreciate your patience. – Daniel Foust May 6 at 14: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.