Adding value from HTML select into MySQL query

Question

I've been re-learning HTML & MySQL, and diving into PHP lately. I'm having some trouble (likely due to the fact that I'm still learning PHP) getting a situation to work correctly. I have an instance of MySQL running on my server, which is also an IIS7 web server, with PHP installed. I've created a form with HTML & PHP to add entries to the table, and another to display the table, and filter it (if needed). Currently it looks messy to me, and would also say it's wet. Trying to follow the principals of DRY, I'd like to change this latter piece (which currently uses a text box & button for each possible table column to filter on) to use a dropdown to select the column to filter, and a single text box to fill in a value to filter for. This is my current code (with multiple text boxes & buttons):

<?php

/**
 * Function to query information based on 
 * a parameter: in the first case, track_eps.
 *
 */

if (isset($_POST['search_eps'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list
    WHERE track_eps = :track_eps";

        $track_eps = $_POST['track_eps'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_eps', $track_eps, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
if (isset($_POST['search_artist'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list
    WHERE track_artist = :track_artist";

        $track_artist = $_POST['track_artist'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_artist', $track_artist, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
if (isset($_POST['search_album'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list
    WHERE track_album = :track_album";

        $track_album = $_POST['track_album'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_album', $track_album, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
if (isset($_POST['search_year'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list
    WHERE track_year = :track_year";

        $track_year = $_POST['track_year'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_year', $track_year, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
if (isset($_POST['search_pick'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list
    WHERE track_pick = :track_pick";

        $track_pick = $_POST['track_pick'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_pick', $track_pick, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
if (isset($_POST['view'])) {
    try  {

        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT * 
        FROM track_list";

        $track_pick = $_POST['track_pick'];

        $statement = $connection->prepare($sql);
        $statement->bindParam(':track_pick', $track_pick, PDO::PARAM_STR);
        $statement->execute();

        $result = $statement->fetchAll();
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
?>
<?php require "templates/header.php"; ?>

<?php  
if (isset($_POST['search_eps'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_eps']); ?>.</blockquote>
    <?php } 
}
if (isset($_POST['search_artist'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_artist']); ?>.</blockquote>
    <?php } 
}
if (isset($_POST['search_album'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_album']); ?>.</blockquote>
    <?php } 
}
if (isset($_POST['search_year'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_year']); ?>.</blockquote>
    <?php } 
}
if (isset($_POST['search_pick'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_pick']); ?>.</blockquote>
    <?php } 
}
if (isset($_POST['view'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>

        <table>
            <thead>
                <tr>
            <th>Title</th>
            <th>Artist</th>
            <th>Album</th>
            <th>Year</th>
            <th>Episode</th>
            <th>Picked By</th>
        </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["track_name"]); ?></td>
                <td><?php echo escape($row["track_artist"]); ?></td>
                <td><?php echo escape($row["track_album"]); ?></td>
                <td><?php echo escape($row["track_year"]); ?></td>
                <td><?php echo escape($row["track_eps"]); ?></td>
                <td><?php echo escape($row["track_pick"]); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['track_pick']); ?>.</blockquote>
    <?php } 
} ?> 

<h2>Find track based on Episode #</h2>

<form method="post">
    <input type="submit" name="view" value="View All">
    <label for="track_eps">Episode #</label>
    <input type="text" id="track_eps" name="track_eps">
    <input type="submit" name="search_eps" value="Search Episodes">
    <label for="track_artist">Artist</label>
    <input type="text" id="track_artist" name="track_artist">
    <input type="submit" name="search_artist" value="Search Artist">
    <label for="track_album">Album</label>
    <input type="text" id="track_album" name="track_album">
    <input type="submit" name="search_album" value="Search Album">
    <label for="track_year">Year</label>
    <input type="text" id="track_year" name="track_year">
    <input type="submit" name="search_year" value="Search Year">
    <label for="track_pick">Picked By</label>
    <input type="text" id="track_pick" name="track_pick">
    <input type="submit" name="search_pick" value="Search Pick">
</form>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

As you can see - very lengthy. I have a basic understanding of how to get dropdowns - although it seems like there's more than 1 way, I was trying this:

<form method="post">
    <select name="colunm">
        <option value="track_eps">Episode #</option>
        <option value="track_artist">Artist</option>
        <option value="track_album">Album</option>
        <option value="track_year">Year</option>
        <option value="track_pick">Picked by</option>
    </select>
    <input type="text" id="filter" name="filter">
    <input type="submit" name="Search" value="Search">
</form>

But the thing I'm having trouble with is taking the if statements from the beginning and making it more streamlined so that I only need 1 which will insert the value for the option selection and the value of the text box into the SQL statement WHERE [option value] = [text value]";

Does anyone have any suggestions? I'm likely overlooking something basic.

Just insert the value passed from your select field dynamically as column name into your WHERE clause then. That you’ll have to do using string concatenation, you can not use placeholders for column names, only for data. To avoid someone breaking your SQL statement or filtering by columns they are not supposed to, it might make sense to keep the allowed column names in an array, so that you can check against that. — misorude
– misorude, Commented Feb 19, 2020 at 14:42
And the placeholder name does not have to match the column name in any way - so instead of :track_eps, :track_artist etc., just using something generic, like :value, and then bind your text field content to that. — misorude
– misorude, Commented Feb 19, 2020 at 14:42
Thanks, this helps a little, but I think I've made this more complicated by using the same (or similar) labels all over the place... I'm going to have to test some things out. I tried doing what you suggested, but the code doesn't work and gives me an error: SELECT * FROM track_list WHERE :value = :filter SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens — JKSchneider
– JKSchneider, Commented Feb 19, 2020 at 15:37
They explicitly said "you can not use placeholders for column names" — miken32
– miken32, Commented Feb 19, 2020 at 20:57

miken32 · Accepted Answer · 2020-02-19 21:50:09Z

In your first set of if statements, the only thing changing is the query. There is no need to repeat everything else. In your second set of if statements, there is literally a single word changed in the dozens of lines of code. This is wasteful.

So, as you suspected, this can be done much more efficiently.

<?php
require_once "../config.php";
require_once "../common.php";

// if it doesn't already, $options should look like this:
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

// these are safe columns to search
$columns = ["track_eps", "track_artist", "track_album", "track_year", "track_pick"];
try  {
    // fall back to a safe value if needed
    $column = in_array($_POST["search_column"], $columns) ? $_POST["search_column"] : $columns[0];
    // if it doesn't already, $dsn should include charset=utf8mb4
    $connection = new PDO($dsn, $username, $password, $options);
    $sql = "SELECT * FROM track_list WHERE $column = ?";
    $statement = $connection->prepare($sql);
    // no need for binding, just pass parameters to execute
    $statement->execute([$_POST["search_text"]]);
    $result = $statement->fetchAll();

} catch (\Exception $e) {
    // don't show errors to the user, just pretend you got no results
    $result = [];
    // if you have a global exception handler, let it take over
    throw $e;
}
?>

<?php if(count($result) === 0): ?>
    <div class="alert">No results found for <?= escape($_POST["search_text"]) ?>.</div>
<?php else: ?>
    <h2>Results</h2>

    <table>
        <thead>
            <tr>
                <th>Title</th>
                <th>Artist</th>
                <th>Album</th>
                <th>Year</th>
                <th>Episode</th>
                <th>Picked By</th>
            </tr>
        </thead>
        <tbody>
    <?php foreach ($result as $row): ?>
            <tr>
                <td><?= escape($row["track_name"]) ?></td>
                <td><?= escape($row["track_artist"]) ?></td>
                <td><?= escape($row["track_album"]) ?></td>
                <td><?= escape($row["track_year"]) ?></td>
                <td><?= escape($row["track_eps"]) ?></td>
                <td><?= escape($row["track_pick"]) ?></td>
            </tr>
    <?php endforeach; ?>
        </tbody>
    </table>
<?php endif; ?>

<form method="post">
    <select name="search_column">
        <option value="track_eps">Episode #</option>
        <option value="track_artist">Artist</option>
        <option value="track_album">Album</option>
        <option value="track_year">Year</option>
        <option value="track_pick">Picked by</option>
    </select>
    <input type="text" id="filter" name="search_text">
    <button type="submit">Search</button>
</form>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

A couple of notes:

When mixing HTML and PHP, if you aren't using a proper templating system, it's neater to use alternative syntax for control structures and short echo tags. This is somewhat a matter of opinion, but once you've tried figuring out something like <?php }}} ?> 100 lines down the page, you'll appreciate endif and endforeach a lot.

I assume your escape() function is doing nothing more than htmlspecialchars(), nothing more is needed. So if you want to save typing, why not call it e() instead?

It's a 20 year old argument, but you should not be using semantic elements like <blockquote> for presentational purposes. Make it a <div>, give it a class, and style it.

Awesome, thanks for the tips! Much of this was cobbled together from bits of learning, so I'm not surprised there are issues other than what I was asking about. I'll try this out tonight when I get in.

Collectives™ on Stack Overflow

Adding value from HTML select into MySQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related