我有4个表格:公司,城市,类别和子类别。以下是它们中的每一个的结构:
公司:
company_name (varchar) NOT NULL
company_city_id (int) FOREIGN KEY NOT NULL
company_category_id (int) FOREIGN KEY NOT NULL
company_subcategory_id (int) FOREIGN KEY
company_description (varchar)
城市:
city_name (varchar)
city_id (int)
类别:
category_name (varchar)
category_id (varchar)
子类别:
subcategory_name (varchar)
subcategory_id (varchar)
现在,根据这一点,我想使用一些搜索词在公司表中执行搜索。我需要向公司展示搜索的单词与所选城市的现有值匹配或部分匹配:company_name、category_name、subcategory_name或company_description,并根据相关性对结果进行排序。与顶部搜索词匹配更多的公司。例如,我一直在尝试这个:
$city = 1;
$search_term = "pizza company";
$sql = "SELECT * FROM companies comp, categories cat, subcategories sub WHERE comp.company_city_id = :city AND
comp.company_name OR cat.category_name OR sub.subcategory_name OR comp.company_description LIKE '%:search_term%'";
$query = $conexao->prepare($sql);
$query->execute(array
(
'city' => $city,
'search_term' => $search_term
));
但这似乎效果不佳。我有几个重复的结果,如果我颠倒单词顺序,结果不会出现。似乎当我搜索类别名称时也没有效果。我应该怎么做才能得到我期望的结果?
考虑一下:
$sql = "SELECT * FROM companies comp, categories cat, subcategories sub WHERE
comp.company_city_id = {$city} AND
comp.company_name LIKE '%{$search_term}%' OR cat.category_name LIKE '%
{$search_term}%' OR sub.subcategory_name LIKE '%{$search_term}%' OR
comp.company_description LIKE '%{$search_term}%'";