PHP与MySQL：利用数组数据优化HTML Select选项生成教程

<?php
// 数据库连接信息
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'mydatabase';

// 建立数据库连接
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

// 检查连接
if (!$conn) {
    die("数据库连接失败: " . mysqli_connect_error());
}

// 假设 $_SESSION["adminusername"] 已经设置
// 从ADMIN表获取文件ID字符串
$sql_admin = "SELECT Files FROM ADMIN WHERE id='" . $_SESSION["adminusername"] . "'";
$result_admin = mysqli_query($conn, $sql_admin);

if ($result_admin && mysqli_num_rows($result_admin) > 0) {
    $isadmin = mysqli_fetch_array($result_admin);
    $arraydata = $isadmin["Files"]; // 例如："26,27,28,29"
    $array3 = explode(',', $arraydata); // 将字符串转换为数组：['26', '27', '28', '29']
} else {
    $array3 = []; // 如果没有找到数据，初始化为空数组
}

// 此时 $array3 包含了需要查询的文件ID
?>

// 错误示范：每个循环都创建新的 <select> 标签
foreach ($array3 as $singleID) {
    $sql_servers = "SELECT FileID, FileTitle FROM Servers WHERE FileType='CFG' AND FileID='" . $singleID . "'";
    $result_servers = mysqli_query($conn, $sql_servers);

    if (mysqli_num_rows($result_servers)) {
        $select = '<select class="smallInput" name="serverfile" tabindex="6">';
        while ($rs = mysqli_fetch_array($result_servers)) {
            $select .= '<option value="' . $rs['FileID'] . '">' . $rs['FileTitle'] . '</option>';
        }
        $select .= '</select>';
        echo $select; // 每次循环都会输出一个完整的 select 元素
    }
}

<?php
$select = '<select class="smallInput" name="serverfile" tabindex="6">';

foreach ($array3 as $singleID) {
    // 为每个ID执行一次查询
    $sql_servers = "SELECT FileID, FileTitle FROM Servers WHERE FileType='CFG' AND FileID='" . $singleID . "'";
    $result_servers = mysqli_query($conn, $sql_servers);

    while ($rs = mysqli_fetch_array($result_servers)) {
        $select .= '<option value="' . $rs['FileID'] . '">' . htmlspecialchars($rs['FileTitle']) . '</option>';
    }
}

$select .= '</select>';
echo $select;

// 关闭数据库连接（如果在此处不需要更多操作）
// mysqli_close($conn);
?>

<?php
// 确保数据库连接已建立
// $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); ... (同上文)

// 使用预处理语句结合 JOIN 和 FIND_IN_SET
// 注意：FIND_IN_SET(s.FileID, a.Files) 表示在 a.Files 字符串中查找 s.FileID
$stmt = mysqli_prepare($conn, "
    SELECT s.FileID, s.FileTitle
    FROM Servers AS s
    JOIN ADMIN AS a ON FIND_IN_SET(s.FileID, a.Files)
    WHERE s.FileType = 'CFG'
    AND a.id = ?"); // 使用占位符 '?' 来防止SQL注入

// 绑定参数：'s' 表示参数类型为字符串，$_SESSION["adminusername"] 是要绑定的值
mysqli_stmt_bind_param($stmt, "s", $_SESSION["adminusername"]);

// 执行预处理语句
mysqli_stmt_execute($stmt);

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

$select = '<select class="smallInput" name="serverfile" tabindex="6">';

// 遍历结果集，构建选项
while ($rs = mysqli_fetch_array($result)) {
    $select .= '<option value="' . $rs['FileID'] . '">' . htmlspecialchars($rs['FileTitle']) . '</option>';
}

$select .= '</select>';
echo $select;

// 关闭预处理语句和数据库连接
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>