Matt Hammond Matt Hammond - 6 months ago 12
SQL Question

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?

Answer

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($gameFilter).'"';
}
if (strlen($regionFilter) > 0) {
    $filters[] = 'region = "'.mysqli_escape_string($regionFilter).'"';
}
if (strlen($consoleFilter ) > 0) {
    $filters[] = 'console= "'.mysqli_escape_string($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