So I am building a search form which has a lot of options for users to select from. As you can see from the image below a user selects a search criteria and it allows them to enter or check what they like. If this is unticked it removes all values/unchecks all the boxes.
所以我正在构建一个搜索表单,其中有很多选项供用户选择。从下图中可以看出,用户选择搜索条件并允许他们输入或检查他们喜欢的内容。如果未取消,则删除所有值/取消选中所有框。
I originally only had weight/height/gender as the search but since then have added more.
我最初只有体重/身高/性别作为搜索,但从那以后增加了更多。
I already coded the weight/height/gender search options and it ended being a lot of if statements checking what was selected/null then creating the appropriate MYSQL queries.
我已经编码了权重/高度/性别搜索选项,它结束了很多if语句检查选择的内容/ null然后创建适当的MYSQL查询。
I am not entirely sure how I should go about (if I should start again) with the rest of the options. Is there an easier away around this? I just need some direction so I can make this a bit more effective.
我不完全确定我应该如何处理(如果我应该重新开始)其他选项。围绕这个更容易吗?我只是需要一些方向,所以我可以让它更有效。
Thanks!
require 'functions.php';
//Search data
//$weight_min = $_POST['weight_min'];
//$weight_max = $_POST['weight_max'];
//$height_min = $_POST['height_min'];
//$height_max = $_POST['height_max'];
//$gender_select = $_POST['gender_select'];
if("" == trim($_POST['weight_min'])){
$weight_min = '';
}
else
{
$weight_min = $_POST['weight_min'];
}
if("" == trim($_POST['weight_max'])){
$weight_max = '';
}
else
{
$weight_max = $_POST['weight_max'];
}
if("" == trim($_POST['height_min'])){
$height_min = '';
}
else
{
$height_min = $_POST['height_min'];
}
if("" == trim($_POST['height_max'])){
$height_max = '';
}
else
{
$height_max = $_POST['height_max'];
}
if (!isset($_POST['gender_select'])){
$gender_select = '';
}
else
{
$gender_select = $_POST['gender_select'];
}
//Show test
//echo "sent: weight-min: " .$weight_min. " weight-max: " .$weight_max. " height-min: ".$height_min." height-max: ".$height_max." gender-select: ".$gender_select."<p>";
check_null_values($weight_min, $weight_max, $height_min, $height_max, $gender_select);
function check_null_values($weight_min, $weight_max, $height_min, $height_max, $gender_select)
{
//Weight
if($weight_min !=null && $weight_max != null && $height_min == null && $height_max == null && $gender_select == null)
{
select_weight($weight_min, $weight_max);
//echo "select_weight";
}
//Height
else if($weight_min == null && $weight_max == null && $height_min != null && $height_max != null && $gender_select == null)
{
select_height($height_min, $height_max);
//echo "select_height";
}
//Gender
else if($weight_min == null && $weight_max == null && $height_min == null && $height_max == null && $gender_select != null)
{
select_gender($gender_select);
//echo "select_gender";
}
//Weight + Height
else if($weight_min != null && $weight_max != null && $height_min != null && $height_max != null && $gender_select == null)
{
select_weight_height($weight_min, $weight_max, $height_min, $height_max);
//echo "select_weight_height";
}
//Weight + Gender
else if($weight_min != null && $weight_max != null && $height_min == null && $height_max == null && $gender_select != null)
{
select_weight_gender($weight_min, $weight_max, $gender_select);
//echo "select_weight_gender";
}
//Height + Gender
else if($weight_min == null && $weight_max == null && $height_min != null && $height_max != null && $gender_select != null)
{
select_height_gender($height_min, $height_max, $gender_select);
//echo "select_height_gender";
}
//All
else if($weight_min != null && $weight_max != null && $height_min != null && $height_max != null && $gender_select != null)
{
select_all($weight_min, $weight_max, $height_min, $height_max, $gender_select);
//echo "select_all";
}
else if($weight_min == null && $weight_max == null && $height_min == null && $height_max == null && $gender_select == null)
{
select_none();
//echo "select_none";
}
else
{
//echo "Please enter missing parameter";
}
}
//Weight only selected
function select_weight($weight_min, $weight_max)
{
include 'db_connect.php';
$result = mysqli_query($db,
"SELECT * FROM character_information
WHERE
(char_min_weight BETWEEN '".$weight_min."' AND '" .$weight_max."'
OR char_max_weight BETWEEN '".$weight_min."' AND '" .$weight_max."')
OR
('".$weight_min."' BETWEEN char_min_weight AND char_max_weight
OR '" .$weight_max."' BETWEEN char_min_weight AND char_max_weight)
");
return get_result($result);
}
//Height only selected
function select_height($height_min, $height_max)
{
include 'db_connect.php';
$result = mysqli_query($db,
"SELECT * FROM character_information
WHERE
(char_min_height BETWEEN '".$height_min."' AND '" .$height_max."'
OR char_max_height BETWEEN '".$height_min."' AND '" .$height_max."')
OR
('".$height_min."' BETWEEN char_min_height AND char_max_height
OR '" .$height_max."' BETWEEN char_min_height AND char_max_height)
");
get_result($result);
}
//Gender only selected
function select_gender($gender_select)
{
include 'db_connect.php';
$result = mysqli_query($db,
"SELECT * FROM character_information
WHERE char_gender = '".$gender_select."'
");
get_result($result);
}
//Weight + Height selected
function select_weight_height($weight_min, $weight_max, $height_min, $height_max)
{
include 'db_connect.php';
$result = mysqli_query($db,
"SELECT * FROM character_information
WHERE
((char_min_weight BETWEEN '".$weight_min."' AND '" .$weight_max."'
OR char_max_weight BETWEEN '".$weight_min."' AND '" .$weight_max."')
OR
('".$weight_min."' BETWEEN char_min_weight AND char_max_weight
OR '" .$weight_max."' BETWEEN char_min_weight AND char_max_weight))
AND
((char_min_height BETWEEN '".$height_min."' AND '" .$height_max."'
OR char_max_height BETWEEN '".$height_min."' AND '" .$height_max."')
OR
('".$height_min."' BETWEEN char_min_height AND char_max_height
OR '" .$height_max."' BETWEEN char_min_height AND char_max_height))
");
get_result($result);
}
2 个解决方案
#1
I'm using this way :
我正在使用这种方式:
if(empty($_GET['weightMin'])) {
$weightMin= null;
} else $weightMin= $_GET['weightMin'];
if(empty($_GET['weightMax'])) {
$weightMax= null;
} else $weightMin= $_GET['weightMax'];
And the statement would be :
声明如下:
SELECT * FROM TABLE
WHERE ((weight >= :weighttMin AND weight <= :weightMax) OR (weight >= :weightMin AND :weightMax is null) OR (weight <= :weightMax AND :weightMin is null) OR (:weightMax is null AND :weightMin is null))
This is pretty long when it is x < filter < y
当x
Else if this is only one type like 'Gender' :
否则,如果这只是“性别”这样的一种类型:
if(empty($_GET['gender'])) {
$gender = null;
} else $gender = $_GET['gender'];
The SQL:
SELECT * FROM TABLE
WHERE (gender = :gender or :gender is null)
If gender is selected, it will search the good one, else it returns true and doesn't impact your statement.
如果选择了性别,它将搜索好的性别,否则返回true并且不会影响您的陈述。
The combined query:
合并查询:
SELECT * FROM TABLE
WHERE
((weight >= :weighttMin AND weight <= :weightMax) OR (weight >= :weightMin AND :weightMax is null) OR (weight <= :weightMax AND :weightMin is null) OR (:weightMax is null AND :weightMin is null))
AND
(gender = :gender or :gender is null)
#2
Here's pseudo code for a common method to build the query dynamically:
这是动态构建查询的常用方法的伪代码:
$qry = 'SELECT * FROM character_information WHERE ';
// If the user entered a max height criteria
if ($max_height) {
$qry .= 'char_weight <= :max_height';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
// If the user entered a min height criteria
if ($min_height) {
$qry .= 'char_weight >= :min_height';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
// If the user entered a body build criteria
if ($body_build) {
$qry .= 'char_body_build = :body_build';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
...
// Prepare and bind params here
#1
I'm using this way :
我正在使用这种方式:
if(empty($_GET['weightMin'])) {
$weightMin= null;
} else $weightMin= $_GET['weightMin'];
if(empty($_GET['weightMax'])) {
$weightMax= null;
} else $weightMin= $_GET['weightMax'];
And the statement would be :
声明如下:
SELECT * FROM TABLE
WHERE ((weight >= :weighttMin AND weight <= :weightMax) OR (weight >= :weightMin AND :weightMax is null) OR (weight <= :weightMax AND :weightMin is null) OR (:weightMax is null AND :weightMin is null))
This is pretty long when it is x < filter < y
当x
Else if this is only one type like 'Gender' :
否则,如果这只是“性别”这样的一种类型:
if(empty($_GET['gender'])) {
$gender = null;
} else $gender = $_GET['gender'];
The SQL:
SELECT * FROM TABLE
WHERE (gender = :gender or :gender is null)
If gender is selected, it will search the good one, else it returns true and doesn't impact your statement.
如果选择了性别,它将搜索好的性别,否则返回true并且不会影响您的陈述。
The combined query:
合并查询:
SELECT * FROM TABLE
WHERE
((weight >= :weighttMin AND weight <= :weightMax) OR (weight >= :weightMin AND :weightMax is null) OR (weight <= :weightMax AND :weightMin is null) OR (:weightMax is null AND :weightMin is null))
AND
(gender = :gender or :gender is null)
#2
Here's pseudo code for a common method to build the query dynamically:
这是动态构建查询的常用方法的伪代码:
$qry = 'SELECT * FROM character_information WHERE ';
// If the user entered a max height criteria
if ($max_height) {
$qry .= 'char_weight <= :max_height';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
// If the user entered a min height criteria
if ($min_height) {
$qry .= 'char_weight >= :min_height';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
// If the user entered a body build criteria
if ($body_build) {
$qry .= 'char_body_build = :body_build';
} else {
$qry .= '1 = 1';
}
$qry .= ' AND ';
...
// Prepare and bind params here