Filtering table using button names in assoc array using PHP

Question

What I am trying to achieve

I currently got a huge collection with over thousand rows and 10+ columns parsed into a table with PDO. It is necessary to filter the table afterwards and I decided to use PHP to do this.

My current approach works fine, but I am not sure if it's less efficient performance-wise as there are a lot of else if statements which makes the code looks messy.

My current approach

I got a "Filter Table" HTML button which opens a pop-up with several buttons (15+ or so) inside a form. Each button name has it's own filter query.

I then check which button was clicked and then append the filter query to my actual SQL which parses the whole table.

My code

PHP:

if (isset($_POST['filter_type_pc'])) {
    $newFilter = "WHERE type LIKE 'PC%' ORDER BY time_altered DESC";
} else if (isset($_POST['filter_type_mac'])) {
    $newFilter = "WHERE type LIKE 'Mac%' ORDER BY time_altered DESC";
} else if (isset($_POST['filter_type_linux'])) {
    $newFilter = "WHERE type LIKE 'Linux%' ORDER BY ip DESC";

    //...
    //there are more 'else if' statements, but I've excluded them to maintain a clean question
    //...

} else {
    $newFilter = "ORDER BY time_altered DESC";
}

$sql = "SELECT * FROM myTable $newFilter";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

It might be(?) worth noting that the filter queries can differ completely. I am not only using the WHERE or LIKE clauses.

HTML:

<form id="filterTable" name="filter" method="POST" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
    <h5>Type of device</h5>
    <button type="submit" class="btn btn-light filter_button" name="filter_type_pc">PC</button>
    <button type="submit" class="btn btn-light filter_button" name="filter_type_mac">Mac</button>
    <button type="submit" class="btn btn-light filter_button" name="filter_type_linux">Linux</button>
</form>

My question

To clarify even more I am going to ask a question. What would be the better and cleaner approach instead of this else if mess, if there are any? I am interested in hearing your suggestions as I would like to learn from this and improve from already working code!

_“the sorting queries can differ completely”_ - but they are always static, you don‘t need to insert any dynamic data values into them? Then you could store them all in an array, using your button names as keys. Then you loop over that array, and check if an entry in $_POST for the current key is set. — misorude, Feb 18 '20 at 10:42
Eureka! You're right, as of now (probably not in the future either) I am not inserting any dynamic values into the queries and can't think of any scenario where one would have to do that for just sorting a table. Thanks for your suggestion, works like a charm! — Sanguinary, Feb 18 '20 at 10:57

Sanguinary · Accepted Answer · 2020-09-23T07:58:36.833

Answering my own question so any future visitors like you that stumbles over this post can learn a simplistic solution to this problem.

$filters = array(
    "filter_type_pc" => "My SQL query",
    "filter_type_mac" => "My SQL query",
    "filter_type_linux" => "My SQL query"
);

if (!empty($_POST)) {
    $arrayMatch = array_intersect_key($_POST, $filters);
    foreach(array_keys($arrayMatch) as $filterName) {
        $newFilter = $filters[$filterName];
    }
} else {
    $newFilter = "ORDER BY time_altered DESC";
};

Let's break it down:

First we check if the $_POST request is empty, since we can't know what filter button has been clicked in the DOM using PHP, we just leave it blank.
And because we check the whole super global variable it will take anything in the $_POST request, if you got several requests we solve this by

using array_intersect_key which match the keys in $_POST and $filters array.
create a new array $arrayMatch containing only the matched result(s).

We then loop through the matching keys of our newly created array with foreach() and array_keys(), then name our keys for $filterName.
Finally we can then get our value of our array by defining $newFilter as the value of $filters array by using our new variable $filterName.

EDIT:

I came up with a better solution than the one above, while the above works as intended - it may collide with future implementations as mentioned. This will fix this and will only check for POST requests containing the keys in the array.

$filters = array(
    "filter_type_pc" => "My SQL query",
    "filter_type_mac" => "My SQL query",
    "filter_type_linux" => "My SQL query"
);

$newFilter = "ORDER BY time_altered DESC";
$arrayMatch = array_intersect_key($_POST, $filters);

foreach(array_keys($arrayMatch) as $filterName) {
    if (isset($_POST[$filterName])) {
        $newFilter = $filters[$filterName];
    }
};

Filtering table using button names in assoc array using PHP

1 Answers1