为什么左连接会产生不同的结果

3npbholx 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(461)

在上运行查询时，此查询有什么问题http://localhost/phpmyadmin/ 它将显示我想要的结果，但在php页面上运行时，它将给出不同的结果。

但当我在php页面上获取结果时，它也会给出空记录

如果没有这些空的子类别ID和子类别名称，如何获取记录
下面是php代码

<table id="subCat1Table" class="table table-striped table-bordered" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Sub Category ID</th>
      <th>Sub Category Name</th>
      <th>Root Category ID</th>
      <th>Root Category Name</th>
    </tr>
  </thead>
  <!-- <tfoot>
  <tr>
    <th>Sub Category ID</th>
    <th>Sub Category Name</th>
    <th>Root Category ID</th>
    <th>Root Category Name</th>
  </tr>
  </tfoot> -->
  <tbody>
    <?php
    $query1 = mysqli_query($conn, 'SELECT * FROM sub_category1 LEFT JOIN main_category ON sub_category1.main_category_id = main_category.category_id ');

    while ($row = mysqli_fetch_array($query1, MYSQLI_ASSOC))
    {
    // Result Assigning to Array
    $rows[] = $row;
    }
    // traversing Array
    echo("<pre>");
    print_r($rows);
    echo("</pre>");
    foreach($rows as $row):
    // Getting Values
    $subCategory1ID = stripslashes($row['sub_category1_id']);
    $subCategory1Name = stripslashes($row['sub_category1_name']);
    $rootCategoryID = stripslashes($row['category_id']);
    $rootCategoryName = stripslashes($row['category_name']);
    ?>
    <tr>
      <td><?php echo "$subCategory1ID"; ?> </td>
      <td><?php echo "$subCategory1Name"; ?> </td>
      <td><?php echo "$rootCategoryID"; ?> </td>
      <td><?php echo "$rootCategoryName"; ?> </td>
    </tr>
    <?php
    endforeach; //End ForEach loop
    ?>

  </tbody>
</table>

sql mysql php phpmyadmin

来源：https://stackoverflow.com/questions/48082944/why-left-join-gives-different-results

2条答案

按热度按时间

kjthegm61#

left join关键字返回左表（表1）中的所有记录，以及右表（表2）中匹配的记录。如果不匹配，则右侧的结果为空。
您应该使用内部联接只获取匹配记录

赞(0）回复(0）举报 2021-06-21

6ju8rftf2#

这里出现的问题是由于在while循环中为数组赋值

<?php
    $query1 = mysqli_query($conn, 'SELECT * FROM sub_category1 LEFT JOIN main_category ON sub_category1.main_category_id = main_category.category_id ');

    while ($row = mysqli_fetch_array($query1, MYSQLI_ASSOC))
    {
    // Result Assigning to Array
    $rows[] = $row;
    }
    // traversing Array
    echo("<pre>");
    print_r($rows);
    echo("</pre>");
    foreach($rows as $row):
    // Getting Values
    $subCategory1ID = stripslashes($row['sub_category1_id']);
    $subCategory1Name = stripslashes($row['sub_category1_name']);
    $rootCategoryID = stripslashes($row['category_id']);
    $rootCategoryName = stripslashes($row['category_name']);
    ?>
    <tr>
      <td><?php echo "$subCategory1ID"; ?> </td>
      <td><?php echo "$subCategory1Name"; ?> </td>
      <td><?php echo "$rootCategoryID"; ?> </td>
      <td><?php echo "$rootCategoryName"; ?> </td>
    </tr>
    <?php
    endforeach; //End ForEach loop
    ?>

现在不分配给数组

<?php
     $query = mysqli_query($conn, "SELECT * FROM sub_category1 INNER JOIN main_category ON sub_category1.main_category_id = main_category.category_id");

     while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)):
    // Getting Values
    $subCategory1ID = stripslashes($row['sub_category1_id']);
     $subCategory1Name = stripslashes($row['sub_category1_name']);
     $mainCatID= stripslashes($row['main_category_id']);
     $rootCategoryID = stripslashes($row['category_id']);
     $rootCategoryName = stripslashes($row['category_name']);
     ?>
     <tr>
       <td><?php echo "$subCategory1ID"; ?> </td>
                                <td><?php echo "$subCategory1Name"; ?> </td>
       <td><?php echo "$mainCatID"; ?> </td>
       <td><?php echo "$rootCategoryID"; ?> </td>
                                <td><?php echo "$rootCategoryName"; ?> </td>
     </tr>
    <?php
     endwhile; //End ForEach loop
?>

赞(0）回复(0）举报 2021-06-21

我来回答

为什么左连接会产生不同的结果

2条答案

相关问题

热门标签

最新问答