How to search multiple words?

Question

I was trying to make a search engine so far I had done with it and the feature I lack in it was, when user queries a long sentence or two or three words with on not in line-wise in my DB is not going to show and it shows 0 results.

So far it is working fine with the one-word search it is doing a great job with that.

public function getResultsHtml($page, $pageSize, $term){

    $fromLimit = ($page - 1) * $pageSize;

    //page 1: (1-1) * 20
    //page 2:(2-1) * 20

    $query = $this->con->prepare("SELECT *
                                    FROM sites WHERE title LIKE :term
                                    OR url LIKE :term
                                    OR keywords LIKE :term
                                    OR description LIKE :term
                                    ORDER BY clicks DESC
                                    LIMIT :fromLimit,:pageSize");

    $searchTerm = "%" . $term ."%";

    $query->bindParam(":term",$searchTerm);
    $query->bindParam(":fromLimit",$fromLimit,PDO::PARAM_INT);
    $query->bindParam(":pageSize",$pageSize,PDO::PARAM_INT);
    $query->execute();

    $resultsHtml ="<div class='siteResults'>";

    while($row = $query->fetch(PDO::FETCH_ASSOC)){
        $id = $row["id"];
        $url = $row["url"];
        $title = $row["title"];
        $description = $row["description"];

        $title = $this->trimField($title,55);
        $description = $this->trimField($description,230);

        $resultsHtml .="<div class='resultContainer'>

                            <h3 class='title'>
                                <a class='result' href='$url' data-linkId='$id'>
                                    $title
                                </a>
                            </h3>
                            <span class='url'>$url</span>
                            <span class='description'>$description</span>

                        </div>";


    }

    $resultsHtml .= "</div>";

    return $resultsHtml;


}

So when user searches apple it is retrieving the data and we can see the search result in "apple store search" in the first image.But in second image when we search "apple search" it should be able to show us the "apple store search".

first image

Second image

Answer 1

we dont know how u get search words from user, but as i guess u get them as an array. so you can try below code:

<?php
...
$textSearch = "";
for($i = 0 ; $i< count($userInputs);$i++){
    if($i !== count($userInputs) -1){
        $userInputData       = $userInputs[$i]; 
        $textSearch         .= "'%$userInputData%' OR";
    }else{
        $textSearch         .= "'%$userInputData%'";
    }
}

and put $textSearch into your query. remember , $userInputs is an array that u had before.

UPDATE

as your images shown, you can add $userInput = explode(" ",$textFromUser) in very begin of given code.

Answer 2

First you need to breakdown your $term into separated words. With European languages, you can simply use explode:

<?php

$terms = explode(' ', $term);

// lets say your $term is 'apple orange lemon banana'
// $terms would be ['apple', 'orange', 'lemon', 'banana']

Prepare Terms List for Binding

Then, I'd build a key-value array for the terms binding:

<?php

// build a term list with term key (:term{number}) and associated term
// for binding
$term_params = [];
foreach ($terms as $key => $term) {
        $term_params[":term{$key}"] = "%{$term}%";
}

// $term_params would be:
// [
//   ':term0' => '%apple%',
//   ':term1' => '%orange%',
//   ':term2' => '%lemon%',
//   ':term3' => '%banana%',
// ]

Prepare the Where Clause in SQL for Binding

Now, supposed the logics is all the terms need to show up in either of the target fields:

<?php

// a list of fields to search from
$fields = ['title', 'url', 'keywords', 'description'];

// build a where clause SQL based on the terms and fields
$or_where_clauses = [];
foreach (array_keys($term_params) as $term_key) {
      $or_where_clauses[] = implode(' OR ', array_map(function ($field) use ($term_key) {
                return "{$field} LIKE {$term_key}";
        }, $fields));
}

$where_clauses_sql = implode(' AND ', array_map(function ($term_clause) {
        // quote each term clause with bracket for proper OR logic to work
        return '(' . $term_clause . ')';
}, $or_where_clauses);

The resulting where clauses sql would be:

(title like :term0 OR url like :term0 OR keywords like :term0 OR description like :term0)
AND
(title like :term1 OR url like :term1 OR keywords like :term1 OR description like :term1)
AND
...
...
(title like :termN OR url like :termN OR keywords like :termN OR description like :termN)

Putting Everything Together

I can then build the SQL string and bind the terms to the query accordingly:

<?php

// prepare the select statement
$query = $this->con->prepare("SELECT *
                            FROM sites WHERE {$where_clauses_sql}
                            ORDER BY clicks DESC
                            LIMIT :fromLimit,:pageSize");

// bind the terms
foreach ($term_params as $key => $value) {
        $query->bindParam($key, $value);
}
$query->bindParam(":fromLimit", $fromLimit, PDO::PARAM_INT);
$query->bindParam(":pageSize", $pageSize, PDO::PARAM_INT);
$query->execute();

// ...

Shortcomings and Potential Solution

Please note that this approach does not understand the number of occurrence of terms in the field. And the way to separate words is far from perfect. For example:

1. It didn't handle punctuation marks.
2. It couldn't search for plural terms with the singular, or visa verse.
3. It could be matching part of a word by mistake (e.g. search "sing" and match "dressing").
4. It couldn't deal with CJK or languages without space).

You can use software like Elastic Search for better feature. With plugin and proper config, you can give even sort results with relevance, or give different field a different importance in the search process etc.

But that is an entirely different software than SQL server to use. You'd need to learn and plan how to index your contents there as well as just saving your data to SQL.