PHP search box with data explode-CodePudding

I have a search box (PHP, AJAX). I want to make it so that when you type more than 1 word it searches every place from the name, description, etc. So when I have a description "Salami pizza with mozzarella and curry" I want to be able to type "pizza mozzarella salami" and find this item from the database.

<form method="POST" enctype='multipart/form-data' id="search-form" >
  <input type="text" name="search-bar"  placeholder="Search..." id="search-bar">
  <div ></div>
</form>

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

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    $sql = "SELECT * FROM product WHERE
    name LIKE '%$data[0]%' AND
    name LIKE '%$data[1]%' AND 
    name LIKE '%$data[2]%' AND 
    name LIKE '%$data[3]%' AND 
    name LIKE '%$data[4]%' AND 
    description LIKE '%$data[0]%' AND
    description LIKE '%$data[1]%' AND
    description LIKE '%$data[2]%' AND
    description LIKE '%$data[3]%' AND
    description LIKE '%$data[4]%' AND
    tags LIKE '%$data[0]%' AND
    tags LIKE '%$data[1]%' AND
    tags LIKE '%$data[2]%' AND
    tags LIKE '%$data[3]%' AND
    tags LIKE '%$data[4]%'
    ORDER BY name";
    
    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter  ;
    }
    
    echo "</ul>";

mysqli_close($con);
}

The problem is that when I type less than 5 words (or how many is in the query) I have an error Warning: Undefined array key 1 in C:\xampp\htdocs\[PHP file path] on line 12 I had an idea, but it's not working as I want it to:

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

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    if(empty($data[1])){
        $data[1] = "q6ZoS8GG 2h6/q/"; <-- some random text
    }    
    if(empty($data[2])){
        $data[2] = "q6ZoS8GG 2h6/q/";
    }    
    if(empty($data[3])){
        $data[3] = "q6ZoS8GG 2h6/q/";
    }    
    if(empty($data[4])){
        $data[4] = "q6ZoS8GG 2h6/q/";
    }

    $sql = "SELECT * FROM product WHERE
    name LIKE '%$data[0]%' OR 
    name LIKE '%$data[1]%' OR 
    name LIKE '%$data[2]%' OR 
    name LIKE '%$data[3]%' OR 
    name LIKE '%$data[4]%' OR 
    description LIKE '%$data[0]%' OR
    description LIKE '%$data[1]%' OR
    description LIKE '%$data[2]%' OR
    description LIKE '%$data[3]%' OR
    description LIKE '%$data[4]%' OR
    tags LIKE '%$data[0]%' OR
    tags LIKE '%$data[1]%' OR
    tags LIKE '%$data[2]%' OR
    tags LIKE '%$data[3]%' OR
    tags LIKE '%$data[4]%'
    ORDER BY name";
    
    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter  ;
    }
    
    echo "</ul>";

mysqli_close($con);
}

There is no error, searching for the data generally works fine, BUT when I type "pizza mozzarella salami" I get search results for every item in the database that has one of the words, so it won't find only "Salami pizza with mozzarella and curry", but also "mozzarella sticks" and "salami sandwich with mayo". You get the idea. I know That's because of "OR" in my query, but with AND there are errors. How can I make it work? EDIT:

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

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    $sql = "SELECT * 
            FROM product
            WHERE MATCH(name) AGAINST('$data' IN BOOLEAN MODE)
           ";

    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter  ;
    }
    
    echo "</ul>";

mysqli_close($con);
}

Error

Warning: Array to string conversion in C:\xampp\htdocs\[PHP file path] on line 15

CodePudding user response：

One solution for this is creating a query dynamically via a loop or more nested loop. like this

   $search = $_POST["search-bar"];
    $data = explode(' ',$search);
    
    if(empty($data[1])){
        $data[1] = "q6ZoS8GG 2h6/q/";
    }    
    if(empty($data[2])){
        $data[2] = "q6ZoS8GG 2h6/q/";
    }    
    if(empty($data[3])){
        $data[3] = "q6ZoS8GG 2h6/q/";
    }    
    if(empty($data[4])){
        $data[4] = "q6ZoS8GG 2h6/q/";
    }
    
    $where = array();
    foreach ($data as $value) {
        if ($value != '')
            $where[] = 'name LIKE '.$value;
    }
    
    $sql = "SELECT * FROM product WHERE ".implode(' AND ', $where)." ORDER BY name";

But the better solution for searching in MySQL is to define your description column as FULLTEXT, so it allows you to use some better MYSQL functions like WHERE MATCH (title,body), which have better performance

SELECT * 
FROM <table>
WHERE MATCH(<column>) AGAINST(' place  for  programmers' IN BOOLEAN MODE)

CodePudding user response：

Does the query work?
I'm guessing here.
But even if I'm wrong you need to eliminate extraneous spaces before the explode().

$search = trim($search);
while (strpos($search,'  '){ 
  str_replace('  ', ' ',$search);
}