如何在MySQL LIKE查询中使用PHP字符串? [英] How to use PHP string in mySQL LIKE query?

问题描述

我正在尝试查找与特定模式匹配的行数.在此示例中，所有以"123"开头的内容:

I am trying to find the number of rows that match a specific pattern. In this example, all that START with "123":

这有效:

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '123%'");
$count = mysql_num_rows($query);

问题在于LIKE会有所不同，因此我试图在脚本中定义它，然后执行查询，但这不起作用:

The problem is the LIKE will vary, so I'm trying to define it in the script, then execute the query, but this is NOT working:

$prefix = "123";
$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix.'%'");
$count = mysql_num_rows($query);

如何使查询在第二个示例中正常工作?

How can I get this query to work properly in the second example?

我也试过了没有句号(也无法使用):

I've also tried it without the period (also not working):

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix'%'");

推荐答案

语法错误；无需在双引号字符串内放置句点.相反，它应该更像

You have the syntax wrong; there is no need to place a period inside a double-quoted string. Instead, it should be more like

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '$prefix%'");

您可以通过打印出字符串以确认与第一种情况相同来确认这一点.

You can confirm this by printing out the string to see that it turns out identical to the first case.

当然，将这样的变量简单地注入查询字符串中并不是一个好主意，因为存在SQL注入的危险.至少您应该使用mysql_real_escape_string手动转义变量的内容，这将使其看起来像这样:

Of course it's not a good idea to simply inject variables into the query string like this because of the danger of SQL injection. At the very least you should manually escape the contents of the variable with mysql_real_escape_string, which would make it look perhaps like this:

$sql = sprintf("SELECT * FROM table WHERE the_number LIKE '%s%%'",
               mysql_real_escape_string($prefix));
$query = mysql_query($sql);

请注意，在sprintf的第一个参数内，百分号需要加倍以最终在结果中出现一次.

Note that inside the first argument of sprintf the percent sign needs to be doubled to end up appearing once in the result.

这篇关于如何在MySQL LIKE查询中使用PHP字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！