优化PHP与SQL数据库搜索：处理空格与提升安全性

$query = "SELECT * FROM `master` WHERE CONCAT_WS(`id`, `office`, `firstName`, `lastName`, `type`, `status`, `deadline`, `contactPref`, `email`, `phoneNumber`, `taxPro`) LIKE '%".$valueToSearch."%'";

<?php

// function to connect and execute the query
function filterTable($query)
{
    // 请替换为您的数据库连接信息
    $connect = mysqli_connect("localhost", "username", "password", "database_name");
    if (!$connect) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $filter_Result = mysqli_query($connect, $query);
    if (!$filter_Result) {
        // 错误处理
        echo "Error: " . mysqli_error($connect);
        return false;
    }
    return $filter_Result;
}

$search_result = null;

if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];

    // 1. 拆分关键词
    $searchTerms = explode(' ', $valueToSearch);

    // 2. 构建动态SQL查询的WHERE子句部分
    $whereClauses = [];
    $searchableColumns = ['id', 'office', 'firstName', 'lastName', 'type', 'status', 'deadline', 'contactPref', 'email', 'phoneNumber', 'taxPro'];

    foreach ($searchTerms as $term) {
        $term = trim($term); // 清除词语两端的空白
        if (!empty($term)) {
            $likeConditionsForTerm = [];
            foreach ($searchableColumns as $column) {
                // 注意：这里仍然是字符串拼接，存在SQL注入风险，下一步将解决
                $likeConditionsForTerm[] = "`" . $column . "` LIKE '%" . $term . "%'";
            }
            // 每个词语在任一可搜索列中匹配即可
            $whereClauses[] = "(" . implode(" OR ", $likeConditionsForTerm) . ")";
        }
    }

    $query = "SELECT * FROM `master`";
    if (!empty($whereClauses)) {
        // 所有词语的条件通过 AND 连接，表示所有词语都必须匹配
        // 如果希望任一词语匹配即可，这里使用 OR
        $query .= " WHERE " . implode(" AND ", $whereClauses); 
        // 示例：如果希望任一词语在任一列匹配，可以这样组织：
        // $query .= " WHERE " . implode(" OR ", $whereClauses); 
    }

    $search_result = filterTable($query);

} else {
    $query = "SELECT * FROM `master`";
    $search_result = filterTable($query);
}

?>

<!-- HTML 部分与原代码相同，此处省略 -->

<?php

// function to connect and execute the query safely
function filterTableSafely($searchTerms = [])
{
    // 请替换为您的数据库连接信息
    $connect = mysqli_connect("localhost", "username", "password", "database_name");
    if (!$connect) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $searchableColumns = ['id', 'office', 'firstName', 'lastName', 'type', 'status', 'deadline', 'contactPref', 'email', 'phoneNumber', 'taxPro'];

    $query = "SELECT * FROM `master`";
    $types = ""; // 用于mysqli_stmt_bind_param的参数类型字符串
    $params = []; // 用于mysqli_stmt_bind_param的参数数组
    $whereClauses = [];

    if (!empty($searchTerms)) {
        foreach ($searchTerms as $term) {
            $term = trim($term);
            if (!empty($term)) {
                $likeConditionsForTerm = [];
                foreach ($searchableColumns as $column) {
                    $likeConditionsForTerm[] = "`" . $column . "` LIKE ?";
                    $params[] = '%' . $term . '%'; // 将 '%' 与 term 拼接后作为参数
                    $types .= 's'; // 's' 表示字符串类型
                }
                $whereClauses[] = "(" . implode(" OR ", $likeConditionsForTerm) . ")";
            }
        }
    }

    if (!empty($whereClauses)) {
        // 所有词语的条件通过 AND 连接，表示所有词语都必须匹配
        $query .= " WHERE " . implode(" AND ", $whereClauses);
    }

    // 准备语句
    $stmt = mysqli_prepare($connect, $query);
    if (!$stmt) {
        echo "Error preparing statement: " . mysqli_error($connect);
        mysqli_close($connect);
        return false;
    }

    // 绑定参数
    // mysqli_stmt_bind_param 需要引用传递，所以需要动态创建参数数组
    if (!empty($params)) {
        // 使用call_user_func_array来处理动态数量的参数绑定
        $bind_names[] = $types;
        for ($i = 0; $i < count($params); $i++) {
            $bind_name = 'bind' . $i;
            $$bind_name = $params[$i]; // 创建变量并赋值
            $bind_names[] = &$$bind_name; // 将变量的引用添加到绑定数组
        }
        call_user_func_array([$stmt, 'bind_param'], $bind_names);
    }

    // 执行语句
    mysqli_stmt_execute($stmt);

    // 获取结果
    $result = mysqli_stmt_get_result($stmt);

    // 关闭语句
    mysqli_stmt_close($stmt);
    mysqli_close($connect);

    return $result;
}

$search_result = null;

if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];
    $searchTerms = explode(' ', $valueToSearch);
    $search_result = filterTableSafely($searchTerms);
} else {
    // 默认显示所有数据
    $search_result = filterTableSafely([]);
}

?>

<!DOCTYPE html>
<html>
    <head>
        <title>PHP HTML TABLE DATA SEARCH</title>
        <style>
            table,tr,th,td
            {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>

        <form action="Untitled-1.php" method="post">
            <input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
            <input type="submit" name="search" value="Filter"><br><br>

            <table>
                <tr>
                    <th>ID</th>
                    <th>Office</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Type</th>
                    <th>Status</th>
                    <th>Deadline</th>
                    <th>Contact Preference</th>
                    <th>Email</th>
                    <th>Phone Number</th>
                    <th>Tax Pro</th>
                </tr>

                <?php 
                if ($search_result) { // 检查查询是否成功
                    while($row = mysqli_fetch_array($search_result)):?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['id']);?></td>
                        <td><?php echo htmlspecialchars($row['office']);?></td>
                        <td><?php echo htmlspecialchars($row['firstName']);?></td>
                        <td><?php echo htmlspecialchars($row['lastName']);?></td>
                        <td><?php echo htmlspecialchars($row['type']);?></td>
                        <td><?php echo htmlspecialchars($row['status']);?></td>
                        <td><?php echo htmlspecialchars($row['deadline']);?></td>
                        <td><?php echo htmlspecialchars($row['contactPref']);?></td>
                        <td><?php echo htmlspecialchars($row['email']);?></td>
                        <td><?php echo htmlspecialchars($row['phoneNumber']);?></td>
                        <td><?php echo htmlspecialchars($row['taxPro']);?></td>
                    </tr>
                    <?php endwhile;
                } else {
                    echo "<tr><td colspan='11'>No results found or an error occurred.</td></tr>";
                }
                ?>
            </table>
        </form>

    </body>
</html>