How to change SQL query in PHP in relation to what drop down menus the user selects

Question

I am currently doing a project in school which involves using a form to query a database. This form has multiple drop down menus and I am unsure on how to query the database if the user does not fill out all of the drop down menus. For example if the user only wants to search for a certain job type and does not specify the industry.

INDEX.HTML

<html>
    <head>
    </head>
    <body>
        <form action="test.php" method="post">
            <select name="varjobtype">
                <option value="nullg" disabled selected hidden>Job Type</option>
                <option value="Part Time">Part Time</option>
                <option value="Full Time">Full Time</option> 
                <option value="Contract">Contract</option>
                <option value="Temporary">Temporary</option>
            </select>
            <select name="varindustry">
                <option value="null" disabled selected hidden>Industry</option>
                <option value="Accommodation and Food Services">Accommodation and Food Services</option>
                <option value="Retail">Retail</option>
            </select>
        </form>
    </body>
</html>

TEST.PHP

<html>
    <head>
    </head>
    <body>
        <?php 
        $jobtype = $_POST['varjobtype'];
        $industry = $_POST['varindustry'];

        $sql = "SELECT `Job ID`, Name, Employer FROM JobListings WHERE `Job Type`  = '$jobtype' AND `Industry` = '$industry' ";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
        ?>
                <div id="<? echo $row['Job ID']; ?>" class="box">
                <?
             echo "Job ID: " . $row["Job ID"]. "<br>";     
                echo "Name: " . $row["Name"]. "<br>"; 
                echo "Employer: " . $row["Employer"]. "<br>";
             echo "</div>";
            }
        ?>            
        <?
        } else {
            echo "0 results";
        }
        $conn->close();
    ?>
    </body>
</html>

As of now the php outputs no results.How would you make it so even if the user selects one of the drop down menus then the SQL statement will still display the jobs. Is it possible to make it so that it displays all of the jobs if the user does not interact with any of the drop down menus?

You could try to replace the default selected field's value to '%'. Then in your query you should replace the '=' in the WHERE clause to 'LIKE'. The default value will act as a wildcard then. http://stackoverflow.com/a/804405/5884874 — Robbe, Dec 25 '16 at 22:33
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Dec 26 '16 at 02:19

Sofiane Achouba · Accepted Answer · 2016-12-25T23:52:34.190

1

try this:

$jobtype = isset($_POST['varjobtype']) ? $_POST['varjobtype'] : '';
$industry = isset($_POST['varindustry']) ? $_POST['varindustry'] :'';

$sql = "SELECT `Job ID`, Name, Employer FROM JobListings";

$where = array();

if ($jobtype) $where[] = "`Job Type`  = '".$jobtype."'";

if ($industry ) $where[] = "`Industry` = '".$industry."'";

if (!empty($where)) {
     $sql .= " where " . implode (" and ",$where);
}

$result = $conn->query($sql);
...

edited Dec 25 '16 at 23:52

answered Dec 25 '16 at 22:27

Sofiane Achouba

584
2
6

1

Tip: In PHP 7 the top two lines of code can be cleaned up with the [null coalescence operator](http://php.net/manual/en/migration70.new-features.php#migration70.new-features.null-coalesce-op). – Robbe Dec 25 '16 at 22:36
This error " Parse error: syntax error, unexpected ')' " appears for line 12? – matthew6543 Dec 25 '16 at 22:54
1

@matthew6543 edideted: if (!empty($where)) { – Sofiane Achouba Dec 25 '16 at 23:52
1

You can't just `implode` on raw variables and inject those into the query. This creates a massive [SQL injection hole](http://bobby-tables.com/). – tadman Dec 26 '16 at 02:20
1

the question was not about sql injection, implode or what he wrote ($sql = "SELECT `Job ID`, Name, Employer FROM JobListings WHERE `Job Type` = '$jobtype' AND `Industry` = '$industry' ";) is same thing can not prevent sql injection. There is other threads which talk about this for example [this thread](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Sofiane Achouba Dec 26 '16 at 10:14

How to change SQL query in PHP in relation to what drop down menus the user selects

1 Answers1