How to show subcategories?

Question

PHP

There is a table like this:
I need to display all the categories so that it looks like this:
Category 1
Category 2
Category 3
-- sub-category of the third
---- sub-category of the fourth
And, in fact, this is how I do it:

$r - mysql_query("SELECT `id`,`name`,`parent` FROM `category` WHERE `parent` = 0"); // берем категории, которые явно не "подкатегории"
while($w = mysql_fetch_assoc($r)){
  echo $w['name'];
  $r2 = mysql_query("SELECT * FROM `category` WHERE `parent` = {$w[id]}"); // вытягиваем все подгатегории полученной категории
  if(mysql_num_rows($r2) > 0){
    while($w2 = mysql_fetch_assoc($r2)){
      echo '--'.$w2['name'];
    }
  }
}

This is clearly a resource-intensive operation, given that there can be any number of subcategories and categories too, and each category or subcategory can have its own. It turns out a lot of requests.
What can you think of less complicated for the base?

Answer 1

Store relationships in a separate table for example.
This design pattern is called "closure table", described in detail here - habrahabr.ru/post/193166
PS
Using single-letter variables, using mysql_* functions, using two-space indents, mixing database queries and outputting data - this is something that should never be in the code. I don't know what source you use to learn about PHP programming, but be aware that it is at least 10 years old.

Answer 2

Everything Immortal says is correct. Such things should be broken down when planning the base. Category table, subcategory table and easy JOIN queries.

Answer 3

Keep the category tree in a Nested Set , then its output will be much easier.