php how to partial search / input and dropdown menu search?

I am trying to make this search / partial search and drop down menu search separately. Like for example, when i use the dropdown menu search, the input search bar would be empty, and just display results from the drop down menu search bar. My code works fine when i just do a partial search, but whenever i use the dropdown menu search, and if the input bar is empty, it just displays all the results from the database. Is there a workaround this? thank you :) i have tried messing around with the % sign but it still does not work the way i want to.


if(isset($_POST['submit'])){

    $search = $db->real_escape_string($_POST['search']);
    $category = $_POST['category'];

    $resultSet = $db->query("SELECT * FROM books WHERE booktitle LIKE '%$search%' OR category = '$category'");


    echo "<table border='1'>";
    echo "<th>Author</th><th>Book Title</th><th>Reserved</th>";

    if($resultSet->num_rows > 0){
        while($rows = $resultSet->fetch_assoc()){
            $au = $rows['author'];
            $bt = $rows['booktitle'];
            $rev = $rows['reserved'];
            echo"<tr><td>$au</td><td>$bt</td><td>$rev</td></tr>\n";
        }
        echo"</table>";
    }

Solution

Your problem here is that if the search is blank, the WHERE part will come out as "WHERE booktitle LIKE '%%'" which effectively every row will match.

You could tweak it like so:

if(isset($_POST['submit'])){

    $search = $db->real_escape_string($_POST['search']);
    if ($search != "") {
        $search = "%" . $search . "%";
    }
    $category = $_POST['category'];

    $resultSet = $db->query("SELECT * FROM books WHERE booktitle LIKE '$search' OR category = '$category'");


    echo "<table border='1'>";
    echo "<th>Author</th><th>Book Title</th><th>Reserved</th>";

    if($resultSet->num_rows > 0){
        while($rows = $resultSet->fetch_assoc()){
            $au = $rows['author'];
            $bt = $rows['booktitle'];
            $rev = $rows['reserved'];
            echo"<tr><td>$au</td><td>$bt</td><td>$rev</td></tr>\n";
        }
        echo"</table>";
    }
}

This basically makes the LIKE statement include the % operators only when necessary. It isn't a perfect solution but it should get the job done.