Search Query not works Properly : Php Like Query

Question

 <?php
    include"configration.php";  
?>

 <?php
    $query = $_GET['query'];
    $min_length = 1;
    //echo $query;exit();
    if (strlen($query) >= $min_length) { // if query length is more or equal minimum length then
        //echo "success";exit();
        $query = htmlspecialchars($query);
        $query = mysqli_real_escape_string($conn, $query);
        $sql = "SELECT * FROM table2
                WHERE title LIKE '%".$query."%' order by date DESC";
        $raw_results = mysqli_query($conn, $sql) or die(mysql_error());
        if (mysqli_num_rows($raw_results) > 0) { // if one or more rows are returned do following
            while ($res = mysqli_fetch_array($raw_results)) { ?>
    
              <?php echo $res['title'] ?> // Place where result comes ..
    
            <?php }
        }
    }
    ?>

This is code works fine but search in this way

For Example Title is: you are vary nice boy but lazy

When I search by:

You are vary ............. result shows ..

vary nice boy ............. result shows ..

vary lazy, or boy lazy or vary lazy .. result not shows ..

Plz some one help me in this and how to show searched query in title ..

   <title> Searched Query ...</title>

Answer 1

When you search title LIKE "%vary lazy%", you will get records that contain the string "vary lazy" preceeded and followed by any other or no character sequences. If you want to match strings that contain the words - I should better say, the character sequences - "vary" and "lazy" in that specific order you should use:

title LIKE "%vary%lazy%"

However, this will also match "varylazy", "varying lazytown characters".

Assuming you generally intend to use queries as you mentioned, i.e. each word is separated by a space character and you want to see if those words appear in a text in specifically that order, you could write something like this:

$query = $_GET["query"];
$query = '%'.str_replace(' ', '%', $query).'%';

//... MySQL stuff

Please be aware that the code above is very specific to your needs. I wouldn't use it as a general purpose approach for processing query strings, e.g. having multiple spaces between words would result in multiple consequent % in your SQL query - I'm not even sure if that is allowed. However, under the constraints described, this code should work just fine.

Answer 2

LIKE '%boy lazy%' will show the Of the cases where anything can be before boy lazy and anything can be after boy lazy, but boy lazy will be together.

In your case, one approach can be, you can explode your $query, and then use multiple LIKE queries to create sql query. Example:

<?php
//$conn = mysqli_connect("localhost","your user","your pass","db");
$query = $_GET['query'];
$min_length = 1;
//echo $query;exit();
if (strlen($query) >= $min_length) { // if query length is more or equal minimum length then
    //echo "success";exit();
    $query = htmlspecialchars($query);
    $query = mysqli_real_escape_string($conn, $query);
    $searchKeys = explode(' ',$query);
    $sql = "SELECT * from table2 where title ";
    foreach ($searchKeys as $key) {
      $sql.= "LIKE '%".$key."%' AND title ";
    }
    $sql = substr($sql, 0, -10);
    //$sql.="ORDER BY date DESC;";
    $raw_results = mysqli_query($conn, $sql) or die(mysql_error());
    if (mysqli_num_rows($raw_results) > 0) { // if one or more rows are returned do following
        while ($res = mysqli_fetch_assoc($raw_results)) {
            echo $res['title']."\n";
         }
    }
}