使用 LIKE 的 MYSQL 查询

本文介绍了使用 LIKE 的 MYSQL 查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用 PHP 来查询 MYSQL 以获得一些完全匹配和一些部分匹配.以下代码为我提供了所有字段的完美匹配:

I'm trying to use PHP to query MYSQL for some exact matches and some partial matches. The following code give me perfect exact matches for all fields:

$results= mysql_query ("SELECT * FROM companyinfo WHERE id='$Companyid' OR companyname='$Companyname' OR contactname='$Contactname' OR address1='$Address1' OR city ='$City' OR primaryphone='$Primaryphone' OR email='$Email'")

我希望能够对公司名称进行部分搜索.使用以下代码可以实现这一点，但它会弄乱所有其他搜索字段.

I would like to be able to do a partial search on Company Name. Using the following code allows me to achieve this except it messes up all other search fields.

$results= mysql_query ("SELECT * FROM companyinfo WHERE id='$Companyid' OR companyname LIKE '%{$Companyname}%' OR contactname='$Contactname' OR address1='$Address1' OR city ='$City' OR primaryphone='$Primaryphone' OR email='$Email'")

有人有任何提示吗?

推荐答案

我在这里稍微读了一下你的问题，但我很确定 $Companyname 是空的，留给你的是查询:

I'm reading into your question a bit here, but I'm pretty sure $Companyname is empty, leaving you with the query:

SELECT * FROM companyinfo WHERE id='20' OR companyname LIKE '%%' OR ...

那个 WHERE companyname LIKE '%%' 将匹配 anything 非空；使所有其他 OR 条件没有实际意义.您需要设置一个特殊情况，其中 $Companyname 为空，或者只是不在谓词中包含未使用的搜索条件.

That WHERE companyname LIKE '%%' will match anything non-null; making all the other OR conditions moot. You'll need to set up a special case where $Companyname is empty, or simply not include unused search criteria in the predicate.

当然，假设 OR 确实是您在此处想要执行的操作，则仅此而已.在我所做的大多数搜索中，假设的 UX 是匹配我指定的所有条件，而不是任何.any 唯一适用的情况是使用单个搜索输入，而您的情况显然不是.

This is all, of course, assuming that OR is really what you mean to do here. In most searches I've done, the assumed UX has been to match all criteria I specify, not any. The only time any would apply is with a single search input, which your case clearly is not.

我还应该包括强制性的您的查询受到 SQL 注入的影响.停止将明显是用户输入的内容放入动态查询中，而是使用参数."也可以在这里发表评论.

I should also include the obligatory "Your query is subject to SQL injection. Stop putting what is clearly user-input into dynamic queries, and use parameters instead." comment here as well.