MySQL LIKE statement supporting multiple queries

Question

Would like to find product names using partial matches, e.g if product_name is cat-in-the-hat and name is cat, then the ID will be returned. Currently, an exact match is required, i.e. a partial match is not possible.

A LIKE statement would typically work but we are using an IN statement in order to support a varying number of inputs. Per MySQL LIKE IN()? a REGEXP should also work but as mentioned the number of inputs vary. What would be the ideal way to perform matches for each name instance?

function woo_products_by_name_shortcode( $atts, $content = null ) {

    // Get attribuets
    extract(shortcode_atts(array(
        'name' => ''
    ), $atts));

    if( empty($name) ) return;

    // Format product slugs string
    $name = str_replace(",", "','", $name);

    global $wpdb;
    // Get the corresponding products ids for each name
    $ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->prefix}posts
        WHERE post_type = 'product' AND post_name IN('$name')" );

    ob_start();

    // Define Query Arguments
    $loop = new  WP_Query( array(
        'post_type'      => 'product',
        'posts_per_page' => -1,
        'post__in'       => $ids
    ));

    // Get products number
    $product_count = $loop->post_count;

    echo '<pre>'; print_r($loop->posts); echo '</pre>';

    return ob_get_clean();
}
add_shortcode("woo_products_by_name", "woo_products_by_name_shortcode");

Have you considered just using WHERE <condition> OR <condition> ...? — fubar
– fubar, Commented Mar 16, 2018 at 2:36
@fubar, yes, please see the linked question. In my case, I do not know how many conditions there will be. @anonyXmous isn't what you referenced similar do what I am already doing? Issue is I need to support LIKE matches for each IN match. — Astron
– Astron, Commented Mar 16, 2018 at 3:17
That shouldn't be an issue. Will your conditions be in an array or something? — fubar
– fubar, Commented Mar 16, 2018 at 3:19

Astron · Accepted Answer · 2020-03-31 15:11:50Z

0

I was unable to solve based on Array in Mysql WHERE LIKE?. Below is how I was able to make it work:

$array_name = explode(",", $atts['name']);

global $wpdb;
// Get the corresponding products ids for each name
$query_parts = array();
foreach ($array_name as $val) {
    $query_parts[] = "'%%".$val."%%'";
}

$string = implode(' OR post_name LIKE ', $query_parts);
$ids = $wpdb->get_col($wpdb->prepare( "SELECT ID FROM {$wpdb->prefix}posts
    WHERE post_type = 'product' AND post_name LIKE {$string}" ));

edited Mar 31, 2020 at 15:11

answered Mar 23, 2018 at 1:04

Astron

1,2216 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL LIKE statement supporting multiple queries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related