如何在PHP中更改SQL查询与用户选择的下拉菜单相关

问题描述：

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?

</div>

答

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

如何在PHP中更改SQL查询与用户选择的下拉菜单相关

相关推荐