如何使用条件过滤器构建MySQL查询？

Question

I'm pretty new to both PHP and MySQL and I'm trying to build a small library of singers' quotes. I'd like the user to be able to search for a quote by typing a part of the quote itself, the singer's name or the singer's band, if any.

Now, that part I pretty much nailed:

$query = 'SELECT * FROM quotes WHERE (quote LIKE "%'.$search_string.'%" OR singerName LIKE "%'.$search_string.'%" OR bandName LIKE "%'.$search_string.'%")';

This works, although it alors returns results where the search_string is in the middle of a word: if I type "her", it will return every quote containing "other" or "together". If I drop the second % from the query, it won't return quotes where the search_string isn't the very first word, which I want it to do.

Anyway, what I'd also like to do is give the possibility to filter the results. Let's say I want the user to be able to only show quotes in English, German or Both, via radio buttons. By default, Both is selected, so the $query above is still valid. However, if the user selects English, it should also say something like AND (language = 'EN').

For now, I tried adding to the query this way:

if ($_POST['language']== "EN") {
  $sql .= " AND (language = 'EN')";
}

It works, but it can be a real hassle, as I also want the user to search for quotes using only the filters, without entering a search query: they would be able to look for quotes in English by singers in Band, for example. So, I also have this:

if (strlen($search_string) < 1) {
  $sql = "SELECT * FROM quotes";
}

But then, the $sql .= " AND (language = 'EN')"; wouldn't be correct anymore, as "AND" should be "WHERE". So I'll have to have another if clause to modify the string, and another one for every filter I decide to apply.

My question is then: how should I build my query, given that there are optional filters, and that the search should also be possible using the filters alone?

Thanks!

Answer 1

Set an always true condition in order to have constant WHERE clause.

$sql = "SELECT * FROM myTable WHERE 1 = 1 "
if (true) {
    $sql .= " AND col1 LIKE '%val%' ";
}