过滤功能仅使用一个参数

Question

So I have been working on a program that grabs info from a MySql database and then takes it and puts it into a table and it also has the ability to filter it, whenever I put in a single parameter, it works fine, however it does not work with two or more parameter Here's what I have, This part creates the MySQL query statement

<?php   

                        require 'databaseconnect.php';
                        $filterstmt = ("SELECT * FROM Inventory");
                        if (!empty($_POST['ID'])):
                            $filterstmt .= (" WHERE ID = :id");

                        endif;
                        if (!empty($_POST['ItemCode'])):
                            $filterstmt .= (" WHERE Item = :code");

                        endif;
                        if (!empty($_POST['Type'])):
                            $filterstmt .= (" WHERE Type = :type");

                        endif;
                        if (!empty($_POST['Condition'])):
                            $filterstmt .= (" WHERE PartCondition = :condition");

                        endif;
                        if (!empty($_POST['Location'])):
                            $filterstmt .= (" WHERE Location = :loc");

                        endif;

                        $preparedfilterstmt = $conn->prepare($filterstmt);
                        if (!empty($_POST['ID'])):
                        $preparedfilterstmt->bindParam(':id', $_POST['ID']);
                        endif;
                        if (!empty($_POST['ItemCode'])):
                        $preparedfilterstmt->bindParam(':code', $_POST['ItemCode']);
                        endif;
                        if (!empty($_POST['Type'])):
                        $preparedfilterstmt->bindParam(':type', $_POST['Type']);
                        endif;
                        if (!empty($_POST['Condition'])):
                        $preparedfilterstmt->bindParam(':condition', $_POST['Condition']);
                        endif;
                        if (!empty($_POST['Location'])):
                        $preparedfilterstmt->bindParam(':loc', $_POST['Location']);
                        endif;

And then this part executes the prepared statement and creates the table:

                        $preparedfilterstmt->execute();
                        $fltrtest = $preparedfilterstmt->rowCount();
                        if($fltrtest > 0):
                            echo ("<h3 class = 'Title'>Search Results: </h3>");

                    echo ("<table class = 'hubTable'> <tr class = 'tableheader'> <td class = 'hubCell'>ID</td> <td class = 'hubCell'>Item</td><td class = 'hubCell'>Type</td> <td class = 'hubCell'>Condition</td> <td class = 'hubCell'>Location</td> </tr> ");

                    while ($result = $preparedfilterstmt->fetch(PDO::FETCH_ASSOC)){
                    echo("<tr>"."<td class = 'hubCell'><a href = 'editinventroy.php?id=".$result['ID']."'>".$result['ID']."</a> </td>
                        <td class = 'hubCell'>".$result['Item']." </td>
                        <td class = 'hubCell'>".$result['Type']." </td>
                        <td class = 'hubCell'>".$result['PartCondition']." </td>
                        <td class = 'hubCell'>".$result['Location']." </td>

                         </tr>" );
                    }    
                    echo ("</table>");
                        else:
                            echo("<div class='alert alert-warning' role='alert'><b>Hmm...</b> Nothing seems to be under those parameters</div>");
                        endif;

I tried using a try-catch instead of the if statements to bind parameters, however that didn't work. I don't know what exactly is wrong here. Thanks!

Answer 1

You are not binding param properly. Query should always have param wit and/or. So you should use

$filterstmt = "SELECT * FROM Inventory WHERE";
$condition = "";
if (!empty($_POST['ID'])):
    $condition .= (" ID = :id");
endif;
if (!empty($_POST['ItemCode'])):
    if (empty($condition)) {
        $condition .= (" Item = :code");
    } else {
        $condition .= (" AND Item = :code"); //use AND/Or according to your query
    }

endif;
//remaining code

Then

$filterstmt = $filterstmt . $condition;