php 在表单中搜索具有多个字段的 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9317836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search MySQL Database with Multiple Fields in a Form
提问by A.B.Developer
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> </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.
但在这种情况下，用户可能会将字段留空。
回答by Stelian Matei
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);
