How to pull all parent records in the database?

Question

MySQL

Hello! I have such a table in the database:

For example, I refer to the category with id = 7 (Venus). To get the parent category for this category, I need to execute the query SELECT * FROM `category` WHERE `id` = 6
But how to pull out all the parent categories (I don’t know how many), namely to get a chain (venus -> planets -> space -> science)

Answer 1

As far as I know, recursive queries cannot be used in mysql. Use nested sets , or multiple queries in a loop using the language you're writing in.

Answer 2

An example of a recursive function that can be used to get a tree from a flat array.

/**
   * Формирует дерево
   * @param arr $items
   * @param int $parentId
   * @return arr
   */
  public function tree($items, $parentId = 0) {
    $tree = array();
    foreach($items as $item) {
      if ($item['parentId'] == $parentId) {
        $tree[$item['id']] = $item;
        $tree[$item['id']]['sub'] = $this->tree($items, $item['id']);
      }
    }

    return $tree;
  }

It is better of course to use some algorithm for storing trees in the database. There are many of them, one of them is nested sets, as recommended above. But the choice of algorithm should proceed from the conditions of a particular problem.