Hi.
Following the book PHP & MySQL: From Novice to Ninja, I have built a website and added the beginnings of a Search function to it.
public function search() {
if (isset($_GET['searchString'])) {
$pageResults = $this->pagesTable->search($_GET['searchString']);
$title = 'Search Results';
return [
'template' => 'search.html.php',
'title' => $title,
'variables' => [
'pageResults' => $pageResults
],
];
}
}
This is a method in a “Page” controller I use to handle all pages in the site that have no sub-level of navigation. They are generally just text/information.
The method calls a new Database function I have added in my DatabaseTable class:
public function search($searchString)
{
$tempInput = trim($searchString);
$input = preg_replace('/\s+/', ' ', $tempInput);
$parameters = [
'input' => $input
];
$query = 'SELECT page_title FROM pages WHERE MATCH (page_title, content) AGAINST (:input) LIMIT 10';
$query = $this->query($query, $parameters);
return $query->fetchAll();
}
It’s all good… works wonderfully well. I have a single template: search.html.php.
As far as I can see, I have ONE problem with this code (feel free to disagree with me…). It searches and returns results from a table called “pages” in my database.
I have other tables I also need to search.
QUESTION
What would be the best way to alter what I have so that, without repeating the code in full for each table, I can search and return results for more than one table? Let’s say “pages”, “services” and “vacancies”. Let’s say they all have a “page_title” column and a “content” column, as per the code above.
Requirements:
-
The whole thing must be a single automated operation, i.e. based on entering a search term and hitting Enter once.
-
I would like the output in search.html.php to be grouped together regardless of table: say the strongest 5 results from each table. (I know, as you’ll see in the code, how to limit results… I don’t know how to merge the results into one list… say I have 3 tables… a single list of 15 results.
Thanks in advance,
Mike