Mysql Question posted in
The official MySQL documentation can be found here.

PHP Show MySQL Search result must contain exact keywords but in any order

I am using this php code for MySQL database query:

function search_it() {
    if ( isset( $_GET['s'] ) ) {
        global $wpdb;
        $address_table = $wpdb->prefix . 'my_products';
        $search = $_GET['s'];
    $search = trim($search);         
    $keywords = explode(" ", $search);     
    foreach($keywords as $search) {
        $search = "%{$search}%";
        $where = $wpdb->prepare( 'WHERE name LIKE %s OR price LIKE %d OR id LIKE %d OR market_price LIKE %d OR image_url LIKE %s LIMIT 0,30' , $search, $search, $search, $search, $search );
        }   
    $results = $wpdb->get_results( "SELECT * FROM {$address_table} {$where}" );
        return $results;
    }}

for example my database have these lines

This is a blue car
This is a white car
This is a red car
This car color is blue
This is a yellow car
This is a blue bike
This car name is bluebird
Blue car looks awesome

When i execute the above code, with query blue car
then it will show me all results which contain CAR and all results which contain BLUE
But i want it show me the only results which contain keyword BLUE and CAR even these keywords are saved in any order in database

I want it show me only these results, when i search for blue car

This is a blue car 
This car color is blue
This car name is bluebird
Blue car looks awesome

Above keywords are just for example it would any

Tags:

2

Answers


  1. 0

    The code structure has issues. You are making an array of a parameters and then you make a where statement per element int the array but you’re only using the last where statement in the loop.

    I indented you code for better readability…

    function search_it() {
    if ( isset( $_GET['s'] ) ) {
        global $wpdb;
        $address_table = $wpdb->prefix . 'my_products';
        $search = $_GET['s'];
        $search = trim($search);         
        $keywords = explode(" ", $search);     
        foreach($keywords as $search) {
            $search = "%{$search}%";
            //The $where variable only lives inside this block
            $where = $wpdb->prepare( 'WHERE name LIKE %s OR price LIKE %d OR id LIKE %d OR market_price LIKE %d OR image_url LIKE %s LIMIT 0,30' , $search, $search, $search, $search, $search );
        }   
        //Why you don't get an error over here?
        $results = $wpdb->get_results( "SELECT * FROM {$address_table} {$where}" );
        return $results; 
    }
}

    PD: If you’re using a frame work, let us know.

    Login or Signup to reply.
  2. 0

    I’m not sure if your code is even working successfully or not, because your where clause is going to be repeated with each iteration in your foreach loop. However, according to your given dataset as an example, you can reach the desired output by replacing your OR by AND.

    Something like this will work:

    SELECT * FROM `cars` where (LOWER(`text`) LIKE "%car%" and LOWER(`text`) LIKE "%blue%");

    Also, you can reach the same results using REGEXP, in this case you will need to build up your pattern carefully by building up all possible variations.

    SELECT * FROM `cars` where LOWER(`text`) REGEXP "(car.*blue)|(blue.*car)"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search