I need the search bar to search for multi-words from different columns
this is my function it's working but is searching only 1 word from particular columns.
The search will be using a space-delimited string where I have to search for multiple words in different columns.
I have tried searching for a solution but I'm unable to sort it out.
I need guidance for this.
My PDO connection.
`<?php
class Database
{
private $dbServer = 'localhost';
private $dbUser = 'root';
private $dbPassword = '';
private $dbName = 'playersdb';
protected $conn;

public function __construct()
{
    try {
        $dsn = "mysql:host={$this->dbServer}; dbname={$this->dbName}; charset=utf8";
        $options = array(PDO::ATTR_PERSISTENT);
        $this->conn = new PDO($dsn, $this->dbUser, $this->dbPassword, $options);
    } catch (PDOException $e) {
        echo "Connection Error: " . $e->getMessage();
    }

}

}`
search function.

`public function searchPlayer($searchText, $start = 0, $limit = 100)
{
$sql = "SELECT * FROM {$this->tableName}
WHERE player LIKE :search
OR facility LIKE :search
OR nationality LIKE :search
ORDER BY id DESC LIMIT {$start},{$limit}";

$stmt = $this->conn->prepare($sql);
$stmt->execute([':search' => "%{$searchText}%"]);
if ($stmt->rowCount() > 0) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    $results = [];
}

return $results;

}`

My script.php file
$("#searchinput").on("keyup", function () {
const searchText = $(this).val();
if (searchText.length > 1) {
$.ajax({
url: "/search/ajax.php",
type: "GET",
dataType: "json",
data: { searchQuery: searchText, action: "search" },
success: function (players) {
if (players) {
var playerslist = "";
$.each(players, function (index, player) {
playerslist += getplayerrow(player);
});
$("#userstable tbody").html(playerslist);
$("#pagination").hide();
}
},
error: function () {
console.log("something went wrong");
},
});
} else {
getplayers();
$("#pagination").show();
}
});
// load players
getplayers();
});
My ajax.php file
if ($action == 'search') {
$queryString = (!empty($_GET['searchQuery'])) ? trim($_GET['searchQuery']) : '';
$results = $obj->searchPlayer($queryString);
echo json_encode($results);
exit();
}

    Ideally, and simply, you would use a FULLTEXT search to accomplish this. However, since you are using a leading wild-card character, you cannot use this type of search.

    To change your current method to work for multiple words, you would explode the search string on the space characters, trim, then remove empty string entries. You would then loop over the resulting array of words, adding the sql for each search term to an array and adding each word, including the wild-card characters, to another array. You would then implode the first array with an ' OR ' between the entries to give the sql syntax to use in the WHERE ... clause and use the second array as the call-time parameter in the ->execute(...); call.

    BTW - you can simplify the current player LIKE :search OR facility LIKE :search OR nationality LIKE :search to be the following -

    CONCAT_WS(' ', player, facility, nationality) LIKE ?

    Some points for the current code -

    1. A persistent connection only works when php is running as a server module, which is rare on actual hosting, where it ties up resources for the maximum number of concurrent connections, until the server is restarted, and should be avoided.
    2. When you make the connection you should also set emulated prepared queries to false (you want to run real prepared queries), set the default fetch mode to assoc (so that you don't need to specify it in each fetch statement), and set the error mode to exceptions (will result in the simplest application logic, since you will only need to catch and handle recoverable database errors such as when inserting/updating duplicate or out of range user submitted data.)
    3. A connection error is a fatal problem. You would want to halt code execution upon a connection error so that you don't produce follow-on errors by trying to use a connection that doesn't exist. Removing the existing try/catch logic around the connection will do this since php will then catch and handle the connection exception for you.
    4. fetchAll() returns an empty array when the query doesn't match any data. There's no need for the rowCount() logic.
    5. A zero is considered to be an empty() value by php. The current logic using empty() won't allow a zero to be searched for. You should specifically test if an input value is or is not exactly an empty string ''
    6. The $queryString value can be empty, if the get input wasn't set or if all white-space characters were entered (the trim() call will result in an empty string.) In this case you should not call the ->searchPlayer() method and output a user message instead.
    Write a Reply...