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();
}