How to make WordPress search more flexible?


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?

, , Robert Bethge 5 months 0 Answers 58 views 0

Leave an answer