php - 具有连接表的二维数组

Question

我正在使用联接来查询两个表中的数据。对于每个 a_id，我需要获取关联的 image_id，过滤这些 image_id 结果，只保留第一个，最后将结果输出到 li 中。我认为我的查询很好，但我在思考如何获取每个 a_id 的 image_id 并将其输出到我的 li 时遇到了一些麻烦。此代码返回一些结果，但它们不是我正在寻找的结果。

<?php
echo '<ul>';
$result = mysql_query ("SELECT * from artists left join images on artists.a_id = images.image_id where artists.display_works = '1' and artists.active = '1' order by artists.project_year desc, artists.fullname desc");

while ($row = mysql_fetch_array($result)){
    $data['a_id']['image_id']=$row->a_id;

    foreach($data as $id=>$images) {
            $totalimages=1;
            $addstyle = "";
            $art_id = $data['a_id'];
            $img_id = $data['image_id'];

            foreach($images as $val){

                    if($totalimages > 1){ $addstyle = 'style="display:none;"'; }
                    else {
                    $myimagename = "http://artists/$art_id/images/$img_id" .  "_large.jpg";
                    list($width, $height, $type, $attr) = getimagesize("$myimagename");
                    $myimagename = "http://artists/resize.php/$art_id/images/$img_id" . "_large.jpg?resize(157x2000)";

                    if($row["layout"] == "vert"){$pl = "_vertical";}else if($row["layout"] == "website"){$pl = "-s";}else if($row["layout"] == "video"){$pl = "_video";}else{$pl = "_horizontal";}
                    echo "<li class='thumbnail_container' $addstyle> <a class='thumbnail' href=\"../works$pl.php?a_id=" . $row["a_id"] . "\"><span><img src=\"$myimagename\" /></span>\n</a></li>\n";
                    }

                    $totalimages++;
        }
    }
}
echo '</ul>';
?>

嗯，我对代码做了一些修改，它可以工作，但由于某种原因，我在第一张图像之后得到了一个额外的缩略图，没有图像 url 或链接 url。我认为这可能与我检查重复a_ids的方法有关:

<?php
echo '<ul>';
$result = mysql_query ("SELECT * from artists left join images on artists.a_id = images.a_id where artists.display_works = '1' and artists.active = '1' order by artists.project_year desc, artists.fullname desc, images.position asc");

while ($row = mysql_fetch_array($result)){
    $check = $row['a_id'];
    if (in_array($check, $a_ids)) {end;}
    else { 
    $a_id=$row['a_id'];
    $a_ids[] = $a_id;
    $image_id=$row['image_id'];

    $myimagename = "http://artists/$a_id/images/$image_id" .  "_large.jpg";
    list($width, $height, $type, $attr) = getimagesize("$myimagename");
    $myimagename = "http://artists/resize.php/$a_id/images/$image_id" . "_large.jpg?resize(157x2000)";

    if($row["layout"] == "vert"){$pl = "_vertical";}else if($row["layout"] == "website"){$pl = "-s";}else   if($row["layout"] == "video"){$pl = "_video";}else{$pl = "_horizontal";}
        echo "<li class='thumbnail_container' $addstyle> <a class='thumbnail' href=\"../works$pl.php?a_id=" . $row["a_id"] . "\"><span><img src=\"$myimagename\" /></span>\n</a></li>\n";
    }
}
echo '</ul>';
?>

Answer 1

这是我最终使用的解决方案。真正让我感到困难的是我没有掌握两个概念。

第一个是加入。我不明白它是如何合并两个表的。在阅读了更多相关内容后，我现在知道在连接不同名称的列时 ON 更合适，但我真正想要的是通过 a_id 列连接两个表。即使我保留了 ON 并使两个列名称相同，我也应该考虑使用关键字 USING，因为它专门用于同名的列。

我难以理解的第二个概念是如何从新连接的表中获取我想要的所有信息。我不知道使用 mysql_fetch_array 和 while 循环会遍历每一行并获取每一列的所有数据。一旦我理解了这一点，浏览每一行并获取 image_id 和 a_id 就很容易了。

我的最终代码:

<?php
echo '<ul>';
$result = mysql_query ("SELECT * from artists left join images on artists.a_id = images.a_id where artists.display_works = '1' and artists.active = '1' order by artists.project_year desc, artists.fullname desc, images.position asc");

while ($row = mysql_fetch_array($result)){
    $check = $row['a_id'];
    if (!in_array($check, $a_ids) && $check !='') {
    $a_id = $row['a_id'];
    $a_ids[] = $a_id;
    $image_id = $row['image_id'];

    $myimagename = "http://artists/$a_id/images/$image_id" .  "_large.jpg";
    list($width, $height, $type, $attr) = getimagesize("$myimagename");
    $myimagename = "http://artists/resize.php/$a_id/images/$image_id" . "_large.jpg?resize(157x2000)";

    if($row["layout"] == "vert"){$pl = "_vertical";}else if($row["layout"] == "website"){$pl = "-s";}else   if($row["layout"] == "video"){$pl = "_video";}else{$pl = "_horizontal";}
        echo "<li class='thumbnail_container' $addstyle> <a class='thumbnail' href=\"../works$pl.php?a_id=" . $row["a_id"] . "\"><span><img src=\"$myimagename\" /></span>\n</a></li>\n";
    }
}
echo '</ul>';
?>