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

Question

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);
        }
    }
}

Answer 1

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?