0

My customer has a WooCommerce product catalog that uses a number of different naming conventions. A part commonly referred to as D100 could actually be named D100, D100-BLACK, D100BLACK, D100 BLACK, D100 black, d100-black, D100BLACK+, D-100BLACK,.... When a user enters D100 (or any of the aforementioned naming conventions) into the search form, I want to search for all the naming conventions above. I came up with a regular expression that captures all naming conventions in use, which is

/^([a-zA-Z#]+)([ -]*)([TA]*)([0-9]+)([ -])*([a-zA-Z+]*)/

My idea was to modify the search term using a regular expression instead of a fixed string. Here's the skeleton code to alter the MySQL WHERE clause:

function rb_replace_search($where) {
    global $wp_query;
    // don't do anything is this is not a user search
    if ( is_admin() || ! is_search() ) {
        return $where;
    }
    
    // all valid manufacturer IDs
    $manufacturers = array("#", "S","LP", "RT", "WM", "D", "M", "JSDE", "GH", "C", "GZ", "DE", "XRE", "FS", "NB");
    $raw_search = $wp_query->query['s'];
    // check whether search phrase has format of part search (e.g. LP00012345 or D752-black+)
    if ( preg_match("/^([a-zA-Z#]+)([ -]*)([TA]*)([0-9]+)([ -])*([a-zA-Z+]*)/", $raw_search, $matches) ) {
        // check whether manufacturer ID extracted from search string actually matches valid manufacturers IDs (e.g. D, LP,...)
        if ( in_array($matches[1], $manufacturers) ) {
            // build pattern to look for
            // now replace WHERE clause in SQL
            // missing code goes here
        }
    }
    return $where;
}
add_filter('posts_where', 'rb_replace_search');

The issue is that $where contains hashes and looks like this (when searching for D752-black):

 AND ( 
  wp_posts.ID NOT IN (
                SELECT object_id
                FROM wp_term_relationships
                WHERE term_taxonomy_id IN (253)
            )
) AND (((wp_posts.post_title LIKE '{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}D752-black{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}') OR (wp_posts.post_excerpt LIKE '{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}D752-black{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}') OR (wp_posts.post_content LIKE '{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}D752-black{a217cfaff5b23293c55a56201a470ed5706ca27404fb6e580bc7f0bbcc56ba8c}')))  AND (wp_posts.post_password = '')  AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish')

Seeing the hashes makes me a bit vary...am I on the right path? Should I replace the string after LIKE with strings including wildcards to cover my naming convention? Or should I replace the LIKE clause with REGEXP_LIKE clauses covering my naming conventions? Any other ideas?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.