PHP coding for building a hierarchical menu


#1

I have the following table which creates my links for a hierarchical menu.

mysql> SELECT page_id, page_parent, page_ordernumber FROM tablePages ORDER BY page_parent, page_ordernumber;                                                                                                                                                               
+---------+-------------+------------------+
| page_id | page_parent | page_ordernumber |
+---------+-------------+------------------+
|       1 |           0 |             NULL |
|       2 |           1 |                1 |
|       7 |           1 |                2 |
|      11 |           1 |                3 |
|       3 |           2 |                1 |
|       4 |           2 |                2 |
|       5 |           2 |                3 |
|       6 |           2 |                4 |
|       8 |           7 |                1 |
|       9 |           7 |                2 |
|      10 |           7 |                3 |
|      12 |           4 |                1 |
+---------+-------------+------------------+

I am trying to figure out how to code for this now. For example, if I select result from page_id #12, then my menu will show:

As first selection: page link to page_id #4 because it is the parent of #12
As second selection: page link to page_id #2 because it is the parent of #4.
As third selection: page link to page_id #1 because it is the parent of #2

I am have great difficulty in trying to figure out the PHP code for this. I believe that in order to build out this menu, it should require 1 query and 1 loop, however I just can’t figure it out. The only way I can think of (which I know is wrong in EVERY possible way) is this to cycle through 3 separate queries (since I don’t plan to have more than 3 parent/child associations):

            // First Check
            if (isset($user_selection)) {
                $sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$user_selection]);  
                $result = $stmt->fetch();
                $option1 = $result['page_parent'];
            }
            
            // Second Check
            if (isset($option1) && $option1 != 1) {
                $sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$option1]);
                $result = $stmt->fetch();
                $option2 = $result['page_parent'];
            }
            
            // Third Check
            if (isset($option2) && $option2 != 1) {
                $sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$option2]);
                $result = $stmt->fetch();
                $option3 = $result['page_parent'];
            }

Any help or guidance anyone can provide on this would be greatly appreciated.

(Note: I know the page_ordernumber column is going to annoy seasoned coders. Right now, I want it to work with this column. I am learning in stages. Once I can get this working, then I will focus on better ways to manage the nodes as mentioned here (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/). I only have surface level understanding of the methods described there, and it’s looking like this part may be beyond my mental capabilities.)


#2

ignore this first query. PHPMyAdmin works some magic on it to make it display exactly what I want but in plain php the identical column names overwrite each other and I only get 3 fields.

select 
A.*,B.*,C.*,D.*
from test as A
INNER JOIN test as B on B.page_id = A.page_parent
INNER JOIN test as C on C.page_id = B.page_parent
INNER JOIN test as D on D.page_id = C.page_parent
where A.page_id = 12

Updated query to specify every field with a different name:

select 
A.page_id,
A.page_ordernumber,
B.page_id as parent,
C.page_id as grandparent,
D.page_id as greatgrand
from test as A
INNER JOIN test as B on B.page_id = A.page_parent
INNER JOIN test as C on C.page_id = B.page_parent
INNER JOIN test as D on D.page_id = C.page_parent
where A.page_id = 12

This gives me

Array
(
	[page_id] => 12
	[page_ordernumber] => 1
	[parent] => 4
	[grandparent] => 2
	[greatgrand] => 1
)