CODEWITHSUNDEEP
phphtmlmysqlsql
matthew6543
matthew6543

Reputation: 33

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

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?

Upvotes: 3

Views: 691

Answers (1)

Sofiane Achouba
Sofiane Achouba

Reputation: 604

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);
...

Upvotes: 1

Related Questions