我有一个包含关于一个人的信息的数据库。假设我想搜索来自某个国家的人。所以我输入美国,得到10个数据。在那之后,我想把范围进一步缩小到男性。
我如何做一个SQL,可以先搜索国家,然后搜索性别。
$sql="SELECT * FROM people
WHERE country like $search1%
ORDER BY ASC ";
和
$sql="SELECT * FROM people
WHERE country like $search1%
AND Gender = $search2
ORDER BY ASC ";
我如何将它们组合成一个$sql语句?
尝试用以下代码组合所有情况:
$sql = "SELECT *
FROM people
WHERE (country LIKE %$search1% AND $search2='')
OR ($search1='' AND gender=$search2)
OR (country LIKE %$search1% AND gender=$search2)";
添加您自己的ORDER BY
子句
已更新
SELECT *
FROM people
WHERE
((country = ('USA') AND gender = 'male')
OR
(country LIKE ('USA') OR gender LIKE 'male'))
ORDER BY ASC
第一行((country = ('USA') AND gender = 'male')
对第二行(country LIKE ('USA') OR gender LIKE 'male'))
将'%'
放在不需要的位置。
例如(country LIKE ('USA') OR gender LIKE '%'))
子查询:
SELECT * FROM (SELECT * FROM people WHERE country like $search1%)WHERE Gender = $search2 ORDER BY ASC
有两种方法
-
在MySql中创建视图对于这样复杂的查询
-
在php中编写一个函数来构建上面的查询
<?php function searchQuery($key,$gender,$order){ $sql = "SELECT * FROM people WHERE country like %$key% AND Gender = $gender ORDER BY $order" $query= mysql_query($sql); while($data= mysql_fetch_array($query)) { //Do your stuff here and collect in one variable say $searchData } return $searchData }
希望对你有帮助
你可以把这些组合起来。首先检查过滤器是否有值。
使用if条件进行检查,并连接一个字符串,然后执行它,如。
$sql="SELECT * FROM people WHERE 1 ";
if($search1% != NULL){
$sql.="AND country like $search1% ";
}
elseif($search2 != NULL){
$sql.="AND Gender = $search2 ";
}
$sql.="ORDER BY ASC ";