尝试编写查询以检索与索引值关联的多个项目

Question

I am attempting to create a mysql query that will select all info from a table and then find all associative information from another table. In some instances there could be multiple items associated to the first table, and other other instances, there could be no items.

This is my query so far:

SELECT l.*, i.*
FROM lists l
LEFT JOIN images i ON (i.id = l.image_id AND i.users_id = l.user_id)
WHERE u.user_id = '2' 
    AND l.id = '1025' 
    AND l.active = '0'
ORDER BY i.id ASC

My issue is that when I fetch the data from the query, if there are multiple items in the images table that match the lists table, it returns duplicate entries.

I try to merge all of the data into one large array to use in my code:

while($row = $db->sql_fetchrow($result) )
{
    $lists[] = $row;
}

So this could be an example of result set:

Array
(
    [0] => Array
        (
            [id] => 12345
            [title] => The Old Man and the Sea
            [author] => Ernest Hemingway
            [image] => front.jpg
        )

    [1] => Array
        (
            [id] => 12345
            [title] => The Old Man and the Sea
            [author] => Ernest Hemingway
            [image] => back.jpg
        )

    [2] => Array
        (
            [id] => 12345
            [title] => The Old Man and the Sea
            [author] => Ernest Hemingway
            [image] => ernest.jpg
        )

    [3] => Array
        (
            [id] => 12345
            [title] => The Old Man and the Sea
            [author] => Ernest Hemingway
            [image] => book.jpg
        )

    [4] => Array
        (
            [id] => 12345
            [title] => The Old Man and the Sea
            [author] => Ernest Hemingway
            [image] => mary_hemingway.jpg
        )

    [5] => Array
        (
            [id] => 12331
            [title] => In Our Time
            [author] => Ernest Hemingway
            [image] => time.jpg
        )

    [6] => Array
        (
            [id] => 12331
            [title] => In Our Time
            [author] => Ernest Hemingway
            [image] => time_cover.jpg
        )

)

Is there a better way to write the query or process the data to ensure that I can display a single items with multiple images associated to it.

Answer 1

The query is fine, you just need to build a suitable data structure when you parse the results:

$data = [];

while($row = $db->sql_fetchrow($result) )
{
    $id     = $row['id'];
    $title  = $row['title'];
    $author = $row['author'];
    $image  = $row['image'];

    if(isset($data[$id]){
       $data[$id]['images'][]=$image;
    }else{
       $data[$id] = [ 
           'title'  =>$title,
           'author' =>$author,
           'images' =>[$image],
       ];
    } 
}

var_dump($data);