dragon19720808 2019-04-08 22:40
浏览 52
已采纳

在一个循环之后迭代mysql表的问题

I have 2 tables. First one is categories and the second is items. I need my script to print to .txt file the name of the category and underneath it category items with matching 'category_id'. My code uses first while loop to iterate through the categories and second to iterate through items. Everything works fine until we get to second while loop, because then values become empty.

while ($row2 = mysqli_fetch_array($query_for_categories)) {
    fwrite($fp, $row2["category_name"].PHP_EOL);
    while ($row = mysqli_fetch_array($query_for_items)) {
        if ($row2['id_category_from_category'] == $row['id_category_from_items']) {
            fwrite($fp, $row["item_name"].PHP_EOL);
        }
    }
}
  • 写回答

1条回答 默认 最新

  • dongxunhua2054 2019-04-08 23:21
    关注

    After the first iteration of the outer while loop, you will have fetched all the rows from $query_for_categories. There will be no more rows to fetch from that query on subsequent iterations of the outer while loop.

    You can fetch them all into an array first

    while ($row = mysqli_fetch_array($query_for_items)) {
        $items[] = $row;
    }
    

    Then use the rows from that array instead of a while ... fetch loop.

    while ($category = mysqli_fetch_array($query_for_categories)) {
        fwrite($fp, $category["category_name"].PHP_EOL);
        foreach($items as $item) {
            if ($category['id_category_from_category'] == $item['id_category_from_items']) {
                fwrite($fp, $item["item_name"].PHP_EOL);
            }
        }
    }
    

    It does seem like you could probably do this with one query using a join, though. I'd recommend checking into that instead of doing it this way. Based on the column names in your code, the query would be something like this:

    SELECT c.category_name, i.item_name
    FROM categories c
      LEFT JOIN items i on c.id_category_from_category = i.id_category_from_items
    ORDER BY c.category_name, i.item_name
    

    Then you could print the items with categories in one loop like this:

    $previous_category = null;
    
    while ($item = mysqli_fetch_array($query)) {
    
        // each time category changes, output the new category name
        if ($item['category_name'] != $previous_category) {
            fwrite($fp, $item["category_name"].PHP_EOL);
    
            // then current category becomes previous category
            $previous_category = $item['category_name'];
        }
        fwrite($fp, $item["item_name"].PHP_EOL);
    }
    

    I do wonder how you can tell which of the lines in the file are items and which are categories, though. Maybe you should output some kind of indicator for that?

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?