使用PHP和Amp;进行字段搜索的组合MySQL数据库 [英] Combination of field search using PHP & MYSQL

问题描述

我正在使用PHP& MYSQL.

I am working on an assignment using PHP & MYSQL.

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

one of the tasks is to search on any combination of the fields. That includes Dropdown boxes populated from the Database. and Text fields.

t2ath包含

ID
SPORT
COUNTRY
GENDER
FIRSTNAME
LASTNAME
Image

我已经在此代码上工作了一个星期，以便能够在没有错误的情况下搜索任何组合.

I've been working on this code for a week to be able to search on any combination with no errors.

我想知道是否还有另一种更有效的方法.

I am wondering if there is another more efficient way to do it.

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

推荐答案

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

Instead of all those conditionals about whether to add AND when concatenating to the query, use an array 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准备好的语句类似地执行此操作，请在此处查看我的答案:

To see how to do it similarly using PDO prepared statements, see my answer here: What code approach would let users apply three optional variables in PHP/MySQL?

这篇关于使用PHP和Amp;进行字段搜索的组合MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！