使用 LIKE '%%' 在 MySQL 查询中使用 PHP 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20340553/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use PHP variables in MySQL Query with LIKE '%%'
提问by Vereonix
I'm building a search function for my site, however the MySQl query won't read the PHP variables, and I don't mean errors, it just seems to think they're NULL.
我正在为我的网站构建一个搜索功能，但是 MySQl 查询不会读取 PHP 变量，而且我不是指错误，它似乎只是认为它们是 NULL。
My current code is:
我目前的代码是：
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('library', $conn);
$sql = "SELECT * FROM Books";
if($_POST['find']!="")
{
if($_POST['field'] == "Books")
{
$sql = "SELECT *
FROM Books
JOIN bookauthor ON books.BookID = bookauthor.BookID
JOIN authors ON bookauthor.AuthorID = authors.AuthorID
WHERE books.BookName LIKE '%''".($_POST['find'])."''%'
GROUP BY books.BookName
ORDER BY authors.AuthorID";
}
else if ($_POST['field'] == "Authors")
{
$sql = "SELECT *
FROM Books
JOIN bookauthor ON books.BookID = bookauthor.BookID
JOIN authors ON bookauthor.AuthorID = authors.AuthorID
WHERE authors.Forename LIKE '%J.%'
AND authors.Surname LIKE '%%'
GROUP BY books.BookName
ORDER BY authors.AuthorID";
}
}
$result = mysql_query($sql, $conn) or die("Can't run query");
$loopnumber = 1;
if (mysql_num_rows($result) ==0 ){echo "No Results have been found";}
The POST variable does contain data as I've tested by echo'ing it, however my site just gives the "No Results have been found" message meaning the query retuned no results. Even if I pass the POST into a normal variable I get the same results.
POST 变量确实包含我通过回显测试过的数据，但是我的站点只给出“未找到结果”消息，这意味着查询没有返回任何结果。即使我将 POST 传递给普通变量，我也会得到相同的结果。
However if I remove the "LIKE '%%'" and have it look for and exact match from typing in the search on the site it works fine.
但是，如果我删除“LIKE '%%'”并让它通过在网站上的搜索中输入来查找和完全匹配，它就可以正常工作。
Edit: Hmmmm, just made it so I pass the POST into a variable like so..
编辑：嗯，刚刚做到了，所以我将 POST 传递到这样的变量中..
$searchf = "%".$_POST['find']."%";
and having that variable in the WHERE LIKE makes it work, now I'm just curious as to why it doesn't work the other way.
并在 WHERE LIKE 中使用该变量使其工作，现在我只是好奇为什么它不能以其他方式工作。
I seems to love quotation marks too much, and should go to bed.
我好像太爱引号了，该睡觉了。
回答by Mike Brant
Well first of all, I am guessing you are getting a MySQL syntax error when trying to execute that first query. This line:
首先，我猜您在尝试执行第一个查询时遇到了 MySQL 语法错误。这一行：
WHERE books.BookName LIKE '%''".($_POST['find'])."''%'
Should be
应该
WHERE books.BookName LIKE '%".$_POST['find']."%'
Because right now you are getting
因为现在你得到
WHERE books.BookName LIKE '%''ABC''%'
when you should be getting
当你应该得到
WHERE books.BookName LIKE '%ABC%'
I don't admit to understand what you are doing with your second query, which just hard codes and has %%as one of the search criteria, which is, in essence meaningless.
我不承认你在用你的第二个查询做什么，它只是硬编码并%%作为搜索条件之一，这在本质上是毫无意义的。
回答by SkaiBoa
Its in your LIKE expression. If in $_POST['find'] the value is LOTR the query would be WHERE books.BookName LIKE '%''LOTR''%' and the resault would be empty. Just remove the double ' and it should be work.
它在你的 LIKE 表达式中。如果 $_POST['find'] 中的值为 LOTR，则查询将为 WHERE books.BookName LIKE '%''LOTR''%' 并且结果将为空。只需删除双 ' 就可以了。
回答by Maverick
Try this way:
试试这个方法：
$sql = "SELECT *
FROM Books
JOIN bookauthor ON books.BookID = bookauthor.BookID
JOIN authors ON bookauthor.AuthorID = authors.AuthorID
WHERE books.BookName LIKE '%".$_POST['find']."%'
GROUP BY books.BookName
ORDER BY authors.AuthorID";
It should be work
应该是工作
回答by sri
use this, worked for me:
使用这个，为我工作：
$query_casenumber = "SELECT * FROM pv_metrics WHERE casenumber='$keyword' OR age='$keyword' OR product='$keyword' OR eventpreferredterm='$keyword' OR patientoutcome='$keyword' OR eventsystemclassSOC='$keyword' OR asdeterminedlistedness='$keyword' OR narrative LIKE '%".$_POST['keyword']."%' ";
