Skip to content
Advertisement

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:

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

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:

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?

Advertisement

Answer

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

This is evaluated as

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

So, in your PHP write:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement