MySql query for multiple selected options in HTML form

Question

I have scoured for similar posts and information regarding my issue but can't find any that have been of great help. I have a form on a webpage that consists of 5 dropdown menus, each with different options, the user will select an option from each drop down menu and then hit the submit button. At this point, I want to run a query on the database to look for matching criteria to all the options that are "selected". Right now I don't need to match all the fields, because I'm just testing, so I just want to check say one field. The form I am using is like this:

<div class="section-two">
    <div class="row bound-box">
        <div class="col-lg-8 col-md-8 col-sm-8 col-lg-offset-2 col-md-offset-2 col-sm-offset-2 main clearfix">
            <form id="nl-form" class="nl-form" action="searchpubs.php" method="post">
            I feel to visit 
            <select id="category">
                <option value="anywhere" selected>anywhere</option>
                <option value="pub">a pub</option>
                <option value="nightclub">a nightclub</option>
                <option value="brewery">a brewery</option>
                <option value="stripclub">a gentleman's club</option>
            </select>
            <br/>that
            <select id="foodDrink">
                <option value="food" selected>serves food</option>
                <option value="drinks">serves drinks only</option>
            </select>
            and has 
            <br/> 
            <select id="extra">
                <option value="poolTable" selected>a pool table</option>
                <option value="danceFloor">a dance floor</option>
                <option value="tv">tv's</option>
                <option value="everything">everything</option>
                <option value="anything">anything</option>
            </select>
            . It's within<br>
            <select id="distance">
                <option value="anyDistance" selected>any distance</option>
                 <option value="5">5 km</option>
                 <option value="10">10 km</option>
                 <option value="15">15 km</option>
                 <option value="20">20 km</option>
            </select>
            from me<br> and is open
            <select id="hours">
                <option value="late" selected>past 12 a.m.</option>
                 <option value="allDay">all day</option>
                 <option value="weekdays">on weekdays</option>
                 <option value="sundays">on sundays</option>
                 <option value="anytime">anytime</option>
            </select>
            <div class="nl-submit-wrap">
                <button class="nl-submit" type="submit">Find Your Pub</button>
            </div>
            <div class="nl-overlay"></div>
            </form>
        </div><!--column-->
    </div><!-- row -->
</div><!--section-two-->

So for instance, if the user has selected the option "serves food" under the select id "foodDrink", I want to run a query on our database to search the column labelled "food" for any row that contains "yes" in that column. Then display all of the matching criteria in the browser. Right now it doesn't need to be formatted etc, I just want to see it in the browser to ensure the query works. Can anybody help me with this, and possibly understand how to add additional queries when ready.

I'm currently connecting to my DB like this:

<?php

//DATABASE CONNECTION INFO BELOW:
$hostname="hostname";
$database="dbname";
$username="myusername";
$password="mypassword";

$link = mysql_connect($hostname, $username, $password);
if (!$link) {
    die('Connection failed: ' . mysql_error());
}

$db_selected = mysql_select_db($database, $link);
if (!$db_selected) {
    die ('Can\'t select database: ' . mysql_error());
}

mysql_close($link);

?>

The mysql_* functions are deprecated. Use mysqli or PDO, along with prepared statements. Remember [bobby tables](http://bobby-tables.com). — Matthew Johnson, Nov 03 '15 at 20:43

Drew · Accepted Answer · 2015-11-03T21:28:03.983

The following represents a stub. I am not going to write end-to-end queries for you. Note that your form gave the PHP handler nothing in $_POST due to the lack of name='xxx' in those 5 or so lines for the drop downs. All they had were drop down id's.

Also, you need to be using something other than mysql_* functions. Those old deprecated functions are easily susceptible to sql injection attacks from much of the source code littering the internet. And they do not separate the query from the parameters. There are no prepared statement bindings in them no exception handling (that I have ever seen used, someone correct me). So those are a few reasons and I will stop there.

So I moved it toward mysqli. You could swing for PDO too if you want. And you need to do bindings with prepared statements to sanitize the user input so as not to act on it directly without being sanitized. The bindings do that for you.

But the following will get you your POST back in the output. Proceed accordingly.

Form

<html>
<body>
<div class="section-two">
    <div class="row bound-box">
        <div class="col-lg-8 col-md-8 col-sm-8 col-lg-offset-2 col-md-offset-2 col-sm-offset-2 main clearfix">
            <form id="nl-form" class="nl-form" action="searchpubs.php" method="post">
            I feel to visit 
            <select id="category" name="category">
                <option value="anywhere" selected>anywhere</option>
                <option value="pub">a pub</option>
                <option value="nightclub">a nightclub</option>
                <option value="brewery">a brewery</option>
                <option value="stripclub">a gentleman's club</option>
            </select>
            <br/>that
            <select id="foodDrink" name="foodDrink">
                <option value="food" selected>serves food</option>
                <option value="drinks">serves drinks only</option>
            </select>
            and has 
            <br/> 
            <select id="extra" name="extra">
                <option value="poolTable" selected>a pool table</option>
                <option value="danceFloor">a dance floor</option>
                <option value="tv">tv's</option>
                <option value="everything">everything</option>
                <option value="anything">anything</option>
            </select>
            . It's within<br>
            <select id="distance" name="distance">
                <option value="anyDistance" selected>any distance</option>
                 <option value="5">5 km</option>
                 <option value="10">10 km</option>
                 <option value="15">15 km</option>
                 <option value="20">20 km</option>
            </select>
            from me<br> and is open
            <select id="hours" name="hours">
                <option value="late" selected>past 12 a.m.</option>
                 <option value="allDay">all day</option>
                 <option value="weekdays">on weekdays</option>
                 <option value="sundays">on sundays</option>
                 <option value="anytime">anytime</option>
            </select>
            <div class="nl-submit-wrap">
                <button class="nl-submit" type="submit">Find Your Pub</button>
            </div>
            <div class="nl-overlay"></div>
            </form>
        </div><!--column-->
    </div><!-- row -->
</div><!--section-two-->
</body>
</html>

searchpubs.php

<?php
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    error_reporting(E_ALL);
    ini_set("display_errors", 1);
    // error reporting is now turned on

    echo "start<br/>";
    try {
        $mysqli= new mysqli('hostname', 'username', 'password', 'dbname');  // change me please
        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '
                . $mysqli->connect_error);
        }
        echo "I am connected and feel happy.<br/>";
?>
<table>
<?php 
    //http://stackoverflow.com/a/9332766/

    foreach ($_POST as $key => $value) {
        echo "<tr>";
        echo "<td>";
        echo $key;
        echo "</td>";
        echo "<td>";
        echo $value;
        echo "</td>";
        echo "</tr>";
    }


?>
</table>
<?php
        // make necessary mysqli queries by BINDING parameters with prepared statements

        // because even though they are from a drop down box, they can be faked

        // and sql injection can and will occur by some fool out there

        //$stmt->close(); 
        $mysqli->close();
    } catch (mysqli_sql_exception $e) { 
        throw $e; 
    } 
?>

Browser output (upon success)

start
I am connected and feel happy.
category    anywhere
foodDrink   drinks
extra   poolTable
distance    10
hours   late

Try/catch, errors

Here is what the output will look like with one of the errors possible:

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ' in C:\Apache24\htdocs\searchpubs.php:9 Stack trace: #0 C:\Apache24\htdocs\searchpubs.php(9): mysqli->mysqli('ibm.com', 'LordBenson', 'billybob', 'db666') #1 {main} thrown in C:\Apache24\htdocs\searchpubs.php on line 9

So turn on error reporting, try/catches. It has your back. Let it.

you're welcome, and read a few of [these](http://stackoverflow.com/search?tab=votes&q=mysql%20sql%20injection) when you get a change on the stack. It is a search on the stack for "mysql sql injection" ordered in desc order by vote — Drew, Nov 03 '15 at 21:23
I myself would go for PDO, but the main thing is get onto a different bandwagon than mysql_* stuff from the net because you are just starting out. And check out binding of parameters. Good luck — Drew, Nov 03 '15 at 21:31

MySql query for multiple selected options in HTML form

1 Answers1

Form

searchpubs.php

Browser output (upon success)

Try/catch, errors