How to search the multiple keywords in multiple columns?

Question

I have a search box. It uses explode to create an array and uses it to look for the items from the database (3 columns - name, description, tags). This version works fine (single column - name):

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);
    
    $nameloop = "";
    
    $i = 1;
    while ($i < 10){
        if(!empty($data[$i])){
            $nameloop .= " AND name LIKE '%".$data[$i]."%' ";
        }
            
        $i++;
    }
    
    echo "<br>".$nameloop."<br>";

    $sql = "
    SELECT * FROM product WHERE
    name LIKE '%$data[0]%'".$nameloop." ORDER BY name
    ";
    
    echo $sql;
    
    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 8;

    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        --> CONTENT <--
        $counter++;
    }

mysqli_close($con);
}

When I'm trying to do it for all 3 columns with mysqli_multi_query:

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);
    
    $nameloop = "";
    $descloop = "";
    $tagsloop = "";
    
    $i = 1;
    while ($i < 10){
        if(!empty($data[$i])){
            $nameloop .= " AND name LIKE '%".$data[$i]."%' ";
        }
            
        $i++;
    }
    
    echo "<br>".$nameloop."<br>";
    
    $i = 1;
    while ($i < 10){
        if(!empty($data[$i])){
            $descloop .= " AND description LIKE '%".$data[$i]."%' ";
        }
            
        $i++;
    }
    
    echo "<br>".$descloop."<br>";
    
    $i = 1;
    while ($i < 10){
        if(!empty($data[$i])){
            $tagsloop .= " AND description LIKE '%".$data[$i]."%' ";
        }
            
        $i++;
    }
    
    echo "<br>".$tagsloop."<br>";

    $sql = "
    SELECT * FROM product WHERE
    name LIKE '%$data[0]%'".$nameloop." ORDER BY name;
    
    SELECT * FROM product WHERE
    description LIKE '%$data[0]%'".$descloop." ORDER BY name;
    
    SELECT * FROM product WHERE
    tags LIKE '%$data[0]%'".$tagsloop." ORDER BY name;
    ";
    
    echo $sql;
    
    $res = mysqli_multi_query($con,$sql);
    
    $counter = 0;
    $limit = 8;

    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        --> CONTENT <--
        $counter++;
    }

mysqli_close($con);
}

Error:

:  Uncaught TypeError: mysqli_fetch_assoc(): Argument #1 ($result) must be of type mysqli_result, bool given in C:\xampp\htdocs\Php\search-bar-modify-recipes.php:66
Stack trace:
#0 C:\xampp\htdocs\Php\search-bar-modify-recipes.php(66): mysqli_fetch_assoc(true)
#1 C:\xampp\htdocs\Php\search-bar-modify-recipes.php(89): searchResults()
#2 {main}
  thrown in [file path] on line 66 [while($row = mysqli_fetch_assoc($res)...]

To clarify - I want the search box to look for the words from the input in the database like this:

SELECT * FROM product WHERE name LIKE 'pi' AND name like 'sal' OR description LIKE 'moz' AND 'tor' ORDER BY name

So it should output database items (name: salami pizza | desc: tomato sauce, pesto), (name: mozzarella wrap | desc: mozzarella and a big tortilla). I hope it's clear. When I echo $sql, the query seems fine:

   SELECT * FROM product WHERE
   name LIKE '%piz%' ORDER BY name;
   
   SELECT * FROM product WHERE
   description LIKE '%piz%' ORDER BY name;
   
   SELECT * FROM product WHERE
   tags LIKE '%piz%' ORDER BY name;

Will it even work when it's fixed? Will it be 1 list of items or 3 lists (I mean the ORDER BY element, I want all of them to be sorted in 1 list)? How can I fix it? EDIT: It's not a duplicate. That post refers to searching in 1 column at a time using multiple keywords, I want to search in all three columns at once, that's why OR or UNION don't work in this case.

You need just a SINGLE query. There is absolutely no use case for multi_query(), let alone your particular case — Your Common Sense, Aug 27 '22 at 15:15
What I don't get is how your single query became three queries — Your Common Sense, Aug 27 '22 at 15:19
@YourCommonSense When I use "UNION" or "OR" there is a problem. Let's say I'm searching for (name: salami pizza | desc: mozzarella, tomato sauce, meat). When I type "pizza mozzarella" there are no results. It works when I type "pizza sal" and "tomato meat", but it has to be in the same column. — Astw41, Aug 27 '22 at 15:28
concat(name, description, tags) will make a single column to search in — Your Common Sense, Aug 27 '22 at 15:50
@YourCommonSense No, it doesn't. It works like "you can search in the name column OR description column OR tags column". I can't "mix" it, so one word is present in the name column and the other one is in the tags column. — Astw41, Aug 27 '22 at 15:50
Your question is confusing. I fail to see how a single search box will produce different search terms for different columns as in `WHERE name LIKE 'pi' AND name like 'sal' OR description LIKE 'moz' AND 'tor'`. Looks like this question should be closed as unclear instead. — Your Common Sense, Aug 27 '22 at 15:55
When you type "pi" and "moz" it didn't work, because there was OR between different columns. It's like you have 2 boxes but the access only to 1 at a time. You can take 2 items, but only from 1 box. You can't take an item from box A and then take another item from the box B. At least that's what I think has happened here. — Astw41, Aug 27 '22 at 16:02

How to search the multiple keywords in multiple columns?

0 Answers0