php - mysql左连接回显最后一行

Question

我有两个表classifieds和state。

分类 - id、标题、state_id

状态 - id，状态名称

我正在尝试从状态中回显状态名称。我已经尝试过这个查询，但它只回显最后一行。

    <?php
    $query  = "SELECT * FROM classifieds ";
    $result = mysql_query($query) or die(mysql_query());
    while($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
   ?>

    <div>
   <span>Title: <?php echo $row['title'];?> </span>
    </div>

   <div>
   <span>Name: <?php echo $row['name'];?> </span>
    </div>

      $q = mysql_query("SELECT * FROM classifieds AS C
    LEFT JOIN  state AS S
    ON C.state_id = S.id");
    $z = array();

    while($state=mysql_fetch_array($q))
     {
      array_push($z,$state["statename"]);
     }

     ?>
    <div>
    <span>State: <?php foreach($z as $location) { echo $location; } ?></span>

    </div>

  <?php } // End of the first while loop ?>

谢谢

Answer 1

您需要将 echo 命令移至 while 循环内。否则，只有在遍历完所有行后才会回显。

 <?php
 $q = mysql_query("SELECT * FROM classifieds AS C
LEFT JOIN  state AS S
ON C.state_id = S.state_id");
 while($state=mysql_fetch_array($q)){
    echo "Location: ".$state["statename"];
 }
 ?>

编辑:通过更新的代码，我可以看到您的问题是您首先获得所有分类的列表，然后再次获得包含状态的相同列表加入。您根本不需要执行第一个查询，只需执行第二个查询。

 <?php
 $q = mysql_query("SELECT C.*, S.statename 
       FROM classifieds AS C
       LEFT JOIN  state AS S
       ON C.state_id = S.state_id");
 while($row=mysql_fetch_array($q)){
    echo "Title: ".$row["title"]."<br />";
    echo "Name: ".$row["name"]."<br />";
    echo "Location: ".$state["statename"]."<br />";
 }
 ?>