我想知道是否有人能帮我。下面的代码将生成type=bus的所有用户,但不会只生成approved=1。如果有人能指出我的错误,我将不胜感激。搜索查询是由某人通过表单在文本框中输入一个单词来触发的,这一部分工作得很好,只有个人资料中有搜索单词的用户才会出现,只有type=bus但approved=1的用户似乎没有效果,并返回所有已批准的,我真的希望有人能帮忙。
$query = "select * from users where type='bus' and approved=1 and
name like '"%$trimmed%'"
or profile_words like '"%$trimmed%'"
or full_name like '"%$trimmed%'"
or tag_line like '"%$trimmed%'"
or referral like '"%$trimmed%'"
or profession like '"%$trimmed%'"
order by full_name";
这样分组您的条件,
$query = "
select *
from users
where type='bus' and
approved = 1 and
(
name like '"%$trimmed%'" or
profile_words like '"%$trimmed%'" or
full_name like '"%$trimmed%'" or
tag_line like '"%$trimmed%'" or
referral like '"%$trimmed%'" or
profession like '"%$trimmed%'
)
order by full_name";
我有一个建议,请使用PDO或MySQLI扩展以避免SQL注入。
PDO、示例
<?php
$query = "
select *
from users
where type='bus' and
approved = 1 and
(
name like ? or
profile_words like ? or
full_name like ? or
tag_line like ? or
referral like ? or
profession like ?
)
order by full_name";
$name = '%' . $trimmed . '%';
$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $name);
$stmt->bindParam(3, $name);
$stmt->bindParam(4, $name);
$stmt->bindParam(5, $name);
$stmt->bindParam(6, $name);
$stmt->execute();
?>
$query = "select * from users where type='bus' and approved=1 and
(name like '"%$trimmed%'"
or profile_words like '"%$trimmed%'"
or full_name like '"%$trimmed%'"
or tag_line like '"%$trimmed%'"
or referral like '"%$trimmed%'"
or profession like '"%$trimmed%'")
order by full_name";
我认为您要批准的部分是可以的,但我认为问题可能在于OR
的启动。试着把AND
和OR
语句分开放在括号里,如下所示:
$query = "select * from users where type='bus' and approved=1 and
( name like '"%$trimmed%'"
or profile_words like '"%$trimmed%'"
or full_name like '"%$trimmed%'"
or tag_line like '"%$trimmed%'"
or referral like '"%$trimmed%'"
or profession like '"%$trimmed%'" )
order by full_name";
将where运算符括起来。目前您正在获得
where (type='bus' and approved=1)
or .... [all the other operators]