PHP/SQL simple search engine

Question

I have 4 tables: companies, cities, categories and subcategories. Here goes the structure of each of them:

companies:

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)

cities:

city_name (varchar)
city_id (int)

categories:

category_name (varchar)
category_id (varchar)

subcategories:

subcategory_name (varchar)
subcategory_id (varchar)

Now, according to this, I want to perform a search in the companies table using some search term. I need to show the companies that the searched words match or partially match the values existing on: company_name, category_name, subcategory_name or company_description in selected city and order the results according to relevance. The companies that match more the search terms on top. For example, I've been trying with this:

$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
));

But this does not seem to be working very well. I have several repeated results, and if I reverse the word order the results do not appear. It seems that when I search for the category name there is no effect either. What should I do to get the result I expect?

Answer 1

consider this:

$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}%'";