Geek Answers Handbook

Combination of field search using PHP and MYSQL

I am working on assignment using PHP and MYSQL.

One of the tasks is to search for any combination of fields. This includes pop-ups populated from the database. and text fields.

t2ath contains

ID
SPORT
COUNTRY
GENDER
FIRSTNAME
LASTNAME
Image

I have been working on this code for a week to be able to search in any combination without errors.

I am wondering if there is an even more efficient way to do this.

$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);
0
source share
3 answers

Instead of all these conditions about whether to add ANDwhen concatenating a request, use the and array 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, . : PHP/MySQL?

+1

( , PHP- :

SELECT 
  * 
FROM 
  table
WHERE 
  (ID = $id OR $id = 'showAll')
  AND (SPORT = $sport OR $sport = 'showAll')
  AND (COUNTRY = $country OR $country = 'showAll')
  AND (GENDER = $gender OR $gender = 'showAll')
  AND (FIRSTNAME = $firstname OR $firstname = 'showAll')

, NVL ( int string)

0

- , , . select, from, where, order. , . 'array_unique', SQL.

$array_select = array('users.Id'); // SET SOME DEFAULTS SO THE QUERY WILL ALWAYS RUN
$array_from = array('users');
$array_where = array();
$array_order = array();

if (isset($first_name)) {
    $array_select[] = 'First_Name';
    $array_from[] = 'users';
}

if (isset($city)) {
    $array_select[] = 'City';
    $array_from[] = 'user_contact';
    $array_where[] = 'users.Id = user_contact.City';
}

if ($array_select) {
    $array_select = array_unique($array_select);
    $string_select = implode(', ', $array_select);
}
if ($array_where) {
    $array_where = array_unique($array_where);
    $string_where = 'WHERE '.implode(' AND ', $array_where);
}
// REPEAT FOR OTHERS ...



// BUILD THE QUERY OUT
$sql = 'SELECT '.$string_select.' FROM '.$string_from.' '.$string_where.' ...
0
source

More articles:


All Articles