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
)

#3

Thanks a lot for explaining that. It seems quite clear to me now, however I am having trouble figuring out how to link my data in with that.

Here’s my full table:

+---------+-------------+--------------+--------------+------------------+
| page_id | page_parent | page_sets_id | page_menu_id | page_ordernumber |
+---------+-------------+--------------+--------------+------------------+
|       1 |           0 |            0 |            1 |             NULL |
|       2 |           1 |            0 |            2 |                1 |
|       3 |           2 |            0 |            6 |                1 |
|       4 |           2 |            0 |            4 |                2 |
|       5 |           2 |            0 |            3 |                3 |
|       6 |           2 |           47 |            0 |                4 |
|       7 |           1 |            0 |            7 |                2 |
|       8 |           7 |            0 |            8 |                1 |
|       9 |           7 |            0 |            9 |                2 |
|      10 |           7 |           88 |            0 |                3 |
|      11 |           1 |           20 |            0 |                3 |
+---------+-------------+--------------+--------------+------------------+

My index page creates a list of menu selections, however some of those menu selections are “sub menus” and others are “sets of items”. The information that is pulled in, is based on whether there is a value in page_menu_id or in page_sets_id. Which ever one has a value then it will need to show the corresponding name (menu_name or sets_name). There are also additional columns that display info in the index menu selection as well (menu_info1 or sets_info1 and also sets_releasedate.

Here is the query I use for creating the index menu:

$sql = "SELECT tablePages.*, tableMenus.menu_nameunique, tableHome.home_imagename FROM tablePages 
        LEFT JOIN tableMenus ON tablePages.page_menu_id=tableMenus.menu_id 
        LEFT JOIN tableLines ON tableMenus.menu_line_id=tableLines.line_id
        LEFT JOIN tableMakers ON tableLines.line_make_id=tableMakers.make_id
        LEFT JOIN tableHome ON tableMakers.make_home_id=tableHome.home_id
        WHERE page_id = ?";

How would I incorporate this into the above example?

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

I’m not sure if I need to code a condition into the query (dependent on whether page_sets_id or page_menu_id has a value), or if instead I should do like I have done above and just dump all the info in to a large table, and let PHP do the work with the conditional retrieving of data. I would rather do the latter, but can’t figure out how I can have page_sets_id and page_menu_id, show up in the array for the original page, plus the parent, grandparent and greatgrand.


#5

I’m still trying to figure this out, and I’m guessing the only way I can get the results I need is that I need to do 2 queries.

#1: This would be the above query assigning values into parent, grandparent and greatgrand (although I need to use LEFT join instead of INNER join as I get an empty result if with INNER when any of the nodes are null.)
#2: I would query my pages table for page_id, page_sets_id, and page_menu_id.

I would then have 4 IF conditions that would extract the data… here’s sort of the pseudo code I am thinking should work (however there might be a better way)?
IF page_id is not null, then IF page_menu_id for page_id is not null, get menu_name from menu table by comparing page_menu_id to menu_id ELSE get sets_name from page_sets_id
IF parent is not null, then IF page_menu_id for parent is not null, get menu_name from menu table by comparing page_menu_id to menu_id ELSE get sets_name from page_sets_id
… and repeat for grandparent and greatgrand.

(I have to leave my house now, but I will try this once I have returned).


#6

You might have to write a very large SELECT statement listing every field name with a unique alias.