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

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.

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);

There may be other ways you need to use the array that will prevent this from being feasible, but if you are only using it to output the information, you may be able to eliminate the intermediate step of fetching your query results into a large array. Here is an example of what I am talking about with some minimal markup.

// This can only work if you order by ID as you are currently doing.
$id = null;
while($row = $db->sql_fetchrow($result) ) {
    // Each time the ID changes, output a new header
    if ($row['id'] !== $id) {
        echo '<h2>' . $row['title'] . ' - ' . $row['author'] . '</h2>';
    }
    // Output the image every time, regardless of the header
    echo '<img src="' . $row['image'] . '">';
    $id = $row['id']; // reset $id to the current row's ID for the next comparison
}