使用选项编写PHP / MYSQL搜索的最佳方法[关闭]

Question

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

</div>

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