从数据库JSON数组中高效查询指定ID的项目

// 假设 $conn 是你的数据库连接对象
$firstname = "John"; // 示例用户姓氏
$lastname = "Doe";   // 示例用户名字

$sql = "SELECT bookmarks FROM `accounts` WHERE firstname = ? AND lastname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $firstname, $lastname);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$bookmarkIds = [];
if ($row = mysqli_fetch_assoc($result)) {
    // 确保 'bookmarks' 字段存在且不为空
    if (isset($row['bookmarks']) && !empty($row['bookmarks'])) {
        // json_decode 第二个参数为 true，表示解码为关联数组
        // 如果JSON数组只包含数字ID，则会解码为索引数组
        $decodedJson = json_decode($row['bookmarks'], true);
        if (json_last_error() === JSON_ERROR_NONE && is_array($decodedJson)) {
            // 将所有ID收集到一个扁平数组中
            foreach ($decodedJson as $id) {
                // 确保ID是有效的数字
                if (is_numeric($id)) {
                    $bookmarkIds[] = (int)$id; // 转换为整数类型
                }
            }
        } else {
            // JSON解码失败或数据格式不正确
            error_log("Error decoding bookmarks JSON: " . json_last_error_msg());
        }
    }
}
mysqli_stmt_close($stmt);

$books = [];
if (!empty($bookmarkIds)) {
    // 构建IN子句的占位符字符串
    // 例如：?, ?, ?
    $placeholders = implode(',', array_fill(0, count($bookmarkIds), '?'));

    $sqlBooks = "SELECT * FROM `books` WHERE id IN (" . $placeholders . ")";
    $stmtBooks = mysqli_prepare($conn, $sqlBooks);

    // 动态绑定参数
    // 需要创建一个包含所有ID的数组，并为每个ID指定类型
    $types = str_repeat('i', count($bookmarkIds)); // 'i' 表示整数类型
    mysqli_stmt_bind_param($stmtBooks, $types, ...$bookmarkIds);

    mysqli_stmt_execute($stmtBooks);
    $resultBooks = mysqli_stmt_get_result($stmtBooks);

    while ($rowBook = mysqli_fetch_assoc($resultBooks)) {
        $books[] = $rowBook;
    }
    mysqli_stmt_close($stmtBooks);
}

// 打印查询到的图书信息
print_r($books);

<?php
// 假设 $conn 已经是一个有效的 mysqli 数据库连接
// $conn = new mysqli("localhost", "username", "password", "database");
// if ($conn->connect_error) {
//     die("Connection failed: " . $conn->connect_error);
// }

// 示例数据
$firstname = "John";
$lastname = "Doe";

// --- 步骤一：获取并解码JSON数据，提取ID ---
$bookmarkIds = [];
$sqlAccount = "SELECT bookmarks FROM `accounts` WHERE firstname = ? AND lastname = ?";
$stmtAccount = mysqli_prepare($conn, $sqlAccount);

if ($stmtAccount) {
    mysqli_stmt_bind_param($stmtAccount, "ss", $firstname, $lastname);
    mysqli_stmt_execute($stmtAccount);
    $resultAccount = mysqli_stmt_get_result($stmtAccount);

    if ($rowAccount = mysqli_fetch_assoc($resultAccount)) {
        if (isset($rowAccount['bookmarks']) && !empty($rowAccount['bookmarks'])) {
            $decodedJson = json_decode($rowAccount['bookmarks'], true);
            if (json_last_error() === JSON_ERROR_NONE && is_array($decodedJson)) {
                foreach ($decodedJson as $id) {
                    if (is_numeric($id)) {
                        $bookmarkIds[] = (int)$id;
                    }
                }
            } else {
                error_log("Error decoding bookmarks JSON for user " . $firstname . " " . $lastname . ": " . json_last_error_msg());
            }
        }
    }
    mysqli_stmt_close($stmtAccount);
} else {
    error_log("Failed to prepare statement for accounts: " . mysqli_error($conn));
}


// --- 步骤二：构建并执行IN子句查询 ---
$books = [];
if (!empty($bookmarkIds)) {
    // 构建IN子句的占位符字符串，例如 ?, ?, ?
    $placeholders = implode(',', array_fill(0, count($bookmarkIds), '?'));

    $sqlBooks = "SELECT * FROM `books` WHERE id IN (" . $placeholders . ")";
    $stmtBooks = mysqli_prepare($conn, $sqlBooks);

    if ($stmtBooks) {
        // 动态绑定参数类型字符串，例如 'iii' 表示三个整数
        $types = str_repeat('i', count($bookmarkIds));
        // 使用splat操作符将数组元素作为独立的参数传递给bind_param
        mysqli_stmt_bind_param($stmtBooks, $types, ...$bookmarkIds);

        mysqli_stmt_execute($stmtBooks);
        $resultBooks = mysqli_stmt_get_result($stmtBooks);

        while ($rowBook = mysqli_fetch_assoc($resultBooks)) {
            $books[] = $rowBook;
        }
        mysqli_stmt_close($stmtBooks);
    } else {
        error_log("Failed to prepare statement for books: " . mysqli_error($conn));
    }
}

// 输出查询结果
if (!empty($books)) {
    echo "<h2>收藏的图书列表:</h2>";
    foreach ($books as $book) {
        echo "<p>ID: " . $book['id'] . ", 标题: " . $book['title'] . ", 作者: " . $book['author'] . "</p>";
    }
} else {
    echo "<p>未找到收藏的图书或用户未收藏任何图书。</p>";
}

// 关闭数据库连接
// $conn->close(); 
?>