PHP search box with data explode

Question

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" class="search-form">
  <input type="text" name="search-bar" class="search-bar" placeholder="Search..." id="search-bar">
  <div class="search-results"></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] = "q6Z%6fS8GG+2h6/q/"; <-- some random text
    }    
    if(empty($data[2])){
        $data[2] = "q6Z%6fS8GG+2h6/q/";
    }    
    if(empty($data[3])){
        $data[3] = "q6Z%6fS8GG+2h6/q/";
    }    
    if(empty($data[4])){
        $data[4] = "q6Z%6fS8GG+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

You need some loop to build dynamically your query. And use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) to avoid SQL Injections — Cid, Aug 26 '22 at 16:45
Does this answer your question? [Create a dynamic mysql query using php variables](https://stackoverflow.com/questions/15794179/create-a-dynamic-mysql-query-using-php-variables) — Cyrus Raoufi, Aug 26 '22 at 16:49
Didn't I already point you to [Build SELECT query with dynamic number of LIKE conditions as a mysqli prepared statement](https://stackoverflow.com/a/51036322/2943403) yesterday? Did you delete that question or was my comment deleted? — mickmackusa, Aug 27 '22 at 23:43

Misunderstood · Answer 1 · 2022-08-28T19:50:30.693

0

I'm guessing here.

But even if I'm wrong you need to eliminate extraneous spaces before the explode().

It's likely there is a double space or leading or trailing spaces.

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

Two things to try.

After the explode use var_export($data) to see what's in the array.

echo '<pre>' . var_export($data) . "\n</pre>";

Look at the query string.

echo($sql);

edited Aug 28 '22 at 19:50

answered Aug 26 '22 at 18:59

Misunderstood

5,534
1
18
25

If this is your advice (splitting on a variable number of spaces), then you should be closing this page with [Explode string by one or more spaces or tabs](https://stackoverflow.com/q/1792950/2943403) instead of answering. – mickmackusa Aug 27 '22 at 23:45
@mickmackusa That was not the only possible issue. And I'm not very good with Regex and I had read in the PHP guide that you should use str_replace when possible instead of using Regex. BTW I noticed some of the comments were deleted too. Everyone was commenting on the query. I do not think the query was the issue. The "Warning" he was getting was about the array and it's undeclared key. So people don't like his query, I had not problem with it. What did bug me was use of an * after the SELECT and the use of an associative array. – Misunderstood Aug 28 '22 at 19:34

PHP search box with data explode

1 Answers1