在一个循环之后迭代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);
        }
    }
}

我有2个表。第一个是categories code>，第二个是 items code>。我需要我的脚本打印到.txt文件类别的名称，并在其下面的类别项目匹配'category_id' 。我的代码使用第一个而 code>循环迭代类别，第二个迭代项目。一切正常，直到我们在 code>循环时进入第二个，因为那时值变为空。 p>

  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）; 
  } 
} 
} 
  code>  pre> 
  div>

答

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?

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

相关推荐