找到你要的答案

Q:Best way to code PHP/MYSQL search with options [closed]

Q:代码的PHP / MySQL选项[关闭]搜索最好的方式

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.

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);
	}

所以我正在建立一个搜索表单,有很多选项供用户选择。如下图所示,用户选择搜索标准,允许用户输入或检查他们喜欢的内容。如果这是unticked它删除所有值/不选所有的盒子。

我本来只有体重/身高/性别作为搜索,但自那时以来增加了更多。

我已编码的体重/身高/性别的搜索选项,并最终被很多if语句检查所选择/空然后创建适当的MySQL查询。

我不完全知道我应该如何去(如果我应该重新开始)与其余的选项。这周围有更容易的地方吗?我只是需要一些方向,所以我可以使这一点更有效。

谢谢!

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);
	}
answer1: 回答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

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.

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)

我用这种方式:

if(empty($_GET['weightMin'])) {
    $weightMin= null;
} else $weightMin= $_GET['weightMin'];

if(empty($_GET['weightMax'])) {
    $weightMax= null;
} else $weightMin= $_GET['weightMax'];

声明将:

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))

这是相当长的时候,它是X和LT;过滤器和LT;Y

如果这只是一种类型的“性别”:

if(empty($_GET['gender'])) {
    $gender = null;
} else $gender = $_GET['gender'];

SQL:

SELECT * FROM TABLE
WHERE (gender = :gender or :gender is null)

如果性别被选中,它会搜索好的,否则返回true,不影响您的声明。

组合查询:

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)
answer2: 回答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

这里是用于动态构建查询的常用方法的伪代码:

$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
php  mysql  forms  search  filtering