CODEWITHSUNDEEP
phpmysqlsql
Matt Hammond
Matt Hammond

Reputation: 785

SELECT Query, WHERE selects all when "" is empty

The Aim

Hi, I'm trying to shorten my code by building the query dynamically based on the $_GET. Current I have every possible If statement with the relevant SELECT query. However I would like to create a dynamic system for feature updates.

Current Progress

//Set Filter based on url
        if ($_GET[GAME] != "") { $gameFilter = $_GET[GAME]; } else { $gameFilter = ''; }
        if ($_GET[Region] != "") { $regionFilter = $_GET[Region]; } else { $regionFilter = ''; }
        if ($_GET[Console] != "") { $consoleFilter = $_GET[Console]; } else { $consoleFilter = ''; }

    $result = get_matchfinder($gameFilter, $regionFilter, $consoleFilter);

//The Function

function get_matchfinder($gameFilter, $regionFilter, $consoleFilter) {
        //Set Varibles
        $database = 'matchFinder';
        $order = 'DESC';
        $limit = '20';

    //Query Function
    $connection = connection();
    $sql = 'SELECT * FROM  '. $database .' WHERE game = "'.$gameFilter.'" AND region = "'.$regionFilter.'" AND console = "'.$consoleFilter.'" ORDER BY ID '. $order .' LIMIT '. $limit .'';
    $response = mysqli_query($connection, $sql);   

    //Return
    return $response;
}

Problem Currenly it works when all of the filters are active but if one of them isn't the whole query fails, I know thats because it is try to SELECT something matching ''.

So my questions is how do I make it search for all when that filters is not set?

Upvotes: 0

Views: 79

Answers (3)

Chris Baker
Chris Baker

Reputation: 50612

You should build the query parts depending on the length of the filter:

$sql = '
    SELECT * FROM '.$database.'
';

$filters = array();
if (strlen($gameFilter) > 0) {
    $filters[] = 'game = "'.mysqli_escape_string($connection, $gameFilter).'"';
}
if (strlen($regionFilter) > 0) {
    $filters[] = 'region = "'.mysqli_escape_string($connection, $regionFilter).'"';
}
if (strlen($consoleFilter ) > 0) {
    $filters[] = 'console= "'.mysqli_escape_string($connection, $consoleFilter).'"';
}

if (count($filters) > 0) {
    $sql .= ' WHERE '.implode(' AND ', $filters);
}

if (strlen($oder) > 0) {
    $sql .= ' ORDER BY ID '.$order;
}
if ($limit > 0) {
    $sql .= ' LIMIT '.$limit;
}

$response = mysqli_query($connection, $sql);

What you're doing there is building up an array of conditions, based on the length of the condition. If the condition's input is an empty string, it isn't added to the array. At the end, if there are any filters, use implode to bind the conditions into a string. The way implode works, if there's only one condition, the glue string isn't used.

It also bears mentioning that you are exposing yourself to SQL injection. The above code shows the use of mysqli_escape_string to escape the input, but you should look in to parameterized queries to take full precaution: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php -- the above sample would only be slightly different if you used paraterized queries, but significantly more safe.

Documentation

Upvotes: 2

cableload
cableload

Reputation: 4385

You would have to build your search dynamically

You could start your base query with 

$sql='SELECT * FROM  '. $database .' WHERE 1=1'

Then, if $gameFilter!="", append to your existing $sql string with "and game=$gameFilter"

The syntax for appending would be like this

if ($gameFilter!="")
 {
    $sql.=' and game=$gameFitler'
 }

and so on checking for all of your search conditions.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Make you string like this

$sql = 'SELECT * FROM  '. $database .' WHERE 1=1 {0} {1} {2}'

if ( $gameFilter <> '') 
    $sql = str_replace("{0}", "AND game = '".$gameFilter."'" , $sql);
else 
    $sql = str_replace("{0}", "" , $sql);

if ( $regionFilter <> '') 
    $sql = str_replace("{1}", "AND region = '".$regionFilter."'" , $sql);
else 
    $sql = str_replace("{1}", "" , $sql);

if ( $consoleFilter <> '') 
    $sql = str_replace("{2}", "AND console = '".$consoleFilter."'" , $sql);
else 
    $sql = str_replace("{2}", "" , $sql);

Upvotes: 1

Related Questions