phpmysqlselectmatchagainst

MySql SELECT Query for Multiple Keywords across Multiple Fields


I need some help for the logic of a SELECT query and hope someone can give me a better idea than the one I have.

I have a table like this:

 +---------+----------+---------+-----------+
 |      id | model    | variant | category  |
 +---------+----------+---------+-----------+
 |       1 | round    | black   | fruit     |
 |       2 | square   | white   | vegetable |
 |       3 | flat     | red     | meat      |
 |       4 | round    | black   | meat      |
 +---------+----------+---------+-----------+

Users will be allowed to do a search on this table by entering one or more keywords.

The query I have right now does a MATCH on all entered keywords AGAINST each field and I get rows that match ANY of the keywords. So if the input is "round meat", the result will be rows 1, 3 and 4:

SELECT v.*, vt.color 
            FROM tbl_items 
            AS v 
            LEFT JOIN tbl_apple 
            AS vt 
            ON v.id = vt.v_id 
            WHERE ( 
            MATCH (v.model) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE) 
            OR MATCH (v.variant) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE) 
            OR MATCH (v.category) AGAINST ('keyword1 keyword2 ' IN BOOLEAN MODE) ) 
            AND v.type = 4

However, I need to only return the rows that contain ALL keywords. The keywords might be contained in any of the three columns.

So if the user enters "round meat", only row 4 is returned.

My current idea is very simple and probably not very good (I expect some performance issues for multiple keywords):

SELECT v.*, vt.color 
            FROM tbl_items 
            AS v 
            LEFT JOIN tbl_apple 
            AS vt 
            ON v.id = vt.v_id 
            WHERE 
                ( 
                     (v.model = 'keyword1' 
                   OR v.variant = 'keyword1' 
                   OR v.category = 'keyword1'
                     ) 
        AND 
                     (v.model = 'keyword2' 
                   OR v.variant = 'keyword2' 
                   OR v.category = 'keyword2'
                     )
                 ) 
            AND v.type = 4

Is there a better way to do it?


Solution

  • This is a whole code that may helps you." You can modify query according to database or requirements.

        <?php
    define('DB_SERVER', 'localhost');
    define('DB_USERNAME', 'root');
    define('DB_PASSWORD', '');
    define('DB_DATABASE', 'test');
    $db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
    
    if(isset($_POST['submit']))
    {
        $search_text = $_POST['text'];
    
        $values = explode(" ",$search_text);
    
        $query = "";
        foreach($values as $value)
        {
            $query .='concat(model,varient,category) like "%'.$value.'%" AND ';
        }
    
        $query .= "1=1";
    
        $query = "select * from tbl_items where ".$query."";
        $result= mysqli_query($db,$query);
        echo '<table>';
        while($row=mysqli_fetch_array($result))
        {
            echo '<tr>';
            echo '<td>'.$row['model'].'</td><td>'.$row['varient'].'</td><td>'.$row['category'].'</td>';
            echo '</tr>';
        }
    echo '</table>';
    }
    ?>
    <form method="post" name="form">
    <input type="text" name="text" />
    <input type="submit" value="submit" name="submit">  
    </form>