php - 使用 PHP 和 MYSQL 组合字段搜索

Question

我正在使用 PHP 和 MYSQL 完成一项作业。

任务之一是搜索任意字段组合。这包括从数据库填充的下拉框。和文本字段。

t2ath 包含

ID
SPORT
COUNTRY
GENDER
FIRSTNAME
LASTNAME
Image

我已经为此代码工作了一个星期，以便能够毫无错误地搜索任何组合。

我想知道是否有另一种更有效的方法来做到这一点。

$selectedSport = $_POST['sport']; $gender =$_POST['gender']; $fName =$_POST['fname']; $lName =$_POST['lname']; $country =$_POST['country'];
$sql_fName=""; $sql_lName=""; $sql_gender=""; $sql_sport=""; $sql_country="";
$checkFiled=False;
$where="";
$and="";
//
if ( $selectedSport=="showAll")
    {
        !isset($selectedSport);
    }
else
    {
        if (isset($selectedSport)) 
            { 
                if ($checkFiled==True)
                    {

                        $sql_sport = " AND t2ath.sport = '$selectedSport'" ; 

                    }
                else
                    {
                        $sql_sport = " t2ath.sport = '$selectedSport' " ; 
                        $checkFiled=True;   
                    } 
            }
        else {
            $sql_sport = "";  
        }
    }

//
if ( $country =="showAll")
    {
        !isset($country);
    }
else
    {
        if (isset($country)) 
            { 
                if ($checkFiled ==True)
                    {

                        $sql_country = " AND t2ath.country = '$country'" ; 

                    }
                else
                    {
                        $sql_country = " t2ath.country = '$country' " ; 
                        $checkFiled=True;
                    } 
            }
        else {
            $sql_country = "";  
        }
    }
//
if ( $gender=="Gender")
    {
        !isset($gender);
    }
else
    {
        if (isset($gender)) 
            { 
                if ($checkFiled ==True)
                    {

                        $sql_gender = " AND t2ath.gender = '$gender'" ; 

                    }
                else
                    {
                        $sql_gender = " t2ath.gender = '$gender' " ; 
                        $checkFiled=True;
                    } 
            }
        else {
            $sql_gender = "";  
        }
    }
//
if ($fName =="")
    {
        !isset($fName);
    }
else
    {
        if (isset($fName)) 
            { 
                if ($checkFiled==True)
                    {

                        $sql_fName = " AND t2ath.firstName = '$fName'" ; 
                    }
                else
                    {
                        $sql_fName = " t2ath.firstName = '$fName' " ; 
                        $checkFiled=True;   
                    } 
            }
        else {
            $sql_fName = "";  
        }
    }
//
if ($lName =="")
    {
        !isset($lName);
    }
else
    {
        if (isset($lName)) 
            { 
                if ($checkFiled==True)
                    {

                        $sql_lName = " AND t2ath.lastName = '$lName' " ; 

                    } 
                else
                    {
                        $sql_lName = " t2ath.lastName = '$lName' " ; 
                        $checkFiled=True;
                    }
            }
        else
            {
                $sql_lName = "";  
            }
    }

if ($checkFiled == True)
    $where=" where ";

$selectString = "SELECT t2ath.lastName,t2ath.firstName,t2ath.image,t2ath.sport,t2ath.gender,t2ath.country,t2country.flag FROM t2ath LEFT JOIN t2country
                 ON t2ath.country = t2country.name $where  $sql_sport   $sql_country $sql_gender $sql_fName $sql_lName  ";
$result = mysql_query($selectString);

Answer 1

不要使用有关在连接查询时是否添加 AND 的所有条件，而是使用数组和 implode。

$fields = array('sport' => 'sport',
                'gender' => 'gender', 
                'fname' => 'firstName',
                'lname' => 'lastName',
                'country' => 'country');
$wheres = array();
foreach ($fields as $postfield => $dbfield) {
    if ($_POST[$postfield] != 'showAll') {
        $wheres[] = "$dbfield = '" . mysql_real_escape_string($_POST[$postfield]) . "'";
    }
}
$selectString = "SELECT t2ath.lastName, t2ath.firstName, t2ath.image, t2ath.sport, t2ath.gender, t2ath.country, t2country.flag 
                 FROM t2ath LEFT JOIN t2country
                 ON t2ath.country = t2country.name";
if (count($wheres) > 0) {
    $selectString .= " WHERE " . implode(" AND ", $wheres);
}
$result = mysql_query($selectString);

要了解如何使用 PDO 准备好的语句进行类似的操作，请参阅我的答案:What code approach would let users apply three optional variables in PHP/MySQL?