php - 一个mysql查询的结果在另一个使用php的查询中

Question

我有一个 mysql 查询连接两个表，使用 mysql_fetch_array 将结果放入关联数组中，并使用 while 循环显示“城市名称”。在同一个查询中，我需要使用关联数组的每个相应的“城市名称”再执行一次查询，并显示每个城市的相应标签。

$result = mysql_query("select city.city_ID, city.city_name, city.city_page, city_images.cimage_path from       city, city_images where 

city.city_ID=city_images.city_ID");
while ($row = mysql_fetch_array($result)) {
    echo '<div class="Box">';
    echo '<a href="' . $row['city_page'] . '">';
    echo '<img src=" ' . $row['cimage_path'] . ' "  />';
    echo '</a>';
    echo '<div class="imagetext">';
    echo '<a href="' . $row['city_page'] . '">' . $row['city_name'] . '</a>';
    echo '</div>';
    echo '<div class="Inbox">';
    $r = mysql_query("select Tag_Name from tag, city_tag where city_tag.city_ID    =$row                ['city_ID'] and city_tag.Tag_ID=tag.Tag_ID ");
    while ($raw = mysql_fetch_array($r)) {
        echo '<div class="boxtags">';
        echo '</div>';
    }
    echo '</div>';
    echo '</div>';
}

我需要与当前城市相对应的标签..请帮助

Answer 1

您只需要一个查询...

SELECT c.city_ID
     , c.city_name
     , c.city_page
     , ci.cimage_path 
     , t.Tag_Name 
  FROM city c
  JOIN city_images ci
    ON ci.city_ID = c.city_ID
  JOIN city_tag ct
    ON ct.city_ID = c.city_ID
  JOIN tag t
    ON tag.Tag_ID = ct.Tag_ID;