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?

WARNING: This has some severe SQL injection bugs because user data is used inside the query. Whenever possible use prepared statements. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. NEVER put $_POST, $_GET or any user data directly in your query. — tadman
– tadman, Commented Oct 26, 2017 at 18:35
It shouldn't work. You have no placeholders. You are injecting the raw variable onto the query — Rotimi
– Rotimi, Commented Oct 26, 2017 at 18:35
You cannot combine OR clauses like that. You must specify the condition for each of them (yes, a bit tedious, but such is life) — Patrick Q
– Patrick Q, Commented Oct 26, 2017 at 18:36
I use placeholders and PDO. I wrote the example code with hurry, I updated it. — Hypister
– Hypister, Commented Oct 26, 2017 at 18:37
@PatrickQ Ok, I tried that, but I'm still getting repeated results and results that does not match the searh terms. :/ — Hypister
– Hypister, Commented Oct 26, 2017 at 20:12

user5832543 · Accepted Answer · 2017-10-26 18:39:57Z

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

answered Oct 26, 2017 at 18:39

user5832543

Sign up to request clarification or add additional context in comments.

1 Comment

Hypister Over a year ago

This is not working, I'm still getting repeated results and results that does not match the search terms.

Collectives™ on Stack Overflow

PHP/SQL simple search engine

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related