使用前端搜索条件查询数据库

Question

I am creating a search form to allow a client to search the database for users by entering a first name, last name, birthdate or some combination thereof. The problem I am having is that I am not sure how to handle creating the where clause of the query when any of the fields are left blank much less how to bind paramaters that may not be present. Here is my search box.

<form action="manageUsers.php"method="POST">
        <h3>Search Users</h3>
        <label for="lastName">Last Name:</label>
        <input type="text" name="lastName"><br>

        <label for="firstName">First Name:</label>
        <input type="text" name="firstName"><br>

        <label for="birthdate">Birthdate:</label>
        <input type="text" name="birthdate"><br>

        <input type="submit" value="Search Users">
</form>

The only thing that I can think of is using some if statements to dynamically create the query based on whether a field is blank or not. I'm sure someone has an easy solution I don't know about or haven't thought of. Thanks

Answer 1

The way I would approach this is to make sure your input names match the columns in the MySQL database. It just makes the mapping that much easier. Then you can do the following:

<?
    if(count($_POST)>0){
        // remove any key that has no value
        $data = array_filter($_POST);

        // define an array to hold the pieces of the where clause
        $where = array();

        // loop each of the variables and build the query
        foreach($data as $key => $value){
           // make things safe
           $key = mysql_real_escape_string($key);
           $value = mysql_real_escape_string($value);

           // push values to array
           array_push($where, "$key='$value'");             
        }

        // create teh select query by imploding the array of pieces
        $query = "SELECT * FROM tablename WHERE ".implode(" AND ", $where);

        // just to show sample output
        echo $query;
    }
?>
<form action=""method="POST">
        <h3>Search Users</h3>
        <label for="lastName">Last Name:</label>
        <input type="text" name="lastName"><br>

        <label for="firstName">First Name:</label>
        <input type="text" name="firstName"><br>

        <label for="birthdate">Birthdate:</label>
        <input type="text" name="birthdate"><br>

        <input type="submit" value="Search Users">
</form>

Basically it makes sure you are posting, then does an array filter to remove any keys that have no value (that way you arent querying birthday=""). Then loop through the remaining keys and build that piece of the query. After the loop it implodes the array and joins it by AND and throws it into a string with the rest of your select query.

Outputs something like SELECT * FROM tablename WHERE lastName='efef' AND firstName='adsf'