Combination of field search using PHP & MYSQL

Question

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 contains

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

If you are just learning now, STOP using the deprecated mysql extension and use the mysqli (us1.php.net/manual/en/book.mysqli.php) extension. It supports, among other things, prepared statements, which would make your code much more manageable. Also the code !isset($lName); does not unset the variable, it returns whether the variable is already set. To unset a variable, use unset($lName); — Anthony
– Anthony, Commented Feb 11, 2014 at 2:11
Why do you need to unset those variables at all? You never refer to them outside the if blocks. — Barmar
– Barmar, Commented Feb 11, 2014 at 2:14
@Anthony Actually, dynamically generated SQL like this is harder to do with mysqli prepared statements, because it's difficult to call mysqli_stmt_bind_params with a dynamic set of parameters. This type of thing is much easier in PDO, since you can use an array of parameters. — Barmar
– Barmar, Commented Feb 11, 2014 at 2:16
The parameters don't need to be dynamic. It's a tad convoluted, but you since there are a finite set of fields and values to compare them to, in the case of the value being 'All', the input could be modified to simply leave those blank (have the dropdown show 'All' and the value be empty), and then have the query check for WHERE (sport = $sport OR $sport = '') which will return all rows when the value is blank and the matches when set. And you should not discourage anyone from using something other than the mysql api. I don't care if they switch to PDO or MYSQLI, the goal is to switch. — Anthony
– Anthony, Commented Feb 11, 2014 at 2:39

Community · Accepted Answer · 2017-05-23 12:05:31Z

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

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?

Quixrick · Accepted Answer · 2014-02-11 02:37:47Z

I've done something similar in the past where I checked the value from different fields and then added them to a series of arrays. I created an array for select, from, where, order. You can do similar for other sets like group or limit. Then I ran 'array_unique', imploded them and put them into the SQL string.

$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.' ...

Trent · Accepted Answer · 2014-02-11 02:51:53Z

0

Why not evaluate your string with each column (this is a guide only, I'm not building your PHP code there:

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

Just need to make sure you NVL the variables to an appropriate value (whether it be int or string)

edited Feb 11, 2014 at 2:51

answered Feb 11, 2014 at 2:19

Trent

3,1332 gold badges37 silver badges52 bronze badges

2 Comments

Barmar Over a year ago

Also, you're missing quotes around all the variables.

Trent Over a year ago

Feel free to edit as you see fit - it's more a push in the right direction than giving him code for his homework

Collectives™ on Stack Overflow

Combination of field search using PHP & MYSQL

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related