Database filtering with multiple categories

I’m learning how to work with PHP and MySQL and I’m trying to filter the data with multiple categories. Now I have this form:

<form id="filter" action="submit.php" method="get">
    <h2>Date of release:</h2>
    <input type="checkbox" name="release[]" value="2000"><label>2000</label><br>
    <input type="checkbox" name="release[]" value="2002"><label>2002</label><br>
    <input type="checkbox" name="release[]" value="2011"><label>2011</label>

    <h2>Developer:</h2>
    <input type="checkbox" name="developer[]" value="Rockstar"><label>Rockstar</label><br>
    <input type="checkbox" name="developer[]" value="Valve"><label>Valve</label><br>

    <h2>Country:</h2>
    <input type="checkbox" name="country[]" value="USA"><label>USA</label><br>
    <input type="checkbox" name="country[]" value="Sweden"><label>Sweden</label><br>
</form>

And 3 tables in my database (with games, properties and values). It looks like this:

INSERT INTO `games` (`game_id`, `game_name`, `game_link`) VALUES
(1, 'Grand Theft Auto Vice City', '/gta'),
(2, 'Counter Strike 1.6', '/cs'),
(3, 'Minecraft', '/minecraft');

INSERT INTO `attributes` (`attribute_id`, `attribute_name`) VALUES
(1, 'release'),
(2, 'developer'),
(3, 'country');

INSERT INTO `games_values` (`game_id`, `attribute_id`, `value`) VALUES
(1, 1, '2002'),
(1, 2, 'Rockstar'),
(2, 1, '2000'),
(2, 2, 'Valve'),
(2, 3, 'USA'),
(3, 1, '2011'),
(3, 3, 'Sweden');

My submit.php looks like this:

if(isset($_GET["release"])) {
    $release = $_GET["release"];
    $releaseStr = implode(', ', $release);
    $statement = $pdo -> prepare("
        SELECT game_name, game_link
        FROM games g 
            INNER JOIN games_values v ON v.game_id = g.game_id
            INNER JOIN attributes a ON v.attribute_id = a.attribute_id
        WHERE a.attribute_id = '1' AND value IN ($releaseStr);
    ");
    $statement -> execute($release);
    $result = $statement -> fetchAll(PDO::FETCH_ASSOC);
    $json = json_encode($result);
    echo ($json);
}

Here I filter the data based only on one category: release. What is the best way to filter the data taking all potential categories into account? I would appreciate any help on how should I change my code or change the approach in general.
P.S. Sorry for any language mistakes :sweat_smile:

Shouldn’t that query error out (parameter values supplied for a statement without parameters)?

Note, you’re wide open to SQL injection, and an integer value should not be quoted.

if every category is possible, simply don’t add a WHERE clause.

This becomes much easier to manage if you use a query builder (e.g. Doctrine’s DBAL).

Yeah, I just missed this line
INNER JOIN attributes a ON v.attribute_id = a.attribute_id
I was trying with placeholders using:

$release = $_GET["release"];
$releaseStr = str_repeat('?,', count($release) - 1) . '?';
$developer = $_GET["developer"];
$developerStr = str_repeat('?,', count($developer) - 1) . '?';
$statement = $pdo -> prepare("
    SELECT DISTINCT game_name, game_link
    FROM games g 
        INNER JOIN games_values v ON v.game_id = g.game_id
        INNER JOIN attributes a ON v.attribute_id = a.attribute_id
    WHERE v.attribute_id = 1 AND value IN ($releaseStr)
    	OR v.attribute_id = 2 AND value IN ($developerStr);
");

But then realised that it was also not the best idea

yea, you need to add WHERE clauses depending on what is submitted.

I’m a noob little bit :expressionless: Should I define some $where variable and change it with selected conditions and then substitute it into the query?

You could do it that way, as you do with your “IN” clauses.

That’s one possibility. However, the more complicated the conditions become, the more awkward it gets in getting the SQL for that right.

And even if I have already mentioned it before, there exist libraries for that exact purpose.

Why did you decide to make release, developer, and country attributes instead of adding them as fields to the games table?

Hi. I suggested that sometimes some of this information may not be known so I decided to separate it from the games table

The only reason I asked is because your queries are going to be much more complicated and difficult to optimize with that design. I would HIGHLY suggest scrapping both those attribute tables and simply adding fields as optional with default null values to the games table. If you don’t for see new attributes being added frequently that is by far a better approach. Either that or consider creating a flat table or view with all fields that make queries and optimization a bit easier. The pattern you have used does not lend well to simplicity, performance, scalability, enforcing integrity and type. An alternative that better lends itself to mutable structures is a none relational database like mongo db. A none relational database is much more appropriate for mutable / changing structure than a relational database.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.