How Do I Filter Database Results Based on a Dropdown Selection?

When creating dynamic web applications, you often encounter situations where you need to display data filtered according to user input, such as selecting an option from a dropdown bar. Today, I faced a similar issue where I needed to filter the contents of a database table based on the selection made in a dropdown menu, but my current script didn’t support that functionality. Let’s dive into the issue and see how it can be fixed.

The Original Script Analysis

The original script provided fetches data from a database without considering user input from the dropdown. I’ll quickly break down the script:

$sql = "SELECT CPTE FROM comana";
$stmt = oci_parse($conn, $sql); 

oci_execute($stmt);

while ($row = oci_fetch_assoc($stmt)) {
    echo "<tr>\n";
    echo "<td>" . ($row['CPTE'] !== null ? htmlentities($row['CPTE'], ENT_QUOTES) : '') . "</td>\n";
    echo "</tr>\n";
}
echo "</table>\n";

This script connects to an Oracle database, retrieves all the entries under the CPTE column from the comana table, and then displays them in an HTML table.

Implementing the Dropdown Filter

To filter these results based on a dropdown selection, you need a mechanism to pass the selected value back to the server, modify the SQL query to take this value into account, and then display the filtered results. Here’s how we can go about it:

1. Add a Dropdown to the HTML: First, we need a dropdown in our HTML through which the user can select a filter option. This dropdown should be populated dynamically from the database to ensure it includes all current values.

<form method="post" action="your_script.php">
    <select name="filterOption">
        <?php
        $optionsQuery = "SELECT DISTINCT CPTE FROM comana";
        $optionsStmt = oci_parse($conn, $optionsQuery);
        oci_execute($optionsStmt);
        while ($option = oci_fetch_assoc($optionsStmt)) {
            echo "<option value=\"" . htmlentities($option['CPTE'], ENT_QUOTES) . "\">" . htmlentities($option['CPTE'], ENT_QUOTES) . "</option>";
        }
        ?>
    </select>
    <input type="submit" value="Filter">
</form>

1. Modify the PHP Script: You need to modify the existing PHP script to take into account the option selected by the user. This involves capturing the submitted dropdown value and modifying the SQL query to filter based on this value:

if (!empty($_POST['filterOption'])) {
    $selectedOption = $_POST['filterOption'];

    // Make sure to escape the $selectedOption to prevent SQL injection
    $selectedOption = oci_escape_string($conn, $selectedOption);

    $sql = "SELECT CPTE FROM comana WHERE CPTE = :selectedOption";
    $stmt = oci_parse($conn, $sql);

    // Bind the selected option to the parameter in the SQL query
    oci_bind_by_name($stmt, ":selectedOption", $selectedOption);

    oci_execute($stmt);

    while ($row = oci_fetch_assoc($stmt)) {
        echo "<tr>\n";
        echo "<td>" . ($row['CPTE'] !== null ? htmlentities($row['CPTE'], ENT_QUOTES) : '') . "</td>\n";
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "Please select an option from the dropdown.";
}

This revised script now includes a dropdown menu for selecting the CPTE and a modified PHP block that applies the selected filter to the database query. Now, when a user selects an item from the dropdown menu and submits the form, the PHP script captures this input, safely inserts it into a SQL query (preventing SQL injection), and displays only the data relevant to the selected option.

Always ensure to escape or validate any user inputs to avoid SQL injection, a common security vulnerability. In the above PHP code, oci_escape_string hypothetically represents a function to escape strings for Oracle SQL queries since PHP’s oci8 extension does not have a built-in escape function. You should implement a proper escaping or more preferably use parameter binding as shown.