Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
345 views
in Technique[技术] by (71.8m points)

php - Search MySQL Database with Multiple Fields in a Form

I have created a form where the user can search the database, and the result depends on how the user fills out the form.
For example, say I have name, address, city, state, and zip field, and the user fills out name and city fields, the results reflect the input. When the form submits all records are displayed. for this I write this:

if(isset($_POST['submit'])) {
        $sql = mysql_query("SELECT * FROM table WHERE name LIKE '%" . $_POST['name'] . "%'
                   OR address LIKE '%" . $_POST['address'] . "%'
                   OR city LIKE '%" . $_POST['city'] . "%'
                   OR state LIKE '%" . $_POST['state'] . "%'
                   OR zip LIKE '%" . $_POST['zip'] . "%'");
    }


        <form method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
            <tr>
                <td>Name:</td>
                <td><input type="text" name="name" /></td>
            </tr>
            <tr>
                <td>Address:</td>
                <td><input type="text" name="address" /></td>
            </tr>
            <tr>
                <td>City:</td>
                <td><input type="text" name="city" /></td>
            </tr>
            <tr>
                <td>State:</td>
                <td><input type="text" name="state" /></td>
            </tr>
            <tr>
                <td>Zip:</td>
                <td><input type="text" name="zip" /></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td><input type="submit" name="submit" value="Search" /></td>
            </tr>
        </form>
    </table>

    <?php
        if(isset($_POST['submit'])) {
            while($row = mysql_fetch_array($sql)) {
                echo $row['name'] . "<br />";
            }
        }   
    ?>

But in this case a user may leave a field blank.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Try this:

if(isset($_POST['submit'])) {
    // define the list of fields
    $fields = array('name', 'address', 'city', 'state', 'zip');
    $conditions = array();

    // loop through the defined fields
    foreach($fields as $field){
        // if the field is set and not empty
        if(isset($_POST[$field]) && $_POST[$field] != '') {
            // create a new condition while escaping the value inputed by the user (SQL Injection)
            $conditions[] = "`$field` LIKE '%" . mysql_real_escape_string($_POST[$field]) . "%'";
        }
    }

    // builds the query
    $query = "SELECT * FROM TABLE ";
    // if there are conditions defined
    if(count($conditions) > 0) {
        // append the conditions
        $query .= "WHERE " . implode (' AND ', $conditions); // you can change to 'OR', but I suggest to apply the filters cumulative
    }

    $result = mysql_query($query);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...