php - 如何在 php 中从表单中搜索多个输入?
问题描述
我有一个 PHP 程序,它允许用户根据输入或输入组合搜索 SQL 表。我可以进行单一搜索组合,但无法找出按任何标准进行搜索的方法。到目前为止我得到的结果很糟糕,因为我正在尝试按每个输入可能性进行搜索(但它不起作用)。这是我到目前为止得到的。
<?php
include_once("config.php");
if(isset($_POST['submit'])){
$name = mysqli_real_escape_string($mysqli, $_POST['name']);
$day = mysqli_real_escape_string($mysqli, $_POST['day']);
$month = mysqli_real_escape_string($mysqli, $_POST['month']);
$year = mysqli_real_escape_string($mysqli, $_POST['year']);
// 1 2 3 4
if( !empty($name) && !empty($day) && !empty($month) && !empty($year) ) {
$sql = mysqli_query($mysqli, "SELECT *
FROM transfer
WHERE name like '%$name%'
and day LIKE '%$day%'
AND month LIKE '%$month%'
AND year LIKE '%$year%'");
} else if (!empty($name) && !empty($day) && !empty($month) ) {
$sql = mysqli_query($mysqli, "SELECT *
FROM transfer
WHERE name like '%$name%'
and day LIKE '%$day%'
AND month LIKE '%$month%'");
} else if (!empty($day) && !empty($month) && !empty($year) ) {
$sql = mysqli_query($mysqli, "SELECT *
FROM transfer
WHERE day LIKE '%$day%'
AND month LIKE '%$month%'
AND year LIKE '%$year%'");
} else if (!empty($name && !empty($day) ) {
$sql = mysqli_query($mysqli, "SELECT * FROM transfer
WHERE name like '%$name%' and
day LIKE '%$day%'");
}
//1 3
else if (!empty($name) && !empty($month) )
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%' and month LIKE '%$month%'");
}
//1 4
else if (!empty($name) && !empty($year) )
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%' and year LIKE '%$year%'");
}
//2 3
else if (!empty($day) && !empty($month) )
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and month LIKE '%$month%'");
}
//2 3
else if (!empty($day) && !empty($month) )
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and month LIKE '%$month%'");
}
//2 4
else if (!empty($day) && !empty($year))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and year LIKE '%$year%'");
}
//3 4
else if (!empty($month) && !empty($year))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE month like '%$month%' and year LIKE '%$year%'");
}
//1
else if (!empty($name))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%'");
}
//2
else if (!empty($day))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%'");
}
//3
else if (!empty($month))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE month like '%$month%'");
}
//4
else if(!empty($year))
{
$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE year like '%$year%'");
}
else
{
echo "<p>you must insert an input</p>";
}
//while loop used to retrieve data from the SQL database
while ($res = mysqli_fetch_array($sql))
{
echo "<tr>";
echo "<td>".$res['name']."</td>";
echo "<td>".$res['confirmation']."</td>";
echo "<td>".$res['code']."</td>";
echo "<td>".$res['hora']." ".$res['horario']."</td>";
echo "<td>".$res['day']."/".$res['month']."/".$res['year']."</td>";
echo "<td>".$res['extra']."</td>";
echo "</tr>";
}
}
?>
</table>
解决方案
(注意:据说使用准备好的语句,这是正确的 - 但我不想给出复制和粘贴的答案,所以这里只是一个关于如何实现结果的示例 - 无论如何都要使用准备好的语句。它的工作原理相同,除了您使用占位符创建查询并提供非空变量)
您可以以更“动态”的方式创建查询。这有点棘手,如果需要连接,就会变得非常“具有挑战性”——但您真正想要的是最终得到一个包含所有约束的查询。
首先,您应该定义:您的搜索字段是“和”还是“或”字段?
如果它是“和”,那么实现起来很简单——像这样:
$query = "SELECT * FROM transfer";
$andParts = array();
if(!empty($name))
$andParts[] = "name = '$name'";
if(!empty($day))
$andParts[] = "day = $day";
if (!empty($month))
$andParts[] = "month = $month";
if (!empty($year))
$andParts[] = "year = $year";
if (!empty($andParts))
$query .= " WHERE ".implode(" AND " , $andParts);
$sql->Query($query);
如果还涉及“或”,则需要另一个数组$orParts
,首先将所有“或”加入其中,最后将该数组粘合到最后的“与”。
如果条件可以匹配“连接”表中的列,您需要跟踪它,以便您知道,从您需要“选择”的表中。
如果您对每个“搜索字段”有非常复杂的查询(即每个搜索字段结果都是多个连接的结果等...),您可以只查询每个搜索字段的 id,然后将结果相交并检索匹配所有条件的 id:
$result1 = $sql->Query("SELECT id FROM transfer left join .... ");
// array(1,2,3,5,7,10,15,19,27)
$result2 = $sql->Query("SELECT id FROM transfer right join .... ");
// array(2,3,10,15,19,27,43,123)
$result3 = $sql->Query("SELECT id FROM transfer inner join .... ");
// array(2,10,15,27,43,711)
$ids = array_intersect($result1, $result2, $result3);
// array(2,10,15,27)
$finalResult = $sql->Query("SELECT * FROM transfer WHERE id in (".implode(",", $ids).");");
推荐阅读
- snmpsharpnet - 如何使用 snmpsharpnet 更改发送端口
- docker - Docker:如何将大文件夹包含到容器中?
- ios - 如果 UITapGestureRecognizer 安装在顶级 UIWindow keyWindow 上,则无法获得非零位置
- android - 如何在后台(连续)运行颤振应用程序以跟踪位置
- c++ - 在 C++20 中使用 `std::bit_cast` 创建闭包(lambda)对象是否有效?
- javascript - 我想用子菜单制作一个侧边栏。我尝试了一下,但我遇到了一些错误
- javascript - 用于电子表格的 Google Apps 脚本 - 不断收到函数 () { [native code] } 错误
- python - 如何删除嵌入编辑消息 Discord.py
- javascript - 'TypeError: 无法读取属性 '1' of null'
- flutter - 如果我想使用一个依赖并且它不支持 null 安全,我应该怎么做?