我在创建功能时遇到了一个搜索表单的小问题。我基本上想要一个表单(在任何页面上)转到这个页面,然后列出数据库中的相关行。我的问题是表单同时有一个文本字段和一个选择字段(用于名称和类别),我无法创建让这两个值一起搜索数据库的功能。
所以我想这样做:当你只输入名称而不输入类别时,它将只显示名称,反之亦然,对于类别而没有名称;则当两者都在一起时,它只显示它们都在中的行
以下是我目前所拥有的:
// 2. Create variables to store values
if(!$_GET['search-category'] == "") {
$searchName = $_GET['search-name'];
}
if(!$_GET['search-category'] == "select-your-category") {
$searchCat = $_GET['search-category'];
}
// 2. Create the query for the stored value. Matching it against the name, summary and sub type of my item.
$mainSearch = "SELECT attraction.*, type.type_name, sub_type.sub_type_name ";
$mainSearch .= "FROM attraction ";
$mainSearch .= "INNER JOIN sub_type ON attraction.sub_type = sub_type.sub_type_id ";
$mainSearch .= "INNER JOIN type ON attraction.type = type.type_id ";
$mainSearch .= "WHERE attraction.name LIKE '%" . $searchName . "%' AND (sub_type.sub_type_name LIKE '%" . $searchCat . "%' )";
$mainSearch .= "ORDER BY sub_type_name ASC";
// 2. run query
$result2 = $con->query($mainSearch);
if (!$result2) {
die('Query error: ' . mysqli_error($result2));
}
我会将代码重构为类似-的代码
foreach( $_GET['filters'] as $fname => $fval ) {
if( !$fval ) continue;
$where[] = "$fname LIKE '%{$fval}%'";
}
您只需要在查询中包括那些非空的输入。此外,您还需要解决安全问题,如转义输入等。
您可以声明一个名为$search_condition的变量,并根据$searchName或$searchCat是否为null为$search-condition 赋值
例如
if (isset($searchName ) || !is_empty($searchName ))
{
$search_condition = "WHERE attraction.name LIKE '%" . $searchName;
}
if (isset($searchCat ) || !is_empty($searchCat ))
{
$search_condition = "sub_type.sub_type_name LIKE '%" . $searchCat . "%'";
}
if ((isset($searchName ) || !is_empty($searchName )) && (isset($searchCat ) || !is_empty($searchCat )))
{
$search_condition = "WHERE attraction.name LIKE '%" . $searchName . "%' AND (sub_type.sub_type_name LIKE '%" . $searchCat . "%' )";
}
希望这能帮助你
感谢
这是一个注释,但我想利用格式化选项。。。
你知道,你可以用这种方式重写。。。
// 2. Create the query for the stored value. Matching it against the name, summary and sub type of my item.
$mainSearch = "
SELECT a.*
, t.type_name
, s.sub_type_name
FROM attraction a
JOIN sub_type s
ON a.sub_type = s.sub_type_id
JOIN type t
ON a.type = t.type_id
WHERE a.name LIKE '%$searchName%'
AND s.sub_type_name LIKE '%$searchCat%'
ORDER
BY s.sub_type_name ASC;
";
您可以检查相关值是否为空:
// 2. Create the query for the stored value.
// Matching it against the name, summary and sub type of my item.
$mainSearch = "SELECT attraction.*, type.type_name, sub_type.sub_type_name ";
$mainSearch .= "FROM attraction ";
$mainSearch .= "INNER JOIN sub_type ON attraction.sub_type = sub_type.sub_type_id ";
$mainSearch .= "INNER JOIN type ON attraction.type = type.type_id ";
$mainSearch .= "WHERE ";
if ($searchName) {
$mainSearch .= "attraction.name LIKE '%" . $searchName . "%'";
if ($searchCat) {
$mainSearch .= " AND ";
}
}
if ($searchCat) {
$mainSearch .= "sub_type.sub_type_name LIKE '%" . $searchCat . "%'"
}
$mainSearch .= "ORDER BY sub_type_name ASC";
// Double check that at least one of the search criteria is filled:
if (!$searchName && !$searchCat) {
die("Must supply either name search or category search");
}