How to Loop through a Table Tree with only MySQL

Question

Things to know:

• I am inserting this statement inside of our Reporting software, so I don't have the luxury of using PHP to do the looping for me.
• I am not familiar with the LOOP clause inside MySQL, I did a little research before, but could use some help with the syntax.

Question:

How would I loop through this MySQL statement? (Statement given below)

The following statement works right now, I have further CASE WHEN's to add it, but for now I'm trying to solve for the most confusing part of the whole thing.

I need to loop through the MySQL statement ( or find another way to write the same thing) until I reach the bottom of the tree. To illustrate what I am trying to do:

Let's assume I need to get the Downline of this entire tree starting from the top. Which means I have to run this MySQL statement for each person below.

In our current set-up, I store the ID of the person above them in the record like so, and to follow the record DOWN, I have to give it the Upline ID and return all the records where it has that Upline ID, which returns all the IMMMEDIATE downlines.

+---------+-------------+------+
| ID      | Upline ID   | Name |
+---------+-------------+------+
| 745753  | 654-64645-3 | John |
| 098678  | 916-59172-1 | Jill |
| 543272  | 866-99573-8 | Fred |
| 634543  | 126-97939-3 | Dean |
| 923461  | 734-84628-5 | Bill |
| 861345  | 643-01957-0 | Cris |
+---------+-------------+------+

This means for each row returned in the set, I have to follow the MySQL statement down each record tree individually, and do the same for each row returned, causing the MySQL statements to multiply, UNTIL, when I run the statement for that record, nothing is returned, so I reached the end of the downline.

Without further waiting, here is statement (I give it the first person at the top's ID in the WHERE clause to get the statement started):

    SELECT wn_writing_number_cstm.title_c, 
    wn_writing_number.`name`, 
    preps_contracted_reps.first_name, 
    preps_contracted_reps.last_name, 
    cac_customize_agent_comp_cstm.commission_percentage_c, 
    wn_writing_number_cstm.id_c
FROM wn_writing_number 
     LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida = wn_writing_number.id
     LEFT OUTER JOIN cac_customize_agent_comp ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp.id
     LEFT OUTER JOIN cac_customize_agent_comp_cstm ON cac_customize_agent_comp.id = cac_customize_agent_comp_cstm.id_c
     LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
     LEFT OUTER JOIN preps_contracted_reps_wn_writing_number_1_c ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id
     LEFT OUTER JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
     LEFT OUTER JOIN wn_writing_number_cstm ON wn_writing_number_cstm.id_c = wn_writing_number.id
WHERE wn_writing_number_cstm.wn_writing_number_id_c = '53506bbe-008f-577c-2114-576b32e0ad11'

Here is a Query Builder Diagram to help illustrate this model:

And finally, here is what this statement actually returns, I'd need to run this statement twice again, one for each row with the ID of each of those records in my WHERE clause:

+---------+-------------+------------+-------------------------------------+
| title_c | name        | first_name | last_name | percent_c | id_c        |
+---------+-------------+------------+-------------------------------------+
| A       | MP-AB0682-16| Andrea     | Donald    | 10        | 823462345   |
| GA      | RO-RM4619-16| Ronald     | Yeller    | 12        | 632811634   |
+---------+-------------+------------+-----------+-----------+-------------+

Please comment for any further clarification needed. Thanks!

Answer 1

A semi-pseudo code answer to clarify my method (sorry, don't have time for a more precise solution, and don't use the syntax frequently enough to remember it exactly without looking it up):

DECLARE lastInsertCount INT;
DECLARE lastLayer INT;
CREATE TABLE `temp`
(
   `layer` int,
   [fields you want and parent's identifier]
);

INSERT INTO `temp`(`layer`, fields you want...)
SELECT 0, fields you want...
FROM theTable
WHERE [is "root" result]
;
SET lastLayer := 0;

SET lastInsertCount := 1;
LayerLoop: WHILE lastInsertCount > 0

  INSERT INTO `temp`(`layer`, fields you want...)
  SELECT lastLayer + 1, fields you want...
  FROM theTable
  WHERE parent_id IN (SELECT id FROM `temp` WHERE layer = lastLayer)
  ;

  SET lastInsertCount := ROW_COUNT();
  SET lastLayer := lastLayer + 1;

END WHILE LayerLoop;

SELECT fields you want...
FROM `temp`
;

DROP TABLE `temp`;

---

Edit: If you allow "cycles" in the tree, which would make it not a tree (but I've had to worry about it in bad data), you can avoid those causing you to loop infinitely by adding

AND id NOT IN (SELECT id FROM `temp`)

to the where clause.