MySQL tree walk procedure. See another implementation on https://gist.github.com/kissarat/467b284fc53d5dab2427

descend.mysql.sql

CREATE PROCEDURE descend(uid INT) BEGIN
  DECLARE depth INT;
  SET depth = (SELECT count(*) FROM tree t
    JOIN tree_type tt ON tt.id = t.type_id
    JOIN tree_level l ON l.type_id = tt.id
  WHERE t.user_id = uid);

  IF depth > 0 THEN
    DROP TABLE IF EXISTS descendants;
    DROP TABLE IF EXISTS children;
    CREATE TEMPORARY TABLE descendants (
      id int,
      parent_id int,
      level int
    ) ENGINE = MEMORY;

    INSERT INTO descendants(id, level)
    VALUES (uid, 0);
    CREATE TEMPORARY TABLE children LIKE descendants;
#     ALTER TABLE descendants ADD CONSTRAINT UNIQUE(id);

    iter: LOOP
      INSERT INTO children
        SELECT u.id, u.parent_id, level + 1 FROM user u
          JOIN descendants h ON u.parent_id = h.id
        WHERE level < depth;
      DELETE FROM children WHERE id IN (SELECT id FROM descendants);
      IF (SELECT count(*) FROM children) > 0 THEN
        INSERT INTO descendants SELECT * FROM children;
      ELSE
        LEAVE iter;
      END IF;
    END LOOP;
    SELECT d.level, d.id, d.parent_id, u.created_at as time, u.username, u.email, u.skype FROM descendants d
      JOIN tree t ON d.id = t.user_id
      JOIN user u ON d.id = u.id;
#   DROP TABLE descendants;
#   DROP TABLE children;
  END IF;
END;