优化PHP/MySQL页面加载：从同步下拉菜单到异步数据处理

<select data-live-search='true' required class='selectpicker form-control border' name='wa_ra_id' value="<?php echo isset($_POST['wa_ra_id']) ? htmlspecialchars($_POST['wa_ra_id'], ENT_QUOTES) : ""; ?>">
    <option selected="true" disabled="disabled" value="">Select rack...</option>
    <?php
        $database = new Database();
        $db = $database->getConnection();

        $query = "SELECT
                   ra_id,
                   ra_number,
                   ra_section,
                   ra_zone
                FROM racks
                ORDER BY ra_number";

        $stmt = $db->prepare($query);
        $stmt->execute();

        // 使用 fetchAll() 一次性获取所有结果
        $racks = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // 遍历内存中的数组生成选项
        foreach ($racks as $row) {
            // 不需要 extract()，直接访问 $row 数组元素
            echo "<option value='{$row['ra_id']}'>{$row['ra_number']}{$row['ra_section']}{$row['ra_zone']}</option>";
        }
    ?>
</select>

<input type="text" id="rack_search" placeholder="Search for rack...">
<select id="wa_ra_id_autocomplete" class="form-control border" name="wa_ra_id">
    <option value="">Select rack...</option>
</select>

$(document).ready(function() {
    $('#rack_search').on('input', function() {
        var searchTerm = $(this).val();
        if (searchTerm.length >= 2) { // 至少输入2个字符才开始搜索
            $.ajax({
                url: 'search_racks.php', // 后端处理搜索请求的PHP文件
                method: 'GET',
                data: { query: searchTerm },
                dataType: 'json',
                success: function(data) {
                    var options = '<option value="">Select rack...</option>';
                    $.each(data, function(index, rack) {
                        options += '<option value="' + rack.ra_id + '">' + rack.ra_number + rack.ra_section + rack.ra_zone + '</option>';
                    });
                    $('#wa_ra_id_autocomplete').html(options);
                }
            });
        } else {
            $('#wa_ra_id_autocomplete').html('<option value="">Select rack...</option>');
        }
    });
});

<?php
// search_racks.php
header('Content-Type: application/json');

$searchTerm = $_GET['query'] ?? '';

if (strlen($searchTerm) < 2) {
    echo json_encode([]);
    exit;
}

$database = new Database(); // 假设 Database 类已定义
$db = $database->getConnection();

$query = "SELECT ra_id, ra_number, ra_section, ra_zone
          FROM racks
          WHERE ra_number LIKE :searchTerm OR ra_section LIKE :searchTerm OR ra_zone LIKE :searchTerm
          ORDER BY ra_number
          LIMIT 20"; // 限制返回结果数量

$stmt = $db->prepare($query);
$stmt->bindValue(':searchTerm', '%' . $searchTerm . '%', PDO::PARAM_STR);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($results);
?>