Display items with id from an array-CodePudding

So i created a column in my database that accepts JSON array, inside these array are item id's , i want to create a loop on those ids and insert it in an sql select statements to display all items that has the ids in the array, but im clueless on how to do it.

$sql = "SELECT bookmarks FROM `accounts` WHERE firstname='".$firstname."' && lastname='".$lastname."' ";

$sth = mysqli_query($conn, $sql);
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}

foreach($rows as $key ){ ?????
$bookmark = "SELECT * FROM books id='".$key."'";
$bookselect = mysqli_query($conn, $bookmark);
while($row = mysqli_fetch_assoc($bookselect)) { 


}
}????

CodePudding user response：

As you are storing array in Database, You need to convert that into array and ids first:

Check below code for example:

     $sql = "SELECT bookmarks FROM `accounts` WHERE firstname='".$firstname."' && lastname='".$lastname."' ";

    $sth = mysqli_query($conn, $sql);
    $rows = array();
    while($r = mysqli_fetch_assoc($sth)) {
        $rd = json_decode($r['bookmarks'], true);
        foreach($rd as $ids){
            $rows[] = $ids;
        }
    }

    $bookmark = "SELECT * FROM books WHERE id IN('".implode("','",$rows)."'";
    $bookselect = mysqli_query($conn, $bookmark);
    while($row = mysqli_fetch_assoc($bookselect)) { 
        print_r($row);
    
    
    }