数据库中JSON数组ID的高效查询与应用

// 步骤1：从 accounts 表获取包含 JSON 格式 bookmarks 的记录
$sql = "SELECT bookmarks FROM `accounts` WHERE firstname='".$firstname."' && lastname='".$lastname."' ";
$sth = mysqli_query($conn, $sql);

$all_bookmarks_json = [];
while($r = mysqli_fetch_assoc($sth)) {
    // 假设 $r['bookmarks'] 包含一个 JSON 字符串，例如 '["101","105","203"]'
    $all_bookmarks_json[] = $r['bookmarks'];
}

// 步骤2：解析 JSON 并收集所有 ID
$all_extracted_ids = [];
foreach($all_bookmarks_json as $bookmark_json) {
    $decoded_bookmarks = json_decode($bookmark_json, true);
    if (is_array($decoded_bookmarks)) {
        foreach($decoded_bookmarks as $id) {
            $all_extracted_ids[] = $id;
        }
    }
}

// 步骤3：为每个 ID 执行单独的数据库查询（低效操作）
foreach($all_extracted_ids as $id_to_query ){
    $bookmark_item_sql = "SELECT * FROM books WHERE id='".$id_to_query."'"; // 每次循环都执行一个新查询
    $bookselect = mysqli_query($conn, $bookmark_item_sql);
    while($row = mysqli_fetch_assoc($bookselect)) { 
        // 处理查询结果
        print_r($row);
    }
}

// 假设 $firstname 和 $lastname 已经通过安全方式获取，并已进行转义或使用预处理
// 为防止 SQL 注入，这里使用 mysqli_real_escape_string 进行示例转义
$escaped_firstname = mysqli_real_escape_string($conn, $firstname);
$escaped_lastname = mysqli_real_escape_string($conn, $lastname);

$sql = "SELECT bookmarks FROM `accounts` WHERE firstname='" . $escaped_firstname . "' AND lastname='" . $escaped_lastname . "' ";
$sth = mysqli_query($conn, $sql);

if (!$sth) {
    die("查询 accounts 表失败: " . mysqli_error($conn));
}

$all_extracted_ids = []; // 用于存储从所有JSON数组中提取的ID

while($r = mysqli_fetch_assoc($sth)) {
    // 确保 'bookmarks' 键存在且不是null
    if (isset($r['bookmarks']) && $r['bookmarks'] !== null) {
        // 将JSON字符串解码为PHP数组。true 参数表示将JSON对象解码为关联数组
        $decoded_bookmarks = json_decode($r['bookmarks'], true);

        // 检查解码是否成功且结果是一个数组
        if (is_array($decoded_bookmarks)) {
            // 遍历解码后的数组，将每个ID添加到总ID列表中
            foreach($decoded_bookmarks as $id){
                // 确保 ID 是有效的，例如非空
                if (!empty($id)) {
                    $all_extracted_ids[] = $id;
                }
            }
        } else {
            // 处理JSON解码失败的情况，例如记录日志或抛出异常
            error_log("JSON解码失败或结果不是数组: " . $r['bookmarks']);
        }
    }
}

$id_list_for_sql = "''"; // 默认值，如果 all_extracted_ids 为空，则生成一个不匹配任何记录的 IN 子句

if (!empty($all_extracted_ids)) {
    // 对每个 ID 进行转义，以防止 SQL 注入。
    // 如果 ID 是整数类型，则不需要单引号包裹，但仍建议转义以防万一。
    $escaped_ids = array_map(function($id) use ($conn) {
        return mysqli_real_escape_string($conn, $id);
    }, $all_extracted_ids);

    // 如果 ID 是字符串类型，需要用单引号包裹并用逗号连接
    $id_list_for_sql = "'" . implode("','", $escaped_ids) . "'";

    // 如果 ID 确定是整数类型，则不需要单引号
    // $id_list_for_sql = implode(",", $escaped_ids);
}