如何使用条件过滤器构建MySQL查询? [英] How to build a MySQL query with conditional filters?

问题描述

我对PHP和MySQL还是很陌生，我正在尝试建立一个包含歌手报价的小型图书馆.我希望用户能够通过输入报价本身的一部分，歌手的姓名或歌手的乐队(如果有的话)来搜索报价.

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

这是有效的，尽管它也会返回结果，其中search_string位于单词的中间:如果我键入她"，它将返回包含其他"或一起"的所有引号.如果我从查询中删除第二个％，它将不会返回引号，而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.

无论如何，我也想做的就是过滤结果.假设我希望用户只能通过单选按钮显示英文，德文或两者的引号.默认情况下，两者均处于选中状态，因此上面的$ query仍然有效.但是，如果用户选择英语，则还应该说类似AND (language = 'EN')的字样.

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

它可以工作，但是可能确实很麻烦，因为我也希望用户仅使用过滤器来搜索报价，而无需输入搜索查询:他们将能够通过Band中的歌手查找英文报价，例如.所以，我也有这个:

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

但是，$sql .= " AND (language = 'EN')";将不再正确，因为"AND"应为"WHERE".因此，我将必须有另一个if子句来修改字符串，对于我决定应用的每个过滤器，还要有另一个.

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?

谢谢！

推荐答案

设置一个始终为true的条件，以具有恒定的WHERE子句.

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

这篇关于如何使用条件过滤器构建MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！