Search query shows all records when searching in two columns and search word is empty

I am having an issue with a query I am using.

There are two things to search for, a category and a searchword, category searches within a category column in my database, but this part of the query is only added when a category is selected, if not then the whole category part is not present in the query.

Then my seachword searched in two columns, in a title column and in a text column (called introtext).

This is my query now:

$content = "
SELECT cnt.id as content_id, cnt.title as content_title, cnt.featured, cnt.ordering, cnt.alias as content_alias, cnt.catid, cnt.images, cnt.state, cnt.introtext, cat.id as cat_id, cat.title as cat_title, cat.alias as cat_alias,
MAX(case when f.field_id = 7 then f.value end) as hoofdafbeelding,
MAX(case when f.field_id = 8 then f.value end) as openingstijden,
MAX(case when f.field_id = 9 then f.value end) as straat,
MAX(case when f.field_id = 10 then f.value end) as facebook,
MAX(case when f.field_id = 11 then f.value end) as instagram,
MAX(case when f.field_id = 12 then f.value end) as telefoonnummer,
MAX(case when f.field_id = 13 then f.value end) as website
FROM snm_categories cat
LEFT JOIN snm_content cnt
ON cnt.catid = cat.id AND cnt.state = 1
LEFT JOIN snm_fields_values f
ON cnt.id = f.item_id
WHERE cnt.title LIKE '%".$conn->real_escape_string($trefwoord)."%' OR
cnt.introtext LIKE '%".$conn->real_escape_string($trefwoord)."%'
".$branchequery."
GROUP BY cnt.id, cnt.title, cnt.featured, cnt.alias, cnt.catid, cnt.images, cnt.state, cnt.introtext, cat.id, cat.title, cat.alias
ORDER BY cnt.ordering";
$contentcon = $conn->query($content);

And above that I have a couple of if elses to see if a category (branche) was selected. This code:

if(!empty($branche)){
  $branchequery = "AND cat.alias LIKE '".$conn->real_escape_string($branche)."'";
}else{
  $branchequery = '';
}
$branchetitel = str_replace('-', ' ', $branche);
if(!empty($trefwoord)){
  $gezocht = 'Je zocht naar: <b class="orange">'.$trefwoord.'</b>';
}else if(empty($trefwoord) AND empty($branche)){
  $gezocht = 'Je hebt geen zoekopdracht ingevoerd.';
  $branchequery = "AND cat.alias LIKE '%.............%'";
}else{
  $gezocht = 'Je zocht naar: <b class="orange">'.ucfirst($branchetitel).'</b>';
}

The dots are a dirty fix so that my query does not return all rows when it is empty.

But I still have this issue when $trefwoord is empty.

So when I pick a category in my search form, but don’t add a searchword ($trefwoord) I still get all rows, not just items from that category, I get all of them.

How can I fix that?

Somehow if I remove this line from my query:

OR
    cnt.introtext LIKE '%".$conn->real_escape_string($trefwoord)."%'

It works, but I would like to be able to search in both the text and titles of items in my database. How can I do that?

Answer

Your problem is in operator precedence in your WHERE clause. At present, when $trefwoord is empty, your WHERE clause looks like this:

WHERE cnt.title LIKE '%%' OR
      cnt.introtext LIKE '%%' AND 
      cat.alias LIKE 'something'

This is evaluated as

WHERE cnt.title LIKE '%%' OR
      (cnt.introtext LIKE '%%' AND cat.alias LIKE 'something')

which will always be true (cnt.title LIKE '%%' will always match). You need to resolve this by using parentheses appropriately, so the WHERE clause looks like

WHERE (cnt.title LIKE '%%' OR cnt.introtext LIKE '%%') AND 
      cat.alias LIKE 'something'

So, in your PHP write:

WHERE (cnt.title LIKE '%".$conn->real_escape_string($trefwoord)."%' OR
cnt.introtext LIKE '%".$conn->real_escape_string($trefwoord)."%')
".$branchequery."

Advertisement

Answer