在表单中搜索具有多个字段的MySQL数据库 [英] 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.

推荐答案

尝试一下:

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

这篇关于在表单中搜索具有多个字段的MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！